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

Dear 丶 2022-10-25 05:51 308阅读 0赞

# 前言 #

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

# 在线工具 #

使用在线工具可以快速产生假数据，这里介绍一个网站 Dummy Data for MYSQL Database：[http://filldb.info/][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次即可。而且这个语句执行得非常快，在几秒之内即可完成大规模数据复制。

[http_filldb.info]: http://filldb.info/