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.

更明确地说:假设我有一个表，包含三列:id主键、group_id int、值varchar。

When I insert with group_id like 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 id of 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.)

有没有可能有一个触发器?(我知道插入后我可以更新记录，但有触发器会更好。)

5 个解决方案

#1

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

然而,有一个问题。在插入前阶段，还没有生成自动生成的id值。如果group_id为空，则默认为NEW。id总是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.

但是，如果您在INSERT阶段将此触发器更改为fire，那么您就可以访问NEW的生成值。id，不能修改列值。

MySQL doesn't support expressions for the DEFAULT of a column, so you can't declare this behavior in the table definition either.

MySQL不支持默认的列表达式，因此也不能在表定义中声明这种行为。

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

唯一的解决方案是执行插入，然后立即执行更新，如果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();

#2

I am answering here, as in the accepted answer Bill Karwin states:

我在这里回答，正如比尔·卡尔文所说:

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.

在插入前阶段，还没有生成自动生成的id值。如果group_id为空，则默认为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 row Use BEFORE INSERT ON trigger, this way you can update all the fields for the new row, which can be accessible via NEW operator i.e.

1。要在INSERT ON触发器之前更新新行使用，可以用这种方式更新新行的所有字段，这可以通过新操作符来访问。

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 ;

#3

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是否为null，如果是，它插入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)

#4

-2

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 documentation page.

它是从MYSQL文档页面中一个非常相似的示例复制的。

#5

-2

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

#1