mysql生成唯一订单号的函数优化说明
原来的:
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orderNo`(IN orderNamePre CHAR(4),
IN num INT,
OUT newOrderNo VARCHAR (25))
BEGIN
-- 订单变化的值
DECLARE orderNameValue INT ;
-- 更新行数
DECLARE updateRow INT ;
-- 当前日期,有可能包含时分秒
DECLARE currentTime DATETIME ;
-- 订单号
DECLARE orderCode VARCHAR (64) ;
DECLARE orderNum INT DEFAULT 5; -- 订单变化的值
-- 异常处理
DECLARE CONTINUE HANDLER FOR 1062
SET currentTime = NOW() ;
-- 获得订单号
SELECT
IFNULL(gs.ordervalue, 0) INTO orderNameValue
FROM
generate_serialno gs
WHERE id = 1 ;
SET currentTime = NOW() ;
-- 打开自动提交
SET autocommit = 1 ;
IF TRUE -- 如果true插入一条数据
THEN
INSERT INTO generate_serialno (id, ordervalue, ordertime)
VALUES
(1, 1, currentTime) ;
END IF ; -- 否则更改订单号
UPDATE
generate_serialno obj
SET
obj.ordervalue =
CASE -- 订单变化的值如果今天大于昨天从1开始,否则再原来的基础上加1
WHEN TO_DAYS(currentTime) > TO_DAYS(obj.ordertime)
THEN 1
ELSE orderNameValue + 1
END,
obj.ordertime = currentTime
WHERE ( -- 订单变化的值
obj.id = 1
AND obj.ordervalue = orderNameValue
) ;
SET updateRow = ROW_COUNT() ;
WHILE
! updateRow = 1 DO -- 如果更改的行数不等于1
SELECT -- 获得当前的订单变化值
IFNULL(gs.ordervalue, 0) INTO orderNameValue
FROM
generate_serialno gs
WHERE id = 1 ;
UPDATE
generate_serialno obj
SET
obj.ordervalue =
CASE
WHEN TO_DAYS(currentTime) > TO_DAYS(obj.ordertime)
THEN 1
ELSE orderNameValue + 1
END,
obj.ordertime = currentTime
WHERE (-- 只有订单变化值和id都相等的情况下才能更改,更具行锁的机制
obj.id = 1
AND obj.ordervalue = orderNameValue -- 注意!!!!
) ;
SET updateRow = ROW_COUNT() ;
END WHILE ;
IF num = 8
THEN -- 根据年月日生成订单编号,订单编号形式:前缀+年月日+流水号,如:SH2013011000002
SELECT
CONCAT(
orderNamePre,
DATE_FORMAT(currentTime, '%Y%m%d'),
LPAD(orderNameValue, orderNum, '0')
) INTO orderCode ;
ELSEIF num = 14
THEN -- 根据年月日时分秒生成订单编号,订单编号形式:前缀+年月日时分秒+流水号,如:SH2013011010050700001,个人不推荐使用这种方法生成流水号
SELECT
CONCAT(
orderNamePre,
DATE_FORMAT(currentTime, '%Y%m%d%H%i%s'),
LPAD(orderNameValue, orderNum, '0')
) INTO orderCode ;
ELSE -- 根据年月日时分生成订单编号,订单形式:前缀+年月日时分+流水号,如:SH20130110100900005
SELECT
CONCAT(
orderNamePre,
DATE_FORMAT(currentTime, '%Y%m%d%H%i'),
LPAD(orderNameValue, orderNum, '0')
) INTO orderCode ;
END IF ;
SELECT
orderCode ;
END
修复后的:
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orderNo`(IN orderNamePre CHAR(4), IN num INT, OUT newOrderNo VARCHAR (25))
BEGIN
-- 订单变化的值
DECLARE orderNameValue INT;
-- 当前日期,有可能包含时分秒
DECLARE currentTime DATETIME;
-- 订单号
DECLARE orderCode VARCHAR(64);
DECLARE orderNum INT DEFAULT 5; -- 订单变化的值
-- 开启事务
START TRANSACTION;
-- 获得订单号
SELECT IFNULL(gs.ordervalue, 0) INTO orderNameValue FROM generate_serialno gs WHERE id = 1 FOR UPDATE;
SET currentTime = NOW();
-- 更新订单号
UPDATE generate_serialno obj SET
obj.ordervalue = CASE
WHEN TO_DAYS(currentTime) > TO_DAYS(obj.ordertime) THEN 1
ELSE orderNameValue + 1
END,
obj.ordertime = currentTime
WHERE obj.id = 1 AND obj.ordervalue = orderNameValue;
-- 检查是否更新成功
IF ROW_COUNT() < 1 THEN
-- 回滚事务
ROLLBACK;
-- 抛出异常
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Failed to update order number.';
END IF;
IF num = 8 THEN
-- 根据年月日生成订单编号
SET orderCode = CONCAT(orderNamePre, DATE_FORMAT(currentTime, '%Y%m%d'), LPAD(orderNameValue, orderNum, '0'));
ELSEIF num = 14 THEN
-- 根据年月日时分秒生成订单编号
SET orderCode = CONCAT(orderNamePre, DATE_FORMAT(currentTime, '%Y%m%d%H%i%s'), LPAD(orderNameValue, orderNum, '0'));
ELSE
-- 根据年月日时分生成订单编号
SET orderCode = CONCAT(orderNamePre, DATE_FORMAT(currentTime, '%Y%m%d%H%i'), LPAD(orderNameValue, orderNum, '0'));
END IF;
-- 提交事务
COMMIT;
-- 返回订单号
SELECT
orderCode ;
END
- 在修复后的存储过程中,添加了事务处理逻辑。使用
START TRANSACTION
开启事务,COMMIT
提交事务,以及在更新失败时使用ROLLBACK
回滚事务,确保数据的一致性。 - 修复后的存储过程在获取订单号时使用了
FOR UPDATE
,通过行级锁的方式锁定了该行数据,以避免并发问题。 - 修复后的存储过程添加了异常处理机制。当更新订单号失败时,使用
SIGNAL
抛出自定义的异常,提供更加明确的错误信息。 - 精简了代码逻辑,去除了不必要的部分,使得代码更加清晰易懂。
- 移除了无用的变量声明,如
DECLARE updateRow INT;
。
版权声明:若无特殊注明,本文皆为《菜鸟站长》原创,转载请保留文章出处。
本文链接:mysql生成唯一订单号的函数优化说明 - http://wziyi.com.cn/?post=416