php - 如何在多条记录插入中获取最后插入的id？

Question

我正在尝试编写一个标签系统，我希望允许用户在文本输入中插入多个标签。然后我想在 tag_table 中插入每个标签，并需要将它们的 id 保留在 tag_post_table 中。

我知道在多次插入last_insert_id中返回第一条记录id。我可以向 last_insert_id 添加标签计数以获取真正的最后一个 id 并使用它们之间的每个 id 吗？这可靠吗？

我读了这篇文章，但我不完全理解

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Answer 1

据我了解this article :

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

将innodb_autoinc_lock_mode设置为0或1可保证自动递增的ID是连续的。

因此，您从 LAST_INSERT_ID 获得的 ID 是新 ID 中的第一个，而 LAST_INSERT_ID +“受影响的行数”是新 ID 中的最后一个。

还有LAST_INSTERT_ID不受其他连接影响

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

但请注意，如果您使用 INSERT ... ON DUPLICATE KEY UPDATE 或 INSERT IGNORE ...，可能会出现错误的结果。但我还没有测试过这个。