php - BEFORE UPDATE 触发器会影响 mysqli_insert_id 结果吗？

Question

我有一个 mysql 表，它附加了一个触发器，可以将该表中的更改记录到第二个表中

CREATE TRIGGER log_table BEFORE UPDATE ON table1
  FOR EACH ROW BEGIN
    INSERT INTO log_table(filed) VALUES(NEW.field);
  END;
//

现在，如果我从 PHP 执行 INSERT INTO table1，然后调用 mysqli_insert_id()。

这会返回 table1 中的新 ID 吗？或者log_table中的新ID？

Answer 1

在 INSERT 语句之后调用的

LAST_INSERT_ID() 将返回 table1(INSERT 语句)中的新 ID，而不是 log_table(触发器)中的 ID。

这记录在 LAST_INSERT_ID 的手册部分中:

Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:

• If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value is seen by statements that follow the procedure call.

• For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.