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

问题描述：

想象一下，我有一个MySQL表(tbl_test)，其中包含以下字段: id，标题，优先级.
id 将自动增加.插入后，我需要使用与 id 字段相同的值填充优先级字段.
由于我是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.

示例 :

Example:

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() ，

Observations:
As per mysql documentation on 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.

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

相关推荐