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

本文介绍了两种在MySQL中快速生成大量假数据的方法,包括使用在线工具FillDB和编写存储过程。FillDB适合小规模数据生成,而存储过程则能高效创建万级数据。通过存储过程中的循环插入和INSERT SELECT语句,可以快速复制并扩大已有数据量,实现大规模数据的生成。这种方法对于测试、演示环境的数据准备十分实用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

前言

不管是为了做生产数据脱敏,还是为了验证某个技术是否符合期望,亦或是做简单的集成测试,短时间内生成大批量测试数据是很有必要的,但就算是测试数据也要假的像样点嘛,数据不够假,你放到演示环境,怎么给客户吹牛逼,怎么招标呢?本文将介绍几个生成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次即可。而且这个语句执行得非常快,在几秒之内即可完成大规模数据复制。

### 如何在 MySQL 5.7 中快速生成测试数据 #### 使用存储过程批量插入数据 为了高效地创建大量测试数据,可以编写一个存储过程来自动化这个流程。下面是一个具体的例子: ```sql DROP PROCEDURE IF EXISTS gen_big_data; DELIMITER $$ SET AUTOCOMMIT = 0$$ CREATE PROCEDURE gen_big_data(IN min_num INTEGER, IN max_num INTEGER) BEGIN DECLARE n DECIMAL(10) DEFAULT min_num; dd: LOOP INSERT INTO t_big_table(id, code, username, password) VALUES (n, UUID(), CONCAT('user-', n), PASSWORD(n)); COMMIT; SET n = n + 1; IF n = max_num THEN LEAVE dd; END IF; END LOOP dd; END$$ DELIMITER ; ``` 这段SQL语句定义了一个名为`gen_big_data`的存储过程,它接受两个参数:最小编号和最大编号。该过程会循环执行INSERT操作直到达到指定的最大值,并且每次迭代都会提交事务以确保部分失败不影响已成功写入的数据[^5]。 #### 利用外部工具加速数据填充 除了内置的SQL方法外,还可以考虑使用专门设计用于生成大规模测试数据的应用程序或库。例如Python中的Faker库能够模拟各种真实世界的信息如姓名、地址等;而像DataFactory这样的在线服务则提供了图形界面让用户轻松定制所需的数据集模式。 对于希望进一步提高效率的情况,建议探索这些第三方解决方案并与MySQL相结合使用[^3]。 #### 调整配置提升性能 当准备向数据库中加载大批量记录之前,适当调整一些系统变量可以帮助加快整个过程的速度。比如设置更大的缓冲区大小或者禁用不必要的特性(如唯一性检查)。具体做法如下所示: ```bash # 修改my.cnf文件增加innodb_buffer_pool_size等参数 [mysqld] innodb_buffer_pool_size=4G bulk_insert_buffer_size=256M unique_checks=0 foreign_key_checks=0 ``` 完成上述更改之后重启MySQL服务使新的设定生效[^1]。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Alphathur

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值