mysql 存储过程插入慢_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 

-- 随机生成一个指定个数的字符串 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的错误。

解决办法有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分钟之后。。。统计一下表中的数据，截图如下：

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

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

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

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

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