mysql使用存储过程插入千万条测试数据

本篇博文的mysql版本：5.7.2

准备数据表

CREATE TABLE `emp`  (
  `id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

1、定义一个函数用来随机生成一个字符串

-- 创建一个函数 用来随机生成一个指定个数的字符串

delimiter $$ 
create function rand_str(n int) returns varchar(255)

begin
-- 声明一个str 
declare str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- 记录当前是第几个
declare i int default 0;
-- 生成的结果
declare res_str varchar(255) default '';
while i < n do
-- 随机生成一个指定个数的字符串 a ab abc 
set res_str = CONCAT(res_str,substr(str,floor(1+RAND()*52),1));
set i = i+1;
end while;
return res_str;
end$$
delimiter;

select rand_str(5);

将以上代码在mysql中运行一下。

如果运行过程中报以下错误：

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 
logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

报错截图如下：

报以上错误的原因是：mysql开启了bin-log日志，开启了bin-log日志后，调用存储过程或者函数以及触发器时，会出现错误号为1418的错误。

详情报错原因可查看：https://blog.csdn.net/ty_soft/article/details/6940190

解决办法有N种方式，这里使用以下方式进行解决，在mysql上运行如下语句：

SET GLOBAL log_bin_trust_function_creators = 1;

运行完毕后，再次执行上面的第1步（1、定义一个函数用来随机生成一个字符串）

为什么要创建一个这样的函数？因为下面的存储过程中会用到该函数。

2、定义一个存储过程用来插入指定条数的测试数据

delimiter $$
create procedure insert_emp(in startNum int, in max_num int) -- startNum如果传入的是100 则表示表中的id字段的值从100开始 max_num如果传入的值是10000000则表示最大插入1000万条数据

begin

-- 声明一个变量，记录当前是第几条数据
declare i int default 0;

-- 默认情况下是自动提交sql语句的
set autocommit = 0; -- 取消sql语句的自动提交,当sql条数达到了max_num数量的时候，统一 一次性提交这些sql语句

-- repeat和while循环的作用基本上都差不多,这个repeat也是循环的作用
repeat
set i = i + 1;

-- 开始插入数据
insert into emp values(startNum + i,rand_str(5),FLOOR(10 + RAND() * 30)); -- rand_str(5)这个函数就是上面第1步中创建的函数


until i = max_num -- 当变量i = max_num这个变量的时候  就退出repeat循环,注意:max_num后面没有分号
end repeat; 

commit; -- repeat循环完毕之后 才将生成的sql语句一次性提交,这样才能提高插入的效率,要是生成一条sql就提交一次。。生成一千万的数据就太慢了。。

end$$

delimiter ;

将以上代码在mysql中运行一下。

3、调用存储过程插入数据

call insert_emp(100, 100000); -- 让插入的id从100开始，插入10万条数据
call insert_emp(100, 1000000); -- 让插入的id从100开始，插入100万条数据
call insert_emp(100, 10000000); -- 让插入的id从100开始，插入1000万条数据

-- 以上三个调用方式任选其一即可。或者你可以在写一个调用，插入500万条测试数据，都行 主要看你给insert_emp这个存储过程传入的第二个参数值,这里我选的是第三个调用方式,也就是插入的数据是1000万条数据,然后剩下的就是等待即可，等待时间可能是十几分钟或几十分钟,主要看自己服务器的配置。

N分钟之后。。。统计一下表中的数据，截图如下：