MySQL 触发器插入之前获取记录id

DELIMITER $
DROP TRIGGER if exists houseInsert$
create trigger houseInsert before insert on house for each row
BEGIN
	DECLARE cityPre varchar(10);
	DECLARE cityId bigint;
	DECLARE nextId int;
	set cityId=(select b.cityId from gisTown a join gisCounty b on a.countyId=b.id where a.id=new.townId);
	CASE cityId
	   WHEN 110100000000 THEN  set cityPre='BJ';
	   WHEN 310100000000 THEN set cityPre='SH';
	   ELSE SIGNAL sqlstate '45001' set message_text="cityId not supported";
	END CASE;
	SET nextId = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='house');
	set new.sn=concat(cityPre,from_unixtime(new.createTime,'-%y-%m-'),nextId);
END$
DELIMITER ;

变量nextId就是新的记录的自增主键。

发表评论

电子邮件地址不会被公开。

*