通常我可以将一行数据插入到MySQL表中,并获得last_insert_id
。但现在,我想批量插入许多行到表中,并返回一个ID数组。有人知道我怎样做吗?
有一些类似的问题,但它们并不完全相同。我不想将新的ID插入任何临时表中,我只想得到ID数组。
通常我可以将一行数据插入到MySQL表中,并获得last_insert_id
。但现在,我想批量插入许多行到表中,并返回一个ID数组。有人知道我怎样做吗?
有一些类似的问题,但它们并不完全相同。我不想将新的ID插入任何临时表中,我只想得到ID数组。
虽然这是一则旧帖,但我查阅了相关资料并整理了以下内容:如果你使用的是MySQL最新版本上的InnoDB引擎,你可以通过LAST_INSERT_ID()
和ROW_COUNT()
函数获取ID列表。
InnoDB保证在进行批量插入时,当innodb_autoinc_lock_mode
设置为0(传统模式)或1(连续模式)时,AUTO INCREMENT会分配连续的数字。因此,你可以通过LAST_INSERT_ID()
获取到第一个ID,并通过加上ROW_COUNT()-1
获取到最后一个。
我能想到的唯一方法是,为插入的每组行存储一个唯一标识符 (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。
# 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;
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);
我不能确定自增值会按照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;
#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选项,这些选项也能正常工作...
对于使用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;
}
}
);
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。
我认为你需要在你的应用程序中处理事务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;
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++;
}
$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);
OUTPUT
子句。我认为你可以在 MySQL 中使用触发器来实现。 - Martin Smith