MySQL实现批量插入测试数据的方式总结

Mathilda ·

更新时间:2024-05-16

· 1394 次阅读

设置允许创建函数

产生随机字符串

产生随机数字

创建存储过程

插入角色表

插入用户表

执行存储过程

总结

前言

在开发过程中我们不管是用来测试性能还是在生产环境中页面展示好看一点, 又或者学习验证某一知识点经常需要一些测试数据, 这个时候如果手敲的话, 十行二十行还好, 多了就很死亡了, 接下来介绍两种常用的MySQL测试数据批量生成方式

存储方式+函数

Navicat的数据生成

表

准备了两张表角色表:

id: 自增长

role_name: 随机字符串, 不允许重复

orders: 1-1000任意数字

用户表:

id: 自增长

username: 随机字符串, 不允许重复

password: 随机字符串, 允许重复

role_id: 1-10w之间的任意数字

建表语句:

sql

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL COMMENT '用户名', `role_id` int(11) DEFAULT NULL COMMENT '角色id', `password` varchar(255) DEFAULT NULL COMMENT '密码', `salt` varchar(255) DEFAULT NULL COMMENT '盐', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` varchar(255) DEFAULT NULL COMMENT '角色名', `orders` int(11) DEFAULT NULL COMMENT '排序权重\r\n', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

使用函数生成

通过存储过程快速插入, 通过函数保证数据不重复

设置允许创建函数

查看 MySQL是否允许创建函数

sql

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

结果如图所示, 我们使用以下命令将创建函数功能打开（global-所有session都生效）

sql

SET GLOBAL log_bin_trust_function_creators=1;

这个时候再一次查询就会显示已打开

产生随机字符串

sql

-- 随机产生字符串 DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END $$ -- 假如要删除 -- drop function rand_string;

产生随机数字

sql

-- 用于随机产生区间数字 DELIMITER $$ CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)); RETURN i; END$$ -- 假如要删除 -- drop function rand_num;

创建存储过程 插入角色表

sql

-- 插入角色数据 DELIMITER $$ CREATE PROCEDURE insert_role(max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO role ( role_name,orders ) VALUES (rand_string(8),rand_num(1,5000)); UNTIL i = max_num END REPEAT; COMMIT; END$$ -- 删除 -- DELIMITER ; -- drop PROCEDURE insert_role;

插入用户表

sql

-- 插入用户数据 DELIMITER $$ CREATE PROCEDURE insert_user(START INT, max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO user (username, role_id, password, salt ) VALUES (rand_string(8) ,rand_num(1,100000), rand_string(10), rand_string(10)); UNTIL i = max_num END REPEAT; COMMIT; END$$ -- 删除 -- DELIMITER ; -- drop PROCEDURE insert_user;

执行存储过程

sql

-- 执行存储过程，往dept表添加10万条数据 CALL insert_role(100000); -- 执行存储过程，往emp表添加100万条数据，编号从100000开始 CALL insert_user(100000,1100000);

总结

执行用时 10w数据差不多半分钟, 100w数据超过了20分钟, 同时 user的存储还卡死很久...

最后都成功新增, 但是自动递增值和行数不一致, 这个我也不知道因为啥...