使用 MySQL 触发器插入新记录后更新表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22342853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
update table column after insert new record using MySQL triggers
提问by Mohammad Saberi
Imagine I have a MySQL table (tbl_test) with these fields: id, title, priority.
idwill be incremented automatically. I need to fill priorityfield with a value as same as idfield 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:
想象一下，我有一个 MySQL 表 (tbl_test)，其中包含以下字段：id, title, priority。
id会自动增加。插入后，我需要使用与id字段相同的值填充优先级字段。
由于我是使用 MySQL 触发器的新手，请告诉我我必须为它写些什么。我做了一些事情，但我认为这不是真的：
CREATE TRIGGER 'test' AFTER INSERT ON `tbl_test`
BEGIN
SET new.priority = new.id;
END
Thanks for your assistance.
谢谢你的协助。
回答by Ravinder Reddy
The way you are trying to set value to a column is an update. Because you are doing it after insertoperation is completed.
您尝试为列设置值的方式是更新。因为你是在插入操作完成后才做的。
You actually need a beforetrigger.
你实际上需要一个before触发器。
And to assign the same new auto incremented value of primary key column of same table, you better get it from information_schema.tables.
并且要为同一个表的主键列分配相同的新自动递增值，最好从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.
注意：确保您没有任何具有相同名称和/或操作的预定义触发器。如果有一些，则在创建新的之前删除它们。
Observations:
As per mysql documentation on last_insert_id(),
观察：
根据关于last_insert_id() 的mysql 文档，
"if you insert multiple rows using a single INSERT statement,
LAST_INSERT_ID()returns the value generated for the first inserted row only."
"如果您使用单个 INSERT 语句插入多行，则仅
LAST_INSERT_ID()返回为第一个插入的行生成的值。"
hence, depending on last_insert_id()and auto_incrementfield values in batch inserts seems not reliable.
因此，批量插入中的依赖last_insert_id()和auto_increment字段值似乎不可靠。
回答by Abdul Manaf
I don't think you can do that. An AFTER INSERT trigger cannot modify the same table, neither by issuing an UPDATE nor by something like this:
我不认为你可以这样做。AFTER INSERT 触发器不能修改同一个表，既不能通过发出 UPDATE 也不能通过这样的方式：
DROP TRIGGER new_tbl_test;
DELIMITER $$
CREATE TRIGGER new_tbl_test
AFTER INSERT ON tbl_test for each row
begin
UPDATE tbl_test SET priority = new.id WHERE id = new.id;
END $$
DELIMITER ;
It gives error like
它给出了类似的错误
ERROR 1442 (HY000): Can't update table 'tbl_test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
What you can do, is use a transaction:
您可以做的是使用事务：
Example : Table structure is like below
示例：表结构如下
mysql> show create table tbl_test\G
*************************** 1. row ***************************
Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`title` char(30) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Transaction
交易
START TRANSACTION ;
INSERT INTO tbl_test (title)
VALUES ('Dr');
UPDATE tbl_test
SET `priority` = id
WHERE id = LAST_INSERT_ID();
COMMIT ;
Check data
检查数据
mysql> SELECT * FROM tbl_test;
+----+-------+----------+
| ID | title | priority |
+----+-------+----------+
| 1 | Dr | 1 |
+----+-------+----------+
1 row in set (0.00 sec)
回答by Richard A Quadling
My solution. I initially wanted the table's uniqueID in a navOrder column. But the issues of getting the uniqueID of the table in question whilst in a trigger for a bulk insert was too problematic. So I built a mechanism similar to MSSQL's ROWNUMBER feature where every row is numbered, regardless of the table or schema that the row is in. Whilst the solution I've built below doesn't generate a number for every table (it could if the triggers were added to every table), it solves my problem of needing a unique value for every row.
我的解决方案。我最初希望在 navOrder 列中使用表的 uniqueID。但是在批量插入的触发器中获取相关表的 uniqueID 的问题太成问题了。所以我构建了一个类似于 MSSQL 的 ROWNUMBER 功能的机制，其中每一行都被编号，而不管该行所在的表或模式。虽然我在下面构建的解决方案不会为每个表生成一个数字（如果触发器被添加到每个表中），它解决了我需要每行唯一值的问题。
So, I have 2 tables, each with a BEFORE INSERT trigger that calls upon a User Defined Function (UDF) that will get the next unique sequential number.
因此，我有 2 个表，每个表都有一个 BEFORE INSERT 触发器，该触发器调用将获得下一个唯一序列号的用户定义函数 (UDF)。
I've bulk tested the functionality (inserting 1,000 rows in 1 query, running 1,000 queries, running all that 10 times in parallel) and we use this for a site that experiences around 2,000 real time users a minute and approximately 15,000 inserts a minute. Not a Facebook, but all we use this and it is working for us.
我已经对功能进行了批量测试（在 1 个查询中插入 1,000 行、运行 1,000 个查询、并行运行所有 10 次），我们将其用于每分钟体验大约 2,000 个实时用户和每分钟大约 15,000 个插入的站点。不是 Facebook，但我们都使用它，它对我们有用。
If you run the code below, you'll see that rolling back does NOT rollback the sequential number.
如果您运行下面的代码，您会看到回滚不会回滚序列号。
We get no deadlocks and no duplicate values (as the unique constraint on the navOrder columns does not allow duplicates).
我们没有死锁，也没有重复值（因为 navOrder 列上的唯一约束不允许重复）。
For me this is a relatively easy to understand solution.
对我来说，这是一个相对容易理解的解决方案。
CREATE SCHEMA TestLastInsertId;
USE TestLastInsertId;
CREATE TABLE Table1 (
`tempID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`item` VARCHAR(256) NOT NULL,
`navOrder` INT(11) UNIQUE NOT NULL,
`createdAt` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updatedAt` TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;
CREATE TABLE Table2 (
`tempID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`item` VARCHAR(256) NOT NULL,
`navOrder` INT(11) UNIQUE NOT NULL,
`createdAt` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updatedAt` TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;
CREATE TABLE IF NOT EXISTS `nav_order_sequence` (
`navOrderSequence` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
DELIMITER ;;
CREATE FUNCTION getNextNavOrder()
RETURNS INT(11) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
INSERT INTO nav_order_sequence() VALUES();
SET @navOrder = LAST_INSERT_ID();
DELETE FROM nav_order_sequence WHERE navOrderSequence = @navOrder;
RETURN @navOrder;
END;;
CREATE TRIGGER Table1_BEFORE_INSERT BEFORE INSERT ON Table1 FOR EACH ROW
BEGIN
SET NEW.navOrder = getNextNavOrder();
END;;
CREATE TRIGGER Table2_BEFORE_INSERT BEFORE INSERT ON Table2 FOR EACH ROW
BEGIN
SET NEW.navOrder = getNextNavOrder();
END;;
DELIMITER ;
INSERT INTO Table1(item) VALUES('Item1'),('Item2'),('Item3');
INSERT INTO Table2(item) VALUES('Item4'),('Item5'),('Item6');
SELECT * FROM Table1; -- Result 1
SELECT * FROM Table2; -- Result 2
BEGIN;
INSERT INTO Table1(item) VALUES('Item7'),('Item8'),('Item9');
INSERT INTO Table2(item) VALUES('Item10'),('Item11'),('Item12');
SELECT * FROM Table1; -- Result 3
SELECT * FROM Table2; -- Result 4
ROLLBACK;
INSERT INTO Table1(item) VALUES('Item13'),('Item14'),('Item15');
INSERT INTO Table2(item) VALUES('Item16'),('Item17'),('Item18');
SELECT * FROM Table1; -- Result 5
SELECT * FROM Table2; -- Result 6
DROP SCHEMA TestLastInsertId;
Result 1 - Add 3 rows to Table 1 - navOrders 1, 2, and 3.
1 Item1 1 2019-11-02 18:58:28.657690
2 Item2 2 2019-11-02 18:58:28.657690
3 Item3 3 2019-11-02 18:58:28.657690
Result 2 - Add 3 rows to Table 2 - navOrders 4, 5, and 6.
1 Item4 4 2019-11-02 18:58:28.669873
2 Item5 5 2019-11-02 18:58:28.669873
3 Item6 6 2019-11-02 18:58:28.669873
Result 3 - Add 3 more rows to Table 1 - navOrders 7, 8, and 9.
1 Item1 1 2019-11-02 18:58:28.657690
2 Item2 2 2019-11-02 18:58:28.657690
3 Item3 3 2019-11-02 18:58:28.657690
4 Item7 7 2019-11-02 18:58:28.704766
5 Item8 8 2019-11-02 18:58:28.704766
6 Item9 9 2019-11-02 18:58:28.704766
Result 4 - Add 3 more rows to Table 2 - navOrders 10, 11, and 12.
1 Item4 4 2019-11-02 18:58:28.669873
2 Item5 5 2019-11-02 18:58:28.669873
3 Item6 6 2019-11-02 18:58:28.669873
4 Item10 10 2019-11-02 18:58:28.706930
5 Item11 11 2019-11-02 18:58:28.706930
6 Item12 12 2019-11-02 18:58:28.706930
A rollback happened here, so rows 4, 5, and 6 of both tables are removed.
这里发生了回滚，因此删除了两个表的第 4、5 和 6 行。
Result 5 - Add 3 more rows to Table 1 after a rollback - navOrders 13, 14, and 15.
1 Item1 1 2019-11-02 18:58:28.657690
2 Item2 2 2019-11-02 18:58:28.657690
3 Item3 3 2019-11-02 18:58:28.657690
7 Item13 13 2019-11-02 18:58:28.727303
8 Item14 14 2019-11-02 18:58:28.727303
9 Item15 15 2019-11-02 18:58:28.727303
Result 6 - Add 3 more rows to Table 1 after a rollback - navOrders 16, 17, and 18.
1 Item4 4 2019-11-02 18:58:28.669873
2 Item5 5 2019-11-02 18:58:28.669873
3 Item6 6 2019-11-02 18:58:28.669873
7 Item16 16 2019-11-02 18:58:28.730307
8 Item17 17 2019-11-02 18:58:28.730307
9 Item18 18 2019-11-02 18:58:28.730307
If you were to remove the UNIQUEconstraint on navOrder and replace the function called in the triggers to be LAST_INSERT_ID(), you'll see the duplicate values.
如果您要删除UNIQUE对 navOrder的约束并将触发器中调用的函数替换为LAST_INSERT_ID()，您将看到重复的值。
