mysql 触发器 字段,MySQL触发器将字段更新为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

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

When I insert with group_id like that:

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:

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

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.

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

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.

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 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 () but the expression still can't depend on an auto-increment value (this is documented).

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