如何在 MySQL 表中插入多行并返回新 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7333524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I Insert many rows into a MySQL table and return the new IDs?
提问by Peacemoon
Normally I can insert a row into a MySQL table and get the last_insert_idback. 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?
通常我可以在 MySQL 表中插入一行并last_insert_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.
有一些类似的问题，但它们并不完全相同。我不想将新 ID 插入任何临时表；我只想取回 ID 数组。
Can I retrieve the lastInsertId from a bulk insert?
Mysql mulitple row insert-select statement with last_insert_id()
采纳答案by Dag Sondre Hansen
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().
旧线程，但只是研究了这个，所以这里是：如果您在最新版本的 MySQL 上使用 InnoDB，您可以使用LAST_INSERT_ID()和获取 ID 列表ROW_COUNT()。
InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_modeis set to 0 (traditional) or 1 (consecutive).
Consequently you can get the firstID from LAST_INSERT_ID()and the lastby adding ROW_COUNT()-1.
InnoDB 在进行批量插入时保证 AUTO INCREMENT 的序列号，前提innodb_autoinc_lock_mode是设置为 0（传统）或 1（连续）。因此你可以得到第一，从IDLAST_INSERT_ID()和最后加入ROW_COUNT()-1。
回答by Kevin Burton
The only way I can think it could be done is if you store a unique identifier for each set of rows inserted (guid) then select the row ids. e.g:
我认为可以完成的唯一方法是，如果您为每组插入的行（guid）存储一个唯一标识符，然后选择行 ID。例如：
INSERT INTO t1
(SELECT col1,col2,col3,'3aee88e2-a981-1027-a396-84f02afe7c70' FROM a_very_large_table);
COMMIT;
SELECT id FROM t1
WHERE guid='3aee88e2-a981-1027-a396-84f02afe7c70';
You could also generate the guid in the database by using uuid()
您还可以使用以下命令在数据库中生成 guid uuid()
回答by sundeep
Lets assume we have a table called temptable with two cols uid, col1 where uid is an auto increment field. Doing something like below will return all the inserted id's in the resultset. You can loop through the resultset and get your id's. I realize that this is an old post and this solution might not work for every case. But for others it might and that's why I'm replying to it.
假设我们有一个名为 temptable 的表，它有两个列 uid，col1，其中 uid 是一个自动增量字段。执行以下操作将返回结果集中所有插入的 id。您可以遍历结果集并获取您的 ID。我意识到这是一篇旧帖子，此解决方案可能不适用于所有情况。但对于其他人来说可能是这样，这就是我回复它的原因。
# lock the table
lock tables temptable write;
#bulk insert the rows;
insert into temptable(col1) values(1),(2),(3),(4);
#get the value of first inserted row. when bulk inserting last_insert_id() #should give the value of first inserted row from bulk op.
set @first_id = last_insert_id();
#now select the auto increment field whose value is greater than equal to #the first row. Remember since you have write lock on that table other #sessions can't write to it. This resultset should have all the inserted #id's
select uid from temptable where uid >=@first_id;
#now that you are done don't forget to unlock the table.
unlock tables;
回答by Neznajka
I wouldn't be sure that auto increment value will increase item by 1. and there will be huge problems if your DB will have Master // Master replication and to resolve auto_increment duplicate exclusion. AI will be +2 instead of +1, also if there will be one more master it will come to +3. so relay on thing like AUTO_INCREMENT is going up for 1 is killing your project.
我不确定自动增量值会将 item 增加 1。如果您的数据库将具有 Master // Master 复制并解决 auto_increment 重复排除，则会出现巨大问题。AI 将是 +2 而不是 +1，而且如果再有一位大师，它会变成 +3。所以像 AUTO_INCREMENT 这样的中继上升为 1 正在扼杀你的项目。
I see only some good options to do that.
我只看到一些好的选择来做到这一点。
this SQL snippet will have no problems with multiple masters and give good results until you will need only inserted records. on multiple requests without transactions can catch other inserts records.
此 SQL 代码段对多个母版没有问题，并提供良好的结果，直到您只需要插入的记录。在没有事务的多个请求上可以捕获其他插入记录。
START TRANSACTION;
SELECT max(id) into @maxLastId FROM `main_table`;
INSERT INTO `main_table` (`value`) VALUES ('first'), ('second') ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
SELECT `id` FROM `main_table` WHERE id > @maxLastId OR @maxLastId IS NULL;
COMMIT;
(if you will need also updated records by DUPLICATE KEY UPDATE) you will need to refactor database a bit and SQL will look like next, (safe for transactions and no transactions inside one connection.)
（如果您还需要通过 DUPLICATE KEY UPDATE 更新记录）您需要稍微重构数据库，SQL 将如下所示，（对于事务是安全的，并且在一个连接内没有事务。）
#START TRANSACTION
INSERT INTO bulk_inserts VALUES (null);
SET @blukTransactionId = LAST_INSERT_ID();
SELECT @blukTransactionId, LAST_INSERT_ID();
INSERT INTO `main_table` (`value`, `transaction_id`) VALUES ('first', @blukTransactionId), ('second', @blukTransactionId) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`), `transaction_id` = VALUES(`transaction_id`);
SELECT @blukTransactionId, LAST_INSERT_ID();
SELECT id FROM `main_table` WHERE `transaction_id` = @blukTransactionId;
#COMMIT
both cases are safe to transnational. first will show you only inserted records and second will give you all records even updated.
这两种情况对跨国都是安全的。第一个将只显示插入的记录，第二个会给你所有的记录甚至更新。
also those options will work even with INSERT IGNORE ...
即使使用 INSERT IGNORE ，这些选项也可以使用...
回答by Morg.
I think you will have to either handle the transaction id in your application, or the item id in your application in order to do this flawlessly.
我认为您必须处理应用程序中的事务 ID 或应用程序中的项目 ID，才能完美地完成此操作。
One way to do this which could work, assuming that all your inserts succeed (!), is the following :
假设您的所有插入都成功（！），一种可行的方法是：
You can then get the inserted id's with a loop for the number of affected rows, starting with lastid (which is the first inserted id of the bulk insert). And thus, i checked it works perfectly .. just be careful that HeidiSQL for example will not return the correct value for ROW_COUNT(), probably because it's a crappy GUI doing random shit we don't ask it - however it's perfectly correct from either command line or PHP mysqli -
然后，您可以通过循环获取受影响行数的插入 id，从 lastid（这是批量插入的第一个插入 id）开始。因此，我检查了它是否完美运行……只是要小心，例如 HeidiSQL 不会为 ROW_COUNT() 返回正确的值，可能是因为它是一个蹩脚的 GUI，我们不会问它随机胡说八道 - 但它是完全正确的命令行或 PHP mysqli -
START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');
SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;
COMMIT;
In PHP it looks like this (local_sqle is a straight call to mysqli_query, local_sqlec is a call to mysqli_query + convert resultset to PHP array) :
在 PHP 中它看起来像这样（local_sqle 是对 mysqli_query 的直接调用，local_sqlec 是对 mysqli_query 的调用 + 将结果集转换为 PHP 数组）：
local_sqle("START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');");
$r=local_sqlec("SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;");
local_sqle("
COMMIT;");
$i=0;
echo "last id =".($r[0]['lastid'])."<br>";
echo "Row count =".($r[0]['rowcount'])."<br>";
while($i<$r[0]['rowcount']){
echo "inserted id =".($r[0]['lastid']+$i)."<br>";
$i++;
}
The reason the queries are separated is because I wouldn't otherwise get my result using my own functions, if you do this with standard functions, you can put it back in one statement and then retrieve the result you need (it should be result number 2 - assuming you use an extension which handles more than one result set / query).
查询被分开的原因是因为我不会使用我自己的函数得到我的结果，如果你用标准函数这样做，你可以把它放回一个语句中，然后检索你需要的结果（它应该是结果号2 - 假设您使用处理多个结果集/查询的扩展）。
回答by Ajayendra
$query = "INSERT INTO TABLE (ID,NAME,EMAIL) VALUES (NULL,VALUE1, VALUE2)";
$idArray = array();
foreach($array as $key) {
mysql_query($query);
array_push($idArray, mysql_insert_id());
}
print_r($idArray);
