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

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

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)。

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

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 就不会重新启动一个事务再提交了：

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();