MySQL如何生成大批量测试数据

前言

不管是为了做生产数据脱敏，还是为了验证某个技术是否符合期望，亦或是做简单的集成测试，短时间内生成大批量测试数据是很有必要的，但就算是测试数据也要假的像样点嘛，数据不够假，你放到演示环境，怎么给客户吹牛逼，怎么招标呢？本文将介绍几个生成MySQL假数据的方案，看完你肯定会有收获的。

在线工具

使用在线工具可以快速产生假数据，这里介绍一个网站 Dummy Data for MYSQL Database：http://filldb.info/。纯免费，使用起来很简单，点点鼠标就能很快生成你需要的假数据了，但是不能产生百万级的数量。经测试，造百万级数据会响应504，但是造万级数据还是没问题的，但是如果需要百万级数据量，那大家还是不要试了，网站搞挂了就不好了。

存储过程

通过MySQL的存储过程，可以有效的造一批假数据。当前数据表schema定义如下：

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

存储过程如下。注意，数据库版本为8.0以上，存储过程定义之前必须加入DELIMITER //，存储过程定义结束必须加入 // DELIMITER ; 这个是为了有效的解决存储过程中‘,’引起的语法问题。

drop procedure if exists authors_func;

DELIMITER //
CREATE PROCEDURE authors_func ( ) 
BEGIN
			DECLARE i INT;
			SET i = 1;
			SET @MIN = '2020-01-01 00:00:01';
			SET @MAX = '2030-12-31 23:59:59';
			WHILE
					i <= 10000 DO
					INSERT INTO `demo`.`authors` ( `first_name`, `last_name`, `email`, `birthdate`, `added` )
					VALUES
						(
							(
							SELECT
								concat(
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand( ) * 26+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 26+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 26+1, 1 ) 
								) 
							),
							(
							SELECT
								concat(
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand( ) * 26+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 26+1, 1 ),
									substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 26+1, 1 ) 
								) 
							),
							(
							SELECT
								concat(
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 62+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 62+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 72+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 72+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 72+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 72+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 72+1, 1 ),
									substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', rand( ) * 62+1, 1 ),
									'@example.net' 
								) 
							),
							( SELECT NOW( ) - INTERVAL FLOOR( RAND( ) * 14 ) DAY ),
							(
							SELECT
								TIMESTAMPADD( SECOND, FLOOR( RAND( ) * TIMESTAMPDIFF( SECOND, @MIN, @MAX ) ), @MIN ) 
							) 
						);
				
				SET i = i + 1;
		
			END WHILE;
		END;//
DELIMITER ;

关于存储过程中两段关键的语句的解释：

substring( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand( ) * 26+1, 1 ),
substring( 'abcdefghijklmnopqrstuvwxyz', rand( ) * 52+1, 1 )

A~Z有26个大写字母，所以第一段语句一定生成一个随机的大写字母。

a~z有26个小写字母，那为什么这里可以写成rand( ) * 52 呢？长度扩大一倍，就有50%的概率产生空字符串，所以扩大长度是为了几率性的产生空字符串，然后就能通过concat拼接出变长的字符串了啊！

这个存储过程的主体是10000次循环插入，为啥不扩大到100万呢，因为随机函数实在是太消耗性能了。如果追求速度，那么请避免使用随机函数，但是这样造出来的数据就不够假！

通过上面的随机函数我们可以有效产生1万条基础假数据，距离100万的量还远着呢！不急，下面将介绍大杀器！

使用 insert select 语句

insert authors(first_name, last_name, email, birthdate, added) select first_name, last_name, email, birthdate, added from authors

我们执行这条insert select 语句，将实现对本表当前数据的复制，每次执行，数量都是2倍成长，所以如果达到100w的数据量，只需要将1w的基础数据按照如上的方法执行7次即可。而且这个语句执行得非常快，在几秒之内即可完成大规模数据复制。