如何将多行插入 MySQL 表并返回新 ID？

Question

通常我可以在 MySQL 表中插入一行并获取 last_insert_id 。但现在，我想将许多行批量插入到表中并获取 ID 数组。有谁知道我该怎么做？

类似的问题还有一些，但又不完全一样。我不想将新 ID 插入任何临时表；我只想取回 ID 数组。

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

Mysql 多行插入选择语句最后插入id()

Answer 1

旧线程，但只是研究了这个，所以这里是：如果您在最新版本的 MySQL 上使用 InnoDB，您可以使用 LAST_INSERT_ID() 和 ROW_COUNT()< 获取 ID 列表/代码>。

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

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

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;

Answer 4

我不确定自动增量值是否会将项目增加 1。如果您的数据库具有 Master // Master 复制并解决 auto_increment 重复排除问题，将会出现巨大的问题。 AI将是+2而不是+1，而且如果再多一个大师，它也会变成+3。因此，请继续关注诸如 AUTO_INCRMENT 正在上升之类的事情，因为 1 会杀死您的项目。

我只看到一些好的选择来做到这一点。

这个 SQL 片段对于多个 master 来说不会有任何问题，并且会给出良好的结果，直到您只需要插入记录。在没有事务的多个请求上可以捕获其他插入记录。

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 ，这些选项也将起作用...

Answer 5

值得注意的是，如果您将 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

Answer 6

这个线程很旧，但所有这些解决方案都没有帮助我，所以我想出了自己的解决方案。

首先，计算要插入的行数

假设我们需要添加 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;

最后执行您的 操作inserts

使用保留的自增范围以 id 进行插入。

警告：此解决方案需要提升对表的访问级别。但通常批量插入是由 crons 和导入器脚本运行的，无论如何都可能使用特殊访问权限。您不会仅将其用于几次插入。

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

Answer 7

对于任何使用 java 和 JDBC 的人来说，这是可能的。我通过批量插入获取 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

我必须将其实际添加到我的 JDBC url：rewriteBatchedStatements=true。否则，实际插入将作为单独的行显示在 mysql“常规查询日志”中。插入 7000 行后，常规插入需要 2 分 11 秒，没有重写时需要 46 秒，而重写时需要 1.1 秒。而且，它不会阻止其他人的插入（我测试过）。当我插入 200k 行时，它会将它们分组为每行约 36k 行，即 insert into abc(..) value(..),(..),(..)...。

我实际上使用的是 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;
        }
    }
);

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

原因查询被分开是因为我不会使用我自己的函数获得结果，如果您使用标准函数执行此操作，您可以将其放回一个语句中，然后检索您需要的结果（它应该是结果号 2 -假设您使用一个可以处理多个结果集/查询的扩展）。

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