如何在MySQL表中插入许多行并返回新的ID? [英] How can I Insert many rows into a MySQL table and return the new IDs?
问题描述
通常,我可以在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.
带有last_insert_id的MySQL多行插入选择语句()
推荐答案
旧线程,但只是调查了一下,所以去了:如果您在最新版本的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_autoinc_lock_mode
设置为0(传统)或1(连续),InnoDB保证批量插入时AUTO INCREMENT的序列号.
因此,通过添加ROW_COUNT()-1
,您可以从LAST_INSERT_ID()
获得第一个 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
.
这篇关于如何在MySQL表中插入许多行并返回新的ID?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!