使用Mysql对链接表执行多个INSERT [英] Using Mysql to do multiple INSERT on linked tables
问题描述
我有两个表，一个表链接到另一个表的主键.现在，我插入表A，获取LAST_INSERT_ID，然后插入表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)等);或类似的内容.
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
但是我仍然需要在其中插入相当数量的链接记录，因此我执行以下操作:
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
id的范围就从id到id + N:
ids range from id to id+N as long as we use InnoDB tables:
与多个记录一起使用的MySQL LAST_INSERT_ID()声明
使用了MySQL LAST_INSERT_ID()具有多个记录的INSERT语句
http://gtowey.blogspot.com/2012 /02/multi-insert-and-lastinsertid.html
然后
插入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.
这篇关于使用Mysql对链接表执行多个INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
