利用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 orders_id(customer_name) value('jack');
insert into orders_id(customer_name) value('jason');
-----------------------------------------------------------------------------
orders_id customer_name
201007210001 jack
201007210002 jason我做项目练习,同样要做这样的流水号8位日期+4位顺序号。找了个教程,但是这个教程有问题
1. 触发器里的代码有问题,不能运行,求正确的范例,求表结构和触发器的代码演示2.他的建表字段为orders_id int(10),但主键要插入12位数字,我在navcat里改变长度也只能插入10位数字,那应该用varchar吗?
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 orders_id(customer_name) value('jack');
insert into orders_id(customer_name) value('jason');
-----------------------------------------------------------------------------
orders_id customer_name
201007210001 jack
201007210002 jason我做项目练习,同样要做这样的流水号8位日期+4位顺序号。找了个教程,但是这个教程有问题
1. 触发器里的代码有问题,不能运行,求正确的范例,求表结构和触发器的代码演示2.他的建表字段为orders_id int(10),但主键要插入12位数字,我在navcat里改变长度也只能插入10位数字,那应该用varchar吗?
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&&
1.是varchar类型
2.是 orders where 要分开
3.是主键orders_id要设一个默认值才能插进去,我设空字符串
还有就是注意写触发器,要改边界符delimiter// 中间是触发器代码的方式 //
declare n int;报错
楼主的语句有3个错误
order_id--类型
orderswhere-连起来了
insert into order_id--这里是表名不是列
e.g.
drop table if exists orders,NextSerialNr;
create table orders(orders_id varchar(12) primary key,customer_name varchar(100) );
create table NextSerialNr(DT date,NextNr int);delimiter $$
CREATE TRIGGER tr_orders_id BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
declare n int;
select NextNr into n from NextSerialNr where DT=DATE_FORMAT(CURDATE(),'%Y%m%d');
if n is null then
begin
insert into NextSerialNr values(CURDATE(),2);
set n=1;
end;
end if;
set NEW.orders_id=concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),right(10000+n,4));
END;
$$
delimiter ;
insert into orders(customer_name) value('jack');
insert into orders(customer_name) value('jason');select * from 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;
我用navicat生成的代码,无法保存。
是不是要写成:set n=n+1;