在多个表上使用LAST_INSERT_ID（）进行MySQL批处理插入 [英] MySQL batch insert on multiple tables with LAST_INSERT_ID()

问题描述

我试图插入很多用户到一个MySQL数据库有两个表：

第一个表包含用户数据。示例 INSERT 看起来像这样（ id 是主键，邮件是唯一键）：

  INSERT INTO用户（id，邮件，名称）
 VALUES ，[e-mail]，John Smith）
 ON DUPLICATE KEY UPDATE name = VALUE（name）
  

第二个表包含用户所属的组。它只存储两个外键 users_id 和 groups_id 。示例查询如下所示：

  INSERT INTO users_groups（users_id，groups_id）
 VALUES（LAST_INSERT_ID ）
  

此设置对小数据集完全正常。当我导入大量数据（> 1M行）时， INSERT 会变慢。显然，做一个批处理插入是更好的：

  INSERT INTO用户（id，邮件，名称）
 VALUES（NULL，[e-mail]，John Smith），（NULL，[e-mail]，Anna Smith）
 ON DUPLICATE KEY UPDATE name = VALUE（name）
  

和：

  INSERT INTO users_groups（users_id，groups_id）
 VALUES（LAST_INSERT_ID（），1），（LAST_INSERT_ID（），4）
   
 
当然问题是 LAST_INSERT_ID（）只返回一个批次的ID（第一个） INSERT 。
 
所以，我需要的是一个嵌套批次 INSERT ，IMO不存在MySQL 
 我可以做些什么来使我的 INSERT 更快？ p> 
 
解决方案
默认情况下批量插入提供顺序自动递增，这个知识你可以做插入操作; 
  INSERT INTO用户（id，邮件，名称）
 VALUES（NULL，[e-mail]，John Smith），
（NULL，[e-mail]，Anna Smith），
（...）＃repeat n-times 
; 
 
 SET @ LASTID = LAST_INSERT_ID（）
; 
 
 INSERT INTO users_groups（users_id，groups_id）
 VALUES（@LASTID  -  n，1），＃注意n按降序排列
（@LASTID  -  n-1,1） ，
 ... 
（@LASTID  -  1,1），
（@LASTID  -  0，4）
; 
  
有关批量插入和自动递增的更多信息，请参阅 http://dev.mysql.com/doc/refman/5.1/en/innodb -auto-increment-handling.html  
 
 
 
重要的是，确保innodb_autoinc_lock_mode = 1 
 显示全局变量如'innodb_autoinc_lock_mode'
  
  LOCK TABLES  
  LOCK TABLES tbl_name WRITE 
 。sqls ... 
 UNLOCK TABLES 
  
 
I am trying to insert a lot of users into a MySQL database with two tables:

The first table contains the user data. An example INSERT looks like this (id is the primary key, mail is a unique key):
INSERT INTO users (id, mail, name)  
VALUES (NULL, "[e-mail]", "John Smith") 
ON DUPLICATE KEY UPDATE name = VALUE(name)
The second table contains the group the user belongs to. It only stores two foreign keys users_id and groups_id. An example query looks like this:
INSERT INTO users_groups (users_id, groups_id)
VALUES (LAST_INSERT_ID(), 1)
This setup works perfectly fine for small data sets. When I import large amounts of data (>1M rows) the INSERTs get slow. Obviously, it would be much better to do a batch insert:
INSERT INTO users (id, mail, name)  
VALUES (NULL, "[e-mail]", "John Smith"), (NULL, "[e-mail]", "Anna Smith") 
ON DUPLICATE KEY UPDATE name = VALUE(name)
and:
INSERT INTO users_groups (users_id, groups_id)
VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 4)
The problem of course is, that LAST_INSERT_ID() only returns one (the first) id of a batch INSERT.

So, what I would need is a "nested" batch INSERT, which IMO does not exist in MySQL.

What can I do to make my INSERTs faster?
解决方案
Bulk inserts by default provide sequential auto increments, with this knowledge you can do your inserts like;
INSERT INTO users (id, mail, name)  
VALUES  (NULL, "[e-mail]", "John Smith"), 
        (NULL, "[e-mail]", "Anna Smith"),
        (...)  # repeat n-times
;

SET @LASTID=LAST_INSERT_ID()
;

INSERT INTO users_groups (users_id, groups_id)
VALUES    (@LASTID - n  , 1), # Note n in descending sequence
          (@LASTID - n-1, 1),
          ...
          (@LASTID - 1  , 1), 
          (@LASTID - 0  , 4)
;
For more information on  bulk inserts and auto increment have a look at http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Importantly, make sure that innodb_autoinc_lock_mode=1
show global variables like 'innodb_autoinc_lock_mode'
Otherwise consider wrapping your inserts in LOCK TABLES
LOCK TABLES tbl_name WRITE
... sqls ...
UNLOCK TABLES


                        
这篇关于在多个表上使用LAST_INSERT_ID（）进行MySQL批处理插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！