CREATE PROCEDURE `CreateSerial`(
IN table_name varchar(50),
IN filed_name varchar(50) ,
IN orderNamePre varchar(10),
in num INT,
OUT serial VARCHAR(64))
BEGIN
DECLARE currentDate VARCHAR (15) ;
DECLARE maxNo INT DEFAULT 0 ;
DECLARE padstrlength int DEFAULT 3;
declare prelength INT ;
SET prelength = LENGTH(orderNamePre);
IF num = 6 THEN
SELECT DATE_FORMAT(NOW(), '%y%m%d') INTO currentDate ;-- 根据年月日生产订单编号
ELSEIF num = 8 THEN -- 根据年月日生成订单编号
SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;-- 订单编号形式:前缀+年月日+流水号,如:CG20201215001
ELSEIF num = 14 THEN -- 根据年月日时分秒生成订单编号
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') INTO currentDate ; -- 订单编号形式:前缀+年月日时分秒+流水号,如:SH2013011010050700001,个人不推荐使用这种方法生成流水号
ELSE -- 根据年月日时分生成订单编号
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i') INTO currentDate ;-- 订单形式:前缀+年月日时分+流水号,如:SH20130110100900005
END IF ;
-- 预处理指令的使用
-- 使用max优化
set @optsql = CONCAT('SELECT IFNULL(MAX(RIGHT(',filed_name,',',padstrlength,' )), \'0\' ) into @maxNo',
' from ', table_name,
' WHERE' ,
' SUBSTRING(',filed_name,',', prelength + 1,',',num,') = \'',currentDate,'\'',
' AND SUBSTRING(',filed_name,',1',',',prelength,') = \'',orderNamePre,'\''
);
prepare stmt from @optsql;
execute stmt;
deallocate prepare stmt;
set maxNo = @maxNo;
SELECT CONCAT(orderNamePre,currentDate,LPAD((maxNo + 1),padstrlength,'0')) into serial;
end
原文链接: