如何在 MySQL 表中插入多行并返回新 ID?
通常我可以在 MySQL 表中插入一行并取回 last_insert_id.不过，现在我想向表中批量插入多行并取回一组 ID.有谁知道我该怎么做?
Normally I can insert a row into a MySQL table and get the last_insert_id back. Now, though, I want to bulk insert many rows into the table and get back an array of IDs. Does anyone know how I can do this?
有一些类似的问题，但它们并不完全相同.我不想将新 ID 插入任何临时表；我只想取回 ID 数组.
There are some similar questions, but they are not exactly the same. I don't want to insert the new ID to any temporary table; I just want to get back the array of IDs.
我可以从批量插入中检索 lastInsertId 吗?
Mysql 多行插入选择语句，带有 last_insert_id()
推荐答案
旧线程，但只是研究了这个，所以这里是:如果您在最新版本的 MySQL 上使用 InnoDB，您可以使用 <代码>LAST_INSERT_ID() 和 ROW_COUNT().
Old thread but just looked into this, so here goes: if you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT().
InnoDB 在进行批量插入时保证 AUTO INCREMENT 的序列号，前提是 innodb_autoinc_lock_mode 设置为 0(传统)或 1(连续).因此，您可以通过添加 ROW_COUNT()-1 从 LAST_INSERT_ID() 和 last 获取第一个 ID.
InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive).
Consequently you can get the first ID from LAST_INSERT_ID() and the last by adding ROW_COUNT()-1.
相关文章