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)

报错截图如下:

m1.png

报以上错误的原因是: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分钟之后。。。统计一下表中的数据,截图如下:

m2.png

1000万条测试数据插入成功啦。


表中大概数据形式如下所示:

m3.png

插入的测试数据大概就是这个样子啦。当然了 代码给你了 你可以改成你自己想要的样子。



任何事情,都有一个过程,坚持一下,再坚持一下,播种和收获,本来就不在一个季节



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与