如何在MySQL表中插入许多行并返回新的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?

通常，我可以在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?

我可以从大容量插入中检索lastInsertId吗?

Mysql mulitple row insert-select statement with last_insert_id()

使用last_insert_id()的Mysql mulitple行插入-选择语句

5 个解决方案

#1

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()和ROW_COUNT()来获得IDs列表。

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.

InnoDB保证在进行批量插入时自动递增的序列号，前提是innodb_autoinc_lock_mode被设置为0(传统)或1(连续)。因此，您可以通过添加ROW_COUNT()()-1从LAST_INSERT_ID()获得第一个ID，最后一个ID。

#2

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()

您还可以使用uuid()在数据库中生成guid

#3

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的表，它有两个cols uid, col1中uid是一个自动递增字段。执行如下操作将返回resultset中插入的所有id。您可以循环遍历resultset并获得您的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;

#4

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的调用+将resultset转换为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号(应该是结果——假设你使用一个扩展处理多个结果集/查询)。

#5

-7

$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);

#1