使用Mysql对链接表做多次INSERT
我有两张表，一张链接到另一张的主键.此刻我 INSERT 到表 A，得到 LAST_INSERT_ID，然后 INSERT 到表 B 中.
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.
但我有数百条记录要插入，我想加快速度.
But I have hundreds of records to insert and I want to speed things up.
在 Mysql 中，您可以:
In Mysql you can either:
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 更快地添加多个条目 - 但仅限于一个表.
INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etc to add multiple entries faster - but only for one table.
当然后者要快得多.我想知道是否可以在我的示例中使用存储过程复制此行为，其中包含两个链接表，以及它是否会在性能方面有类似的显着改进:
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:
类似:调用 special_insert((0, 1, 2), (4, 5, 6), etc);或类似的.
something like: call special_insert((0, 1, 2), (4, 5, 6), etc); or similar.
我没有存储过程的经验，所以我正在寻找有关前进方向的想法.
I have no Stored Procedure experience, so I'm fishing for ideas on which direction to proceed in.
推荐答案
经过进一步调查，似乎 SP 不会提供显着的速度改进，并且不能接受像 INSERT INTO 这样的批量参数
After some further investigation it appears as if SP would not offer significant speed improvements and cannot accept bulk parameters like INSERT INTO
MySQL 存储过程与复杂查询
但是我仍然需要在一个中插入相当多的链接记录，所以我做了以下操作:
But I still needed to insert a fairly large number of linked records in one so I did the following:
插入 (x, y) 值 (1,2), (3,4), (5,6), ... (N-1, N)
INSERT INTO a (x, y) VALUES (1,2), (3,4), (5,6), ... (N-1, N)
id = GET_LAST INSERT_ID
id = GET_LAST INSERT_ID
只要我们使用 InnoDB 表，id 的范围从 id 到 id+N:
ids range from id to id+N as long as we use InnoDB tables:
MySQL LAST_INSERT_ID() 用于多条记录 INSERT声明
已使用 MySQL LAST_INSERT_ID()带有多条记录的 INSERT 语句
http://gtowey.blogspot.com/2012/02/multi-insert-and-lastinsertid.html
然后
INSERT INTO b (a_id, z) 值 (id,2), (id+1,4), (id+2,6), ... (id+N, 11)唯一的问题是您需要知道复制中的 mysql 增量.
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.
相关文章