使用MySQL在关联表上执行多个INSERT操作

Question

我有两个表，一个与另一个的主键相关联。目前，我先将数据插入到表A中，获取LAST_INSERT_ID，然后再插入到表B中。

但是我需要插入数百条记录，我想加快速度。

在Mysql中，您可以选择以下方法之一：

INSERT INTO table_a (v1, v2, c3) VALUE (0, 1, 2);

INSERT INTO table_a (v1, v2, v3) VALUE (4, 5, 6); 

INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etc 这个语句可以更快地添加多个条目 - 但仅适用于一个表。

当然，后者要快得多。 我在想是否可能使用存储过程来复制这种行为以处理两个相关联的表，并且是否会有类似的性能显着提升：

例如：调用special_insert((0, 1, 2), (4, 5, 6), etc); 或类似的命令。

我没有存储过程经验，所以希望了解如何继续前进。

- Michael Franze

---

SP可以让您模拟多表单体插入。问题是...您如何在参数列表中分离表A数据和表B数据。 - Marc B

编写自己的存储过程，我会知道参数列表中的数据意义... 存储过程是否比单独的插入语句快得多？ - Michael Franze

我不清楚具体情况。您需要对这两种方法进行基准测试，但最终仍需执行两个插入操作，无法避免这个事实。 - Marc B

Answer 1

这是一个存储过程示例，包含了两个表的插入操作和 Last_Insert_ID()。

DELIMITER //
CREATE PROCEDURE new_person(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO person(firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

CALL new_person('Jerry', 'Fernholz', '[e-mail]', 1);

- eabraham

---

这是一个非常简洁的 SP 示例，谢谢，我很感激。可以传递数组吗，例如：CALL new_person(('Jerry'、'Fernholz'、'[e-mail]'、1)、('Bob'、'Smith'、'[e-mail]'、2)、('Sally'、'Wood'、'[e-mail]'、2)、('Will'、'Black'、'[e-mail]'、3));？ - Michael Franze

不，无论您使用什么语言，都应该在每次迭代数组时调用SP。 - eabraham

Answer 2

经过进一步调查，似乎存储过程（SP）不会提供显著的速度提升，并且无法接受像INSERT INTO这样的批量参数。

但是，我仍然需要在一个表中插入相当数量的关联记录，因此我采取了以下措施：

INSERT INTO a (x, y) VALUES (1,2), (3,4), (5,6), ... (N-1, N)

id = GET_LAST INSERT_ID

只要我们使用InnoDB表，id范围从id到id+N： MySQL LAST_INSERT_ID() used with multiple records INSERT statement MySQL LAST_INSERT_ID() used with multiple records INSERT statement

http://gtowey.blogspot.com/2012/02/multi-insert-and-lastinsertid.html

然后：

将(a_id, z)插入到b表中，值分别为(id,2), (id+1,4), (id+2,6), ... (id+N, 11) 唯一需要注意的是你需要知道你的MySQL复制增量。

- Michael Franze

Answer 3

这是一个MySQL存储过程，我们可以按照定义创建成千上万条记录。

drop procedure batchInsertUser;
call batchInsertUser(2);

DELIMITER ;;
CREATE PROCEDURE batchInsertUser(totalRecs int)
BEGIN
  SET @createdRecs = 0;
  SET @maxDataId = (SELECT MAX(id) FROM userTable);
  WHILE @createdRecs < totalRecs DO
    SET @createdRecs = @createdRecs + 1;
    SET @maxDataId = @maxDataId + 1;
    SET @userName = CONCAT('batch', @maxDataId );
    INSERT INTO userTable (id, username, created_at, updated_at)
      VALUES (@maxUserId,  @userName, now(), now());
    INSERT INTO userProfile
      (user_id, gender, birthday, created_at, updated_at)
      VALUES
      (@maxUserId, @userName, 'F', '1999-01-10', now(), now());
END ;;

- Bơ Loong A Nhứi