MySQL构造测试数据的几种方法
导语
平常压测，学习过程中需要构造测试数据，简单列举2种方法。
步骤
使用存储过程
1.1 建库和建表
# 建库
create database test;
# 建表
create table test.t1 (id int auto_increment, name varchar(64),primary key(`id`));
1.2 编写存储过程
delimiter $$
create procedure add_data(in num int)
begin
declare str char(60) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare nums char(20) default '123456789';
declare _name char(16);
declare i int default 1;
while i<num
do
set _name=concat(substring(str,1+floor(rand()*51),2),i);
set i=i+1;
insert into test.t1(name) values (_name);
end while;
end $$
delimiter ;
1.3 调用存储过程
call add_data(100);
使用mysql_random_data_load
2.1 下载
# 下载
wget https://github.com/Percona-Lab/mysql_random_data_load/releases/download/v0.1.12/mysql_random_data_load_0.1.12_Linux_x86_64.tar.gz
# 解压
tar xvpf mysql_random_data_load_0.1.12_Linux_x86_64.tar.gz
2.2 建表
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE `test`.`t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tcol01` tinyint(4) DEFAULT NULL,
`tcol02` smallint(6) DEFAULT NULL,
`tcol03` mediumint(9) DEFAULT NULL,
`tcol04` int(11) DEFAULT NULL,
`tcol05` bigint(20) DEFAULT NULL,
`tcol06` float DEFAULT NULL,
`tcol07` double DEFAULT NULL,
`tcol08` decimal(10,2) DEFAULT NULL,
`tcol09` date DEFAULT NULL,
`tcol10` datetime DEFAULT NULL,
`tcol11` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tcol12` time DEFAULT NULL,
`tcol13` year(4) DEFAULT NULL,
`tcol14` varchar(100) DEFAULT NULL,
`tcol15` char(2) DEFAULT NULL,
`tcol16` blob,
`tcol17` text,
`tcol18` mediumtext,
`tcol19` mediumblob,
`tcol20` longblob,
`tcol21` longtext,
`tcol22` mediumtext,
`tcol23` varchar(3) DEFAULT NULL,
`tcol24` varbinary(10) DEFAULT NULL,
`tcol25` enum('a','b','c') DEFAULT NULL,
`tcol26` set('red','green','blue') DEFAULT NULL,
`tcol27` float(5,3) DEFAULT NULL,
`tcol28` double(4,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
2.3 构造数据
[root@mgr2 opt]# ./mysql_random_data_load test t3 1000 --user=root --password=GreatSQL --config-file=/data/GreatSQL/my.cnf
INFO[2022-04-11T18:05:28+08:00] Starting
0s [====================================================================] 100%
INFO[2022-04-11T18:05:31+08:00] 1000 rows inserted