通过存储过程解决并发生成唯一订单号

来源：互联网发布：新唐单片机官网编辑：程序博客网时间：2024/05/18 08:26

DELIMITER $$USE `roamerbuddy`$$DROP PROCEDURE IF EXISTS `generate_orderNo`$$CREATE DEFINER=`root`@`%` 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 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   THEN   INSERT INTO generate_serialno (id, ordervalue, ordertime)   VALUES    (1, 1, currentTime) ;  END IF ;  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    ) ;  SET updateRow = ROW_COUNT() ;  WHILE    ! updateRow = 1 DO -- 获得当前的订单和变化的值       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 (        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, num, '0')    ) INTO orderCode ;  ELSEIF num = 14   THEN -- 根据年月日时分秒生成订单编号,订单编号形式：前缀+年月日时分秒+流水号，如：SH2013011010050700001,个人不推荐使用这种方法生成流水号       SELECT     CONCAT(  orderNamePre,      DATE_FORMAT(currentTime, '%Y%m%d%H%i%s'),      LPAD(orderNameValue, num, '0')    ) INTO orderCode ;  ELSE -- 根据年月日时分生成订单编号,订单形式：前缀+年月日时分+流水号,如：SH20130110100900005  SELECT     CONCAT(  orderNamePre,      DATE_FORMAT(currentTime, '%Y%m%d%H%i'),      LPAD(orderNameValue, num, '0')    ) INTO orderCode ;  END IF ;  SELECT     orderCode ;END$$DELIMITER ;

DROP TABLE IF EXISTS `generate_serialno`;CREATE TABLE `generate_serialno` (  `id` int(20) DEFAULT NULL,  `orderno` varchar(50) DEFAULT NULL,  `orderpre` varchar(30) DEFAULT NULL,  `ordervalue` int(20) DEFAULT NULL,  `ordertime` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `generate_serialno` (`id`, `ordervalue`) values('1','1');

0 0