对,插入后,用LAST_INSERT_ID()取得auto_increment的值LAST_INSERT_ID(), LAST_INSERT_ID(expr) For MySQL 5.1.12 and later, LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this: mysql> SELECT LAST_INSERT_ID();
--订单表主键的递增形式如O00000001create trigger trOrderNo after insert on OrderMaster for each row begin set OrderMaster.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0')); end; 这个触发器怎么不能通过呀?ERROR 1193 (HY000): Unknown system variable 'orderNo'
create trigger trOrderNo after insert on OrderMaster for each row begin set OrderMaster.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0')); end;
create trigger trOrderNo before insert on OrderMaster for each row set new.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0'));
但用触发器实现这个并不可靠,特别是在并发的情况下.建议还是用 auto_increment自动增长
我是要生成一个订单编号,create table orderMaster ( orderID int(9) primary key auto_increment, orderNo char(9) not null, /*订购单编号*/ goodsNo char(9) not null, /*商品编号*/ salePrice numeric(7,2) null, /*销售价格*/ orderQuatity int null, /*订购数量*/ ...); 已经有一个auto_increment的属性列了
after insert on OrderMaster
for each row
begin
set OrderMaster.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0'));
end;
这个触发器怎么不能通过呀?ERROR 1193 (HY000): Unknown system variable 'orderNo'
after insert on OrderMaster
for each row
begin
set OrderMaster.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0'));
end;
before insert on OrderMaster
for each row
set new.orderNo=concat('O',lpad(cast(last_insert_id() as char),8,'0'));
(
orderID int(9) primary key auto_increment,
orderNo char(9) not null, /*订购单编号*/
goodsNo char(9) not null, /*商品编号*/
salePrice numeric(7,2) null, /*销售价格*/
orderQuatity int null, /*订购数量*/
...);
已经有一个auto_increment的属性列了
(
orderID int(9) primary key auto_increment,
orderNo char(9) not null, /*订购单编号*/
goodsNo char(9) not null, /*商品编号*/
salePrice numeric(7,2) null, /*销售价格*/
orderQuatity int null, /*订购数量*/
...
orderDate date,
...);你的表中添加这个时间字段,然后在查询的时候 select concat(date_format(orderDate ,'%Y%m%d%H'),100000000+orderID)as orderNo , *
尝试了下写触发器也可以.BEGIN
Set New.uid = Concat('DM',DATE_FORMAT(NOW(),'%y%m%d%H%i%s'),Lpad(Cast(Floor(Rand()*100) as char), 2 ,'0') );
END应该可以..