利用mysql触发器生成流水号

来源：互联网发布：自学平面设计软件编辑：程序博客网时间：2024/05/14 09:14

http://blog.csdn.net/bluestream/article/details/5755176

利用mysql触发器可以生成如日期(20100721)+编号(0001)的流水号,编号每天都会从0001开始计算

create table orders(orders_id int(10) primary key,customer_name varchar(100) );

----------------------------------------------------------------------------------------------------------

CREATE TRIGGER tr_orders_id BEFORE INSERT ON orders
FOR EACH ROW BEGIN
declare n int;
select IFNULL(max(right(orders_id,4)),0) into n from orderswhere mid(orders_id,1,8)=DATE_FORMAT(CURDATE(),'%Y%m%d');
set NEW.orders_id=concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),right(10001+n,4));
END;

----------------------------------------------------------------------------

insert into test(customer_name) value('jack');

insert into test(customer_name) value('jason');

-----------------------------------------------------------------------------

orders_id customer_name

201007210001 jack

201007210002 jason