mysql生成订单编号函数

DROP TABLE IF EXISTS `order_seq`;
CREATE TABLE `order_seq` (
`timestr` int(11) NOT NULL,
`order_sn` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop FUNCTION order_seq;

create function order_seq()
returns VARCHAR(50)
begin
declare v_cnt integer;
declare v_timestr integer;
declare v_value VARCHAR(50) ;
declare rowcount BIGINT;
set v_timestr =DATE_FORMAT(now(),'%Y%m%d');
set v_cnt = 1;
select order_sn + 1 INTO v_cnt from order_seq where timestr = v_timestr;
UPDATE order_seq set order_sn = v_cnt where timestr = v_timestr;
if ROW_COUNT() = 0 THEN
INSERT INTO order_seq values(v_timestr,v_cnt);
end if;
select CONCAT(v_timestr,LPAD(order_sn,7,0)) INTO v_value from order_seq where timestr = v_timestr;
return v_value;
end;
--运行结果
SELECT order_seq();

转载于:https://www.cnblogs.com/working/p/5624666.html