MySQL 如何快速插入大量测试数据

简述

开发过程中经常需要测试 SQL 在大量数据集时候的执行效率，这就需要我们在表中插入大量的测试数据，下面介绍如何使用存储过程插入大量的测试数据

定义常用方法

我们要确保生成的测试数据要有足够的随机性，测试结果才会更准确，如果某个字段的测试数据都是一样的，索引的效率会大大折扣，测试结果往往与真实数据的执行结果大相径庭

我们可以使用 MySQL 的自定义函数来实现随机值的生成，下面罗列出几种常见的字段的函数定义

生成随机时间

函数声明：

CREATE DEFINER=`root`@`%` FUNCTION `genDate`(
		start_time VARCHAR(10),
    end_time VARCHAR(10)
) RETURNS VARCHAR(255) CHARSET utf8mb4
BEGIN
	DECLARE random_date DATETIME DEFAULT NULL;
	SET random_date = CONCAT(
		(DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(start_time) + FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) + 1))))), 
			' ',	
            FLOOR(RAND() * 24),	':', FLOOR(RAND() * 60), ':', FLOOR(RAND() * 60)
		);
	RETURN date_format(random_date,'%Y-%m-%d %H:%i:%s');
END

使用示例：
生成 2020-01-01 ～ 2023-01-01 时间段内的随机时间

> select genDate('2020-01-01','2023-01-01');
2020-06-22 4:25:35

生成中文名

函数声明：

CREATE DEFINER=`root`@`%` FUNCTION `genUsername`() RETURNS varchar(255) CHARSET utf8mb4
BEGIN
    DECLARE first_name_dict VARCHAR(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
    DECLARE last_name_dict VARCHAR(2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
    DECLARE first_name VARCHAR(3) DEFAULT substring(first_name_dict, floor(length(first_name_dict) / 3 * rand()), 1);
    DECLARE last_name VARCHAR(9);
    DECLARE full_name_length INT DEFAULT FLOOR(2+(RAND()*3))*3;
    DECLARE full_name VARCHAR(12) DEFAULT first_name;
    
	WHILE LENGTH(full_name) < full_name_length DO
        SET full_name = CONCAT(full_name, substring(last_name_dict, floor(length(last_name_dict) / 3 * rand()), 1));
    END WHILE;
    
    return full_name;
END

使用示例：

> select genUsername();
凌之泽

字符串分割选取

函数声明：

CREATE FUNCTION `splitStr` (
	str VARCHAR (1000),
	delimiter VARCHAR (5),
	str_order INT
) RETURNS VARCHAR (255) CHARSET utf8mb4 DETERMINISTIC
BEGIN
	DECLARE result VARCHAR (255) DEFAULT '';

	SET result = REVERSE(
		substring_index(
			REVERSE(
				substring_index(
					str,
					delimiter,
					str_order
				)
			),
			delimiter,
			1
		)
	);
	RETURN result;
END

使用示例：
该函数用于将字符串按照指定的分割符进行分割，并返回分割后的第 n（n 由参数指定） 个字符串，如取字符串”I love MySQL“按空格分割后的第 2 个字符串

> select splitStr('I love MySQL',' ','2);
love

生成随机手机号

函数声明：

CREATE DEFINER=`root`@`%` FUNCTION `genMobile`() RETURNS char(11) CHARSET utf8mb4 NOT DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(100) DEFAULT '132,133,139,183,186,187,130,131,189,151,156,157,176,134,135,137,138,136,000';
    DECLARE content CHAR(10) DEFAULT '0123456789';
    DECLARE phone CHAR(20) DEFAULT splitStr(head, ',', FLOOR(1 + RAND() * 19));
    DECLARE i int DEFAULT 1;
	
    WHILE i<9 DO
        SET i=i+1;
        SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * 10), 1));
    END WHILE;
    RETURN phone;
END

使用示例：

> select genMobile();
18975304923

插入大量测试数据

如下面这张表，现在要插入 10w 的测试数据，我们可以定义一个 MySQL 存储过程，通过存储过程的方式插入数据到表中

表结构

CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mobile` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

存储过程定义

CREATE DEFINER=`root`@`%` PROCEDURE `t_user_batch_insert`(IN size INT)
BEGIN
	declare i int default 0;
    while i < size do
		insert into t_user(username,sex,mobile) values(genUsername(),floor(rand() * 2),genMobile());
        set i = i + 1;
    end while;
END

调用存储过程

> call t_user_batch_insert(100000);

在我这边，插入 10w 条数据，只要 52s

延伸

除了使用存储过程的方法插入数据外，还可以通过代码的方式插入数据，但是该方法的执行效率不高，但是改方法的执行效率不高。另外，如果你有 navicat 的话，也可以试试 navicat 的数据生成方案，由于我没有 navicat，就不介绍了，感兴趣的可以看 navicat 的文档

关注公众号：huangxy，一起学习，一起进步