使用 MySQL 触发器插入新记录后更新表列

2022-01-01 00:00:00 sql-update insert mysql triggers

假设我有一个 MySQL 表 (tbl_test)，其中包含以下字段:id、title、priority.
id 将自动增加.插入后，我需要用与 id 字段相同的值填充 priority 字段.
由于我是使用 MySQL 触发器的新手，请告诉我我必须为它写些什么.我做了一些事情，但我认为这不是真的:

Imagine I have a MySQL table (tbl_test) with these fields: id, title, priority.
id will be incremented automatically. I need to fill priority field with a value as same as id field after inserting.
As I'm new in using MySQL triggers, please tell me what I have to write for it. I did something , but I think it is not true: 

CREATE TRIGGER 'test' AFTER INSERT ON `tbl_test`
BEGIN
   SET new.priority = new.id;
END

感谢您的帮助.

推荐答案

您尝试为列设置值的方式是更新.因为你是在插入操作完成之后做的.

The way you are trying to set value to a column is an update. Because you are doing it after insert operation is completed.

您实际上需要一个 before 触发器.

You actually need a before trigger.

并且要为同一个表的主键列分配相同的新自增值，最好从information_schema.tables中获取.

And to assign the same new auto incremented value of primary key column of same table, you better get it from information_schema.tables.

示例:

delimiter //
drop trigger if exists bi_table_name //

create trigger bi_table_name before insert on table_name
for each row begin
  set @auto_id := ( SELECT AUTO_INCREMENT 
                    FROM INFORMATION_SCHEMA.TABLES
                    WHERE TABLE_NAME='table_name'
                      AND TABLE_SCHEMA=DATABASE() ); 
  set new.priority= @auto_id;
end;
//

delimiter ;

注意:确保您没有任何具有相同名称和/或操作的预定义触发器.如果有，则在创建新的之前删除它们.

Note: Make sure that you don't have any pre-defined trigger with the same name and/or action. If have some, then drop them before creating the new.

观察:
根据 关于 的 mysql 文档last_insert_id(),

"如果您使用单个 INSERT 语句插入多行，LAST_INSERT_ID() 返回为第一个插入生成的值仅行."

"if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only."

因此，批量插入中依赖于 last_insert_id() 和 auto_increment 字段值似乎不可靠.

hence, depending on last_insert_id() and auto_increment field values in batch inserts seems not reliable.

相关文章