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 '排序权重rn',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

二、运用函数生成

经过存储进程快速刺进, 经过函数保证数据不重复

设置答应创立函数

查看 MySQL是否答应创立函数

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

成果如图所示, 咱们运用以下指令将创立函数功用打开（global-一切session都生效）

SET GLOBAL log_bin_trust_function_creators=1; 

这个时分再一次查询就会显现已打开

发生随机字符串

-- 随机发生字符串
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); 

小结

履行用时 10w数据差不多半分钟, 100w数据超过了20分钟, 一起 user的存储还卡死好久…
最后都成功新增, 但是自动递增值和行数不一致, 这个我也不知道因为啥…

数据展示

• role表

• user表

五、运用 Navicat自带的数据生成

接下来咱们运用 Navicat的数据生成

直接下一步, 然后挑选对应的两张表生成行数和对应的生成规则, 基于之前的履行速度, 这次 role生成 1w数据, user生成 10w数据
对于字符串类型的字段, 咱们能够设置他的随机数据生成器, 根据需求进行挑选

例如人物称号, 挑选了 职位称号 还能够进行是否包含 null 的挑选等

但是如果是 名字 那么就会让你挑选是否唯一

数字的话会让你挑选范围, 默认值等

等确定好了, 咱们就能够点击右下角进行生成随机测试数据

经过成果能够看到生成十一万测试数据总共用时十一秒， 比第一种方法速度快许多， 推荐运用