MySQL MySQL触发器将字段更新为id的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1211792/
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
MySQL trigger to update a field to the value of id
提问by szeryf
I would like to have a trigger to perform following operation for inserted records:
我想要一个触发器来对插入的记录执行以下操作：
# pseudocode
if new.group_id is null
set new.group_id = new.id
else
# don't touch it
end
More clearly: say I have one table with three columns: idprimary key, group_idint, valuevarchar.
更清楚地说：假设我有一个包含三列的表：id主键、group_idint、valuevarchar。
When I insert with group_idlike that:
当我group_id像这样插入时：
INSERT INTO table(value, group_id) VALUES ('a', 10)
I'd like to have:
我想要：
id | group_id | value
---+----------+------
1 | 10 | a
but when I omit group_id:
但是当我省略时group_id：
INSERT INTO table(value) VALUES ('b')
it should be automatically set to the idof this record:
它应该自动设置id为该记录的：
id | group_id | value
---+----------+------
2 | 2 | b
Is it possible with a trigger? (I know I can update the record after inserting but having the trigger would be nicer.)
可以用触发器吗？（我知道我可以在插入后更新记录，但有触发器会更好。）
回答by Bill Karwin
I don't know of any way to do this in one statement, even using a trigger.
我不知道有什么方法可以在一个语句中做到这一点，即使使用触发器也是如此。
The trigger solution that @Lucky suggested would look like this in MySQL:
@Lucky 建议的触发器解决方案在 MySQL 中如下所示：
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END
However, there's a problem. In the BEFORE INSERTphase, the auto-generated idvalue hasn't been generated yet. So if group_idis null, it defaults to NEW.idwhich is always 0.
然而，有一个问题。在该BEFORE INSERT阶段，id尚未生成自动生成的值。因此，如果group_id为空，则默认NEW.id为始终为 0。
But if you change this trigger to fire during the AFTER INSERTphase, so you have access to the generated value of NEW.id, you can't modify column values.
但是，如果您在AFTER INSERT阶段期间将此触发器更改为触发，因此您可以访问 的生成值NEW.id，则无法修改列值。
MySQL doesn't support expressions for the DEFAULTof a column, so you can't declare this behavior in the table definition either. *Update: MySQL 8.0.13 supports DEFAULT (<expression>)but the expression still can't depend on an auto-increment value (this is documented).
MySQL 不支持DEFAULT列的表达式，因此您也不能在表定义中声明此行为。*更新：MySQL 8.0.13 支持，DEFAULT (<expression>)但表达式仍然不能依赖于自动增量值（这是记录在案）。
The only solution is to do the INSERT, and then immediately do an UPDATEto change the group_idif it's not set.
唯一的解决方案是执行INSERT，然后立即执行 anUPDATE以更改group_id如果未设置。
INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();
回答by Gabriel Bouyssou
This works for me
这对我有用
DELIMITER $$
CREATE TRIGGER `myTriggerNameHere`
BEFORE INSERT ON `table` FOR EACH ROW
BEGIN
SET NEW.group_id = IF(NEW.group_id IS NULL, LAST_INSERT_ID()+1, NEW.group_id);
END;
$$
DELIMITER ;
回答by Nabeel Ahmed
I am answering here, as in the accepted answer Bill Karwinstates:
我在这里回答，正如比尔·卡尔文在接受的回答中所说：
In the BEFORE INSERT phase, the auto-generated id value hasn't been generated yet. So if group_id is null, it defaults to NEW.id which is always 0.
在 BEFORE INSERT 阶段，尚未生成自动生成的 id 值。因此，如果 group_id 为 null，则默认为 NEW.id，始终为 0。
I have an answer for it - for OP (and the visitors to come), here are few points: You cannot update the table from where the trigger gets invoked, for it you'll get Error 1442:
我有一个答案 - 对于 OP（以及即将到来的访问者），这里有几点：您无法从调用触发器的位置更新表，因为它会收到错误 1442：
Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
1.to Update the new rowUse BEFORE INSERT ONtrigger, this way you can update all the fields for the new row, which can be accessible via NEW operator i.e.
1.更新新行使用BEFORE INSERT ON触发器，这样您就可以更新新行的所有字段，这些字段可以通过 NEW 运算符访问，即
set NEW.group_id = NEW.id
2.Get auto_increment value before insert:
2.插入前获取auto_increment值：
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='MyTable'
To sum up - the trigger SQL for 'd be something as following:
总而言之 - 'd 的触发器 SQL 如下：
DELIMITER //
DROP TRIGGER IF EXISTS MyTrigger//
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
IF new.group_id IS NULL
set @auto_id := (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA=DATABASE() );
set NEW.group_id = @auto_id;
ENF IF;
END;
//
DELIMITER ;
回答by Justin Giboney
I believe that this will work for you
我相信这对你有用
I have two tables
我有两张桌子
test_b: a_id, value
test_c: a_id, value
And here is a trigger on the insert of test b. It checks to see if a_id is null and if it is it inserts 0
这是测试 b 插入的触发器。它检查 a_id 是否为空，如果是则插入 0
CREATE TRIGGER test_b AFTER INSERT ON test_b
FOR EACH ROW
INSERT INTO test_c (a_id, value) VALUES (IFNULL(NEW.a_id, 0),NEW.value)
回答by Lucky
This trigger should do what you asked.
这个触发器应该按照你的要求做。
CREATE TRIGGER mytrigger BEFORE INSERT ON mytable
IF new.group_id IS NULL
SET new.group_id = new.id
END IF
END;
It's copied from a very similar example in the MYSQL documentationpage.
它是从MYSQL 文档页面中一个非常相似的示例复制而来的。
回答by Tarek Kamil
A trigger seems like overkill in this situation. Simply apply a default.
在这种情况下，触发器似乎有点矫枉过正。只需应用默认值。
CREATE TABLE `test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`value` varchar(100) NOT NULL,
`group_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2'
)
