不需要再创建一个表,根据表的auto_increment值max()会出现问题,这样做:
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
DECLARE next_id;
SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
SET NEW.field = next_id;
END
我声明 next_id 变量,因为通常它会以其他方式使用(*),但你可以直接 new.field=(select ...)
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
END
另外,在(SELECT 字符串字段)的情况下,您可以使用 CAST 值;
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
SET NEW.field=CAST((SELECT aStringField FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl') AS UNSIGNED);
END
(*) 自动命名图像:
SET NEW.field = CONCAT('image_', next_id, '.gif');
(*) 创建哈希:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );