使用 Mysql 对链接表执行多个 INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10075279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Mysql to do multiple INSERT on linked tables
提问by Michael Franze
I have two tables, one linked to the Primary Key of the other. At the moment I INSERT into table A, get the LAST_INSERT_ID, and then INSERT into table B.
我有两个表，一个链接到另一个的主键。在我 INSERT 到表 A 的那一刻，获取 LAST_INSERT_ID，然后 INSERT 到表 B。
But I have hundreds of records to insert and I want to speed things up.
但是我有数百条记录要插入，我想加快速度。
In Mysql you can either:
在 Mysql 中，您可以：
INSERT INTO table_a (v1, v2, c3) VALUE (0, 1, 2);
INSERT INTO table_a (v1, v2, v3) VALUE (4, 5, 6);
etc, or
等，或
INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etcto add multiple entries faster - but only for one table.
INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etc更快地添加多个条目 - 但仅限于一张表。
Of course the latter is much faster. I was wondering whether it was possible to replicate this behaviour for my example with two linked tables using a Stored Procedure, and whether it would have a similarly dramatic improvement in performance:
当然后者要快得多。我想知道是否可以使用存储过程在我的示例中使用两个链接表复制这种行为，以及它是否会在性能上有类似的显着改进：
something like: call special_insert((0, 1, 2), (4, 5, 6), etc); or similar.
类似：调用 special_insert((0, 1, 2), (4, 5, 6), etc); 或类似。
I have no Stored Procedure experience, so I'm fishing for ideas on which direction to proceed in.
我没有存储过程经验，所以我正在寻找关于继续前进的方向的想法。
采纳答案by Michael Franze
After some further investigation it appears as if SP would not offer significant speed improvements and cannot accept bulk parameters like INSERT INTO
经过一些进一步的调查，似乎 SP 不会提供显着的速度改进并且不能接受诸如 INSERT INTO 之类的批量参数
MySQL Stored Procedure vs. complex query
But I still needed to insert a fairly large number of linked records in one so I did the following:
但是我仍然需要在一个记录中插入相当多的链接记录，所以我做了以下事情：
INSERT INTO a (x, y) VALUES (1,2), (3,4), (5,6), ... (N-1, N)
插入 (x, y) 值 (1,2), (3,4), (5,6), ... (N-1, N)
id = GET_LAST INSERT_ID
id = GET_LAST INSERT_ID
ids range from id to id+N as long as we use InnoDB tables:
只要我们使用 InnoDB 表，id 的范围从 id 到 id+N：
MySQL LAST_INSERT_ID() used with multiple records INSERT statement
MySQL LAST_INSERT_ID() 与多条记录 INSERT 语句一起使用
MySQL LAST_INSERT_ID() used with multiple records INSERT statement
MySQL LAST_INSERT_ID() 与多条记录 INSERT 语句一起使用
http://gtowey.blogspot.com/2012/02/multi-insert-and-lastinsertid.html
http://gtowey.blogspot.com/2012/02/multi-insert-and-lastinsertid.html
and then
进而
INSERT INTO b (a_id, z) VALUES (id,2), (id+1,4), (id+2,6), ... (id+N, 11) only gotcha is you need to know your mysql increment increment from replication.
INSERT INTO b (a_id, z) VALUES (id,2), (id+1,4), (id+2,6), ... (id+N, 11) 唯一的问题是你需要知道你的mysql从复制增量增量。
回答by eabraham
Here is an example of a store procedure with a two table insert including Last_Insert_ID().
下面是一个带有两个表插入的存储过程示例，包括 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', '[email protected]', 1);
