MySQL 使用存储过程循环插入十万条数据的高效方法

93 阅读 0 评论 62 点赞

我是靠谱客的博主尊敬芹菜，这篇文章主要介绍MySQL 使用存储过程循环插入十万条数据的高效方法，现在分享给大家，希望可以做个参考。

我们先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：

复制代码

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB；

然后，我们往表 t 中插入 10 万行记录，取值按整数递增，即：(1, 1, 1)，(2, 2, 2)，(3, 3, 3) 直到 (100000, 100000, 100000)。

用存储过程来插入数据，这里是常规的语句段：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

delimiter ;; 定义了 sql 语句段的执行开始在开头的 ;; 和结尾的 ;; 之间，否则 sql 语句的执行默认以 ; 结束，我们要确保循环语句能完整运行，而不是只运行一句。

但是，如果机器 IO 性能太差，插十万条要十几二十分钟……

你如果没有设置的话，插入 10W 条数据，默认自动提交 10W 次事务。

所以，我们应该在循环开始之前启动一次事务，循环结束后提交，这样每次 insert 就不会重新启动一个事务再提交了：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
START TRANSACTION;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
commit;
end;;
delimiter ;
call idata();