如何在MySQL表中插入多行并返回新的ID？

Question

通常我可以将一行数据插入到MySQL表中，并获得last_insert_id。但现在，我想批量插入许多行到表中，并返回一个ID数组。有人知道我怎样做吗？

有一些类似的问题，但它们并不完全相同。我不想将新的ID插入任何临时表中，我只想得到ID数组。

我能从批量插入中检索lastInsertId吗？

Mysql使用insert-select语句进行多行插入并获取last_insert_id()

- Peacemoon

---

为什么不能一个一个地插入它们？ - sanmai

你需要模拟 OUTPUT 子句。我认为你可以在 MySQL 中使用触发器来实现。 - Martin Smith

可能是MySQL中的OUTPUT子句的重复问题。 - Martin Smith

@Martin：实际上，我对这个问题非常好奇。在Postgres中，您可以编写“INSERT..RETURNING *”来返回您刚刚插入的所有行。MySQL没有这样的子句。使用JDBC，您几乎总是可以获取所有自动生成的ID，但这里并不一定是关于JDBC的。让我们等着瞧。 - Lukas Eder

不行-那种方法也有缺陷-除非你将插入语句放在LOCK TABLES ... WRITE 中，而且你还应该考虑auto_increment_increment。 - symcbean

Answer 1

虽然这是一则旧帖，但我查阅了相关资料并整理了以下内容：如果你使用的是MySQL最新版本上的InnoDB引擎，你可以通过LAST_INSERT_ID()和ROW_COUNT()函数获取ID列表。

InnoDB保证在进行批量插入时，当innodb_autoinc_lock_mode设置为0（传统模式）或1（连续模式）时，AUTO INCREMENT会分配连续的数字。因此，你可以通过LAST_INSERT_ID()获取到第一个ID，并通过加上ROW_COUNT()-1获取到最后一个。

- Dag Sondre Hansen

---

您需要进行交易吗？如果在交易过程中插入了其他数据，会怎样呢？ - user317033

只要使用InnoDB存储引擎并确保启用了auto_increment锁定（这可能会导致性能变慢），就不需要显式事务定义。由于InnoDB在插入期间执行锁定，其他插入必须等待插入完成。请参阅http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html。 - Dag Sondre Hansen

@Wulf 我假设（assume underlined）它指的是INSERT、LOAD DATA、REPLACE INTO等操作。 - Dag Sondre Hansen

@Adders 这是简单的数学问题：LAST_INSERT_ID 保存第一个 ID。例如，如果只返回一行，则第一个值为 LAST_INSERT_ID，最后一个值为 LAST_INSERT_ID + 1 - 1（相同的 ID）。 - Dag Sondre Hansen

我认为值得注意的是，如果您依赖这样的方法来获取多个插入ID，则在需要使用多主集群（例如Galera Cluster）时，您将无法使用该方法。 - JohnK

Answer 2

我能想到的唯一方法是，为插入的每组行存储一个唯一标识符 (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';

你也可以通过在数据库中使用uuid()生成guid。

- Kevin Burton

Answer 3

假设我们有一个名为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;

- sundeep

Answer 4

值得注意的是，如果你把innodb_autoinc_lock_mode设置为2，则可以通过在插入之前锁定表来实现@Dag Sondre Hansen的答案。

LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = LAST_INSERT_ID();
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);

这里有一个演示，它展示了如何实现： https://www.db-fiddle.com/f/ahXAhosYkkRmwqR9Y4mAsr/0

- But those new buttons though..

Answer 5

我不能确定自增值会按照1递增，如果你的数据库有主/主复制并且要解决自增重复问题，则会出现巨大问题。AI将会增加2而不是1，如果另外有一个主库将会增加3。因此，依赖AUTO_INCREMENT这样的东西增加1会破坏你的项目。

我只看到一些好的选项可以解决这个问题。

这个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;

如果您需要使用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

这两种情况都可以安全地进行事务处理。第一种情况只会显示已插入的记录，而第二种情况将显示所有记录，包括更新的记录。

此外，即使使用INSERT IGNORE选项，这些选项也能正常工作...

- Neznajka

Answer 6

对于使用JDBC的Java开发者来说，这是可能的。我通过批量插入的方式获取ID，就像这样：

PreparedStatement insertBatch = null;
Connection connection = ....;

for (Event event : events) {
    
        if (insertBatch == null){
            insertBatch = connection.prepareStatement("insert into `event` (game, `type`, actor, target, arg1, arg2, arg3, created) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        }
        
        insertBatch.setObject(1, event.game);
        insertBatch.setString(2, event.type);
        insertBatch.setObject(3, event.actor);
        insertBatch.setObject(4, event.target);
        insertBatch.setString(5, event.arg1);
        insertBatch.setObject(6, event.arg2);
        insertBatch.setObject(7, event.arg3);
        insertBatch.setTimestamp(8, new Timestamp(event.created.getTime()));
        insertBatch.addBatch();
    }
}

if (insertBatch != null){
    insertBatch.executeBatch();
    ResultSet generatedKeys = insertBatch.getGeneratedKeys();
    
    for (Event event : events) {

        if ( generatedKeys == null || ! generatedKeys.next()){
            logger.warn("Unable to retrieve all generated keys");
        }
        event.id = generatedKeys.getLong(1);
    }
    
    logger.debug("events inserted");
}

源代码："使用MySQL，我可以通过JDBC这种方式来实现：" - Plap - https://groups.google.com/g/jdbi/c/ZDqnfhK758g?pli=1

我必须将rewriteBatchedStatements=true添加到我的JDBC URL中，否则实际插入的行会显示在mysql的“常规查询日志”中。插入7000行时，常规插入需要2m11s，没有重写需要46秒，有重写只需要1.1秒。此外，它不会阻塞其他人的插入（我测试过）。当我插入200k行时，它们被分组为每行约36k个，即insert into abc(..) values(..),(..),(..)...。

我实际上正在使用JDBCTemplate，因此访问PreparedStatement的方法是：

ArrayList<Long> generatedIds = (ArrayList<Long>) jdbcTemplate.execute(
    new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            return connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
        }
    },
    new PreparedStatementCallback<Object>() {
        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            // see above answer for setting the row data
            ...
            ps.executeBatch();

            ResultSet resultSet = ps.getGeneratedKeys();
            ArrayList<Long> ids = new ArrayList<>();
            while (resultSet.next()) {
                ids.add(resultSet.getLong(1));
            }
            return ids;
        }
    }
);

- Curtis Yallop

Answer 7

这个帖子很旧，但是所有的解决方案都没有帮助到我，所以我自己想出了一个解决方法。

首先，计算需要插入多少行。

假设我们需要添加5行：

LOCK TABLE tbl WRITE;

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME   = 'tbl'

然后使用刚选择的auto_increment值执行下一个查询：

ALTER TABLE tbl AUTO_INCREMENT = {AUTO_INCREMENT}+5;
UNLOCK TABLES;

最后进行插入操作

使用保留的自增范围来插入带有id的数据。

警告：此解决方案需要对表进行提高访问级别的访问权限。但通常批量插入是由cron、导入脚本等运行的，这些可能会使用特殊的访问权限。您不会仅用于少量插入操作。

如果使用ON DUPLICATE KEY UPDATE，可能会留下未使用的id。

- Paul G Mihai

Answer 8

我认为你需要在你的应用程序中处理事务ID或项目ID，以便无缝地完成此操作。

一种可能有效的方法（假设所有插入都成功！）是：

您可以使用循环获取插入的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;

在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++;
}

查询被分开的原因是因为如果不使用我的函数，我将无法得到结果。如果您使用标准函数进行此操作，则可以将其放回一个语句中，然后检索所需的结果（应该是第二个结果-假设您使用处理多个结果集/查询的扩展程序）。

- Morg.

Answer 9

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

- Ajayendra

---

这不是批量插入查询。 - Peacemoon