MySQL 触发器将字段更新为 id 的值 [英] MySQL trigger to update a field to the value of id

问题描述

我想要一个触发器来对插入的记录执行以下操作:

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

更清楚一点:假设我有一个包含三列的表:id 主键、group_id int、value varchar.

More clearly: say I have one table with three columns: id primary key, group_id int, value varchar.

当我像这样插入 group_id 时:

When I insert with group_id like that:

INSERT INTO table(value, group_id) VALUES ('a', 10)

我想要:

id | group_id | value
---+----------+------
 1 |       10 | a

但是当我省略 group_id:

INSERT INTO table(value) VALUES ('b')

应该自动设置为这条记录的id:

it should be automatically set to the id of this record:

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.)

推荐答案

我不知道有什么方法可以在一个语句中做到这一点，即使使用触发器也是如此.

I don't know of any way to do this in one statement, even using a trigger.

@Lucky 建议的触发器解决方案在 MySQL 中如下所示:

The trigger solution that @Lucky suggested would look like this in MySQL:

CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
  SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END

但是，有一个问题.在 BEFORE INSERT 阶段，尚未生成自动生成的 id 值.因此，如果 group_id 为 null，则默认为 NEW.id，它始终为 0.

However, there's a problem. 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.

但是如果您在 AFTER INSERT 阶段将此触发器更改为触发，因此您可以访问 NEW.id 的生成值，则无法修改列价值观.

But if you change this trigger to fire during the AFTER INSERT phase, so you have access to the generated value of NEW.id, you can't modify column values.

MySQL 不支持列的 DEFAULT 表达式，因此您也不能在表定义中声明此行为.*更新:MySQL 8.0.13 支持 DEFAULT (<expression>) 但表达式仍然不能依赖于自动增量值(这是 记录).

MySQL doesn't support expressions for the DEFAULT of 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).

唯一的解决办法是先进行INSERT，如果未设置group_id，则立即进行UPDATE更改.p>

The only solution is to do the INSERT, and then immediately do an UPDATE to change the group_id if it's not set.

INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();

这篇关于MySQL 触发器将字段更新为 id 的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！