BEFORE UPDATE触发器是否影响mysqli_insert_id结果？

Question

I have a mysql table which has a trigger attached, that logs changes in this table to a second one

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

Now if I perform an INSERT INTO table1 from PHP an call mysqli_insert_id() afterwards.

Would that return the new ID in table1? Or the new ID in log_table?

Answer 1

LAST_INSERT_ID() called after the INSERT statement will return the new ID in table1 (of the INSERT statement), not the one in the log_table (of the trigger).

This is documented in the manual section of 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.