MySQL LAST_INSERT_ID() 与多条记录 INSERT 语句一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4637367/
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
MySQL LAST_INSERT_ID() used with multiple records INSERT statement
提问by bogdan
If I insert multiple records with a loop that executes a single record insert, the last insert id returned is, as expected, the last one. But if I do a multiple records insert statement:
如果我使用执行单个记录插入的循环插入多条记录，则返回的最后一个插入 ID 正如预期的那样是最后一个。但是，如果我执行多条记录插入语句：
INSERT INTO people (name,age)
VALUES ('William',25), ('Bart',15), ('Mary',12);
Let's say the three above are the first records inserted in the table. After the insert statement I expected the last insert id to return 3, but it returned 1. The first insert id for the statement in question.
假设以上三个是插入表中的第一条记录。在插入语句之后，我希望最后一个插入 id 返回 3，但它返回了 1。有问题的语句的第一个插入 id。
So can someone please confirm if this is the normal behavior of LAST_INSERT_ID()in the context of multiple records INSERT statements. So I can base my code on it.
所以有人可以确认这是否是LAST_INSERT_ID()多条记录 INSERT 语句上下文中的正常行为。所以我可以基于我的代码。
回答by Asaph
Yes. This behavior of last_insert_id()is documented in the MySQL docs:
是的。这种行为last_insert_id()是在MySQL的文档记录：
Important
If you insert multiple rows using a singleINSERTstatement,LAST_INSERT_ID()returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the sameINSERTstatement against some other server.
重要
如果您使用单个INSERT语句插入多行，则仅LAST_INSERT_ID()返回为第一个插入的行生成的值。这样做的原因是可以轻松地INSERT针对其他服务器重现相同的语句。
回答by Larry Lustig
回答by Marcel Zúbrik
I think it's possible if your table has unique autoincrement column (ID) and you don't require them to be returned by mysql itself. I would cost you 3 more DB requests and some processing. It would require these steps:
我认为如果您的表具有唯一的自动增量列 (ID) 并且您不需要它们由 mysql 本身返回，这是可能的。我会花费你 3 个数据库请求和一些处理。这将需要以下步骤：
- Get "Before MAX(ID)" right before your insert:
- 在插入之前获取“Before MAX(ID)”：
SELECT MAX(id) AS before_max_id FROM table_name`
Make multiple INSERT ... VALUES () query with your data and keep them:
INSERT INTO table_name (col1, col2) VALUES ("value1-1" , "value1-2"), ("value2-1" , "value2-2"), ("value3-1" , "value3-2"), ON DUPLICATE KEY UPDATEGet "After MAX(ID)" right after your insert:
SELECT MAX(id) AS after_max_id FROM table_name`Get records with IDs between "Before MAX(ID)" and "After MAX(ID)" including:
SELECT * FROM table_name WHERE id>$before_max_id AND id<=$after_max_id`Do a check of retrieved data with data you inserted to match them and remove any records that were not inserted by you. The remaining records have your IDs:
使用您的数据进行多个 INSERT ... VALUES () 查询并保留它们：
INSERT INTO table_name (col1, col2) VALUES ("value1-1" , "value1-2"), ("value2-1" , "value2-2"), ("value3-1" , "value3-2"), ON DUPLICATE KEY UPDATE插入后立即获取“After MAX(ID)”：
SELECT MAX(id) AS after_max_id FROM table_name`获取 ID 介于“Before MAX(ID)”和“After MAX(ID)”之间的记录，包括：
SELECT * FROM table_name WHERE id>$before_max_id AND id<=$after_max_id`使用您插入的数据检查检索到的数据以匹配它们并删除所有未由您插入的记录。其余记录有您的 ID：
foreach ($after_collection as $after_item) {
foreach ($input_collection as $input_item) {
if ( $after_item->compare_content($input_item) ) {
$intersection_array[] = $after_item;
}
}
}
This is just how a common person would solve it in a real world, with parts of code. Thanks to autoincrement it should get smallest possible amount of records to check against, so they will not take lot of processing. This is not the final "copy & paste" code - eg. you have to create your own function compare_content() according you your needs.
这就是普通人在现实世界中使用部分代码解决它的方式。由于自动增量，它应该得到尽可能少的记录来检查，因此它们不会进行大量处理。这不是最终的“复制和粘贴”代码 - 例如。您必须根据需要创建自己的函数 compare_content()。
