mysql生成订单编号函数

最新推荐文章于 2024-12-05 19:40:59 发布

转载最新推荐文章于 2024-12-05 19:40:59 发布 · 580 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/working/p/5624666.html

文章标签：

#数据库

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();