使用 LAST_INSERT_ID() 在多个表上批量插入 MySQL

Question

我正在尝试将大量用户插入到具有两个表的 MySQL 数据库中:

第一个表包含用户数据。 INSERT 的示例如下所示(id 是主键，mail 是唯一键):

INSERT INTO users (id, mail, name)  
VALUES (NULL, "foo@bar.tld", "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(), 1)

此设置非常适用于小型数据集。当我导入大量数据(>1M 行)时，INSERT 会变慢。显然，批量插入会好得多:

INSERT INTO users (id, mail, name)  
VALUES (NULL, "foo@bar.tld", "John Smith"), (NULL, "baz@qux.tld", "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() 只返回批处理 INSERT 的一个(第一个)id。
所以，我需要的是一个“嵌套”批处理 INSERT，IMO 在 MySQL 中不存在。

我该怎么做才能使我的INSERT速度更快？

Answer 1

默认情况下，批量插入提供顺序自动增量，有了这些知识，您可以像这样进行插入；

INSERT INTO users (id, mail, name)  
VALUES  (NULL, "foo@bar.tld", "John Smith"), 
        (NULL, "baz@qux.tld", "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)
;

有关批量插入和自动增量的更多信息，请查看 http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

重要的是，确保innodb_autoinc_lock_mode=1

show global variables like 'innodb_autoinc_lock_mode'

否则考虑将您的插入包装在 LOCK TABLES

LOCK TABLES tbl_name WRITE
... sqls ...
UNLOCK TABLES