Using Mysql to do multiple INSERT on linked tables(使用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.
这篇关于使用Mysql对链接表做多次INSERT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:使用Mysql对链接表做多次INSERT
基础教程推荐
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01