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

前言

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

存储方式+函数
Navicat的数据生成

表

准备了两张表角色表:

id: 自增长
role_name: 随机字符串, 不允许重复
orders: 1-1000任意数字

用户表:

id: 自增长
username: 随机字符串, 不允许重复
password: 随机字符串, 允许重复
role_id: 1-10w之间的任意数字

建表语句:

				?

									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是否允许创建函数

				?

									SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

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

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

				?

									SET GLOBAL log_bin_trust_function_creators=1;

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

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

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

产生随机字符串

				?

									-- 随机产生字符串

									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;

产生随机数字

				?

									-- 用于随机产生区间数字

									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;

创建存储过程

插入角色表

				?

									-- 插入角色数据

									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;

插入用户表

				?

									-- 插入用户数据

									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;

执行存储过程

				?

									-- 执行存储过程，往dept表添加10万条数据

									CALL insert_role(100000); 

									-- 执行存储过程，往emp表添加100万条数据，编号从100000开始

									CALL insert_user(100000,1100000);