mysql触发器生成流水_利用mysql触发器生成流水号

利用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

.

.