MySQL创建测试数据

构造测试数据

函数	说明
rand()	随机函数，会生成0~1之间的随机数
md5(input)	散列函数，根据输入值的不同，生成不同的32位字符串（但只有0_9，af共16种字符）
substring(string, position, length)	字符串截取函数，会截取字符串string从position位置开始共length个字符的子串
floor	向下取整
concat	字符串拼接
lpad(string,length,填充字符)	左侧填充字符至指定长度

SQL	格式	说明
floor(rand()*100)	整数	100内的随机整数
floor(rand()*(max-min)+min)	整数	指定范围内的随机数
round(rand() * 100, 2)	小数	小数点后两位小数
substring(md5(rand()),1,5)	字符串	随机生成长度为5的字符串
case when round(rand())=0 then 'male' else 'female' end	字符串	性别
`concat(floor(rand()(2025-2000)+2000),'-',lpad(floor(rand()(12-1)+1),2,'0'),'-',lpad(floor(rand()*(28-1)+1),2,'0'))`	日期	yyyy-mm-dd格式
`concat(lpad(floor(rand()(23-0)+0),2,'0'),':',lpad(floor(rand()(59-0)+0),2,'0'),':',lpad(floor(rand()*(59-0)+0),2,'0'))`	时间	hh:mm:ss格式

构造数据

普通表

普通表1

create table t_test(
id varchar(200), 
name varchar(200), 
age int, 
password varchar(200)
);

-- 插入600w的模拟数据
INSERT INTO t_test (id, name, age, password)
SELECT
    CONCAT('id', FLOOR(RAND() *10000)),  
    CONCAT('User', FLOOR(RAND() *10000)),           -- 生成随机的用户名
    FLOOR(RAND() *100) +18,                           -- 生成随机的年龄 (18-117)
    CONCAT('user', FLOOR(RAND() *10000), '@example.com') -- 生成随机的邮箱地址
    
FROM
    information_schema.tables a,
    information_schema.tables b
LIMIT 6000000;

普通表2

-- 创建表
CREATE TABLE `tbl_test` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `item_code` bigint NOT NULL COMMENT '子项编号',
  `order_code` varchar(100) NOT NULL COMMENT '订单编号',
  `id_card` varchar(30) NOT NULL COMMENT '身份证',
  `goods_number` bigint NOT NULL COMMENT '商品数量',
  `amount` decimal(6,2) NOT NULL COMMENT '金额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `random` bigint NOT NULL COMMENT '数据数',
  PRIMARY KEY (`id`),
  KEY `index_item_code` (`item_code`),
  KEY `index_id_card` (`id_card`),
  KEY `index_random` (`random`)
);


-- 在tbl_test表中，goods_number列上创建索引
CREATE INDEX index_goods_number ON tbl_test (goods_number);

insert into tbl_test (
    name,
	item_code,
	order_code,
	id_card,
	goods_number,
	amount,
	create_time,
	random)
select
   CONCAT("test", substring(md5(rand()),1,5)),
   floor(rand()*10000),
   CONCAT("order", substring(md5(rand()),1,5)),
   FLOOR(RAND() * 10000000000000),
   floor(rand()*10000),
   ROUND(RAND() * 100, 2),
   NOW(),
   FLOOR(RAND() * 1000000)
FROM
    information_schema.tables a,
    information_schema.tables b
LIMIT 6000000;

普通表3

CREATE TABLE  t1(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键', 
  person_id tinyint not null comment '用户id',
  person_name varchar(30) comment '用户名称',
  gmt_create datetime not null comment '创建时间', 
  gmt_modified datetime comment '修改时间'
) comment '测试表2048条';

insert into t1 values(1,1,'user_1', NOW(), now());

select (@i:=@i+1) as rownum, person_name from t1, (select @i:=100) as init;

#多次执行如下语句
insert into t1(id, person_id, person_name, gmt_create, gmt_modified) 
select @i:=@i+1,
  left(rand()*10,1) as person_id,
  concat('user_',@i%2048),
  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),
  date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)
from t1;


https://www.jianshu.com/p/cf5d381ef637/

(147条消息) MySQL快速生成100W条测试数据_我的麦的博客-CSDN博客_mysql生成测试数据

[MySQL学习笔记]1. MySQL测试数据的构造 - 码农教程 (manongjc.com)

MySQL快速生成大量测试数据（100万、1000万、1亿） - 简书 (jianshu.com)

分区表

范围分区

CREATE TABLE test_range (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

insert into test_range
select floor(rand()*10000),
       substring(md5(rand()),1,5),
       concat(floor(rand()*(2025-2000)+2000),'-',lpad(floor(rand()*(12-1)+1),2,'0'),'-',lpad(floor(rand()*(28-1)+1),2,'0')),
       '9999-12-31',
       floor(rand()*10),
       floor(rand()*30)
from information_schema.tables a,information_schema.tables b;

列表分区

CREATE TABLE test_list (
    id INT NOT NULL,
    name VARCHAR(30),
    sex VARCHAR(10),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY LIST COLUMNS (sex) (
    PARTITION pmale VALUES IN ('male'),
    PARTITION pfemale VALUES IN ('female')
);

insert into test_list
select floor(rand()*10000),
       substring(md5(rand()),1,5),
       case when round(rand())=0 then 'male' else 'female' end,
       concat(floor(rand()*(2025-2000)+2000),'-',lpad(floor(rand()*(12-1)+1),2,'0'),'-',lpad(floor(rand()*(28-1)+1),2,'0')),
       '9999-12-31',
       floor(rand()*10),
       floor(rand()*30)
from information_schema.tables a,information_schema.tables b;

哈希分区

CREATE TABLE test_hash (
   id INT NOT NULL,
   name VARCHAR(30),
   hired DATE NOT NULL DEFAULT '1970-01-01',
   separated DATE NOT NULL DEFAULT '9999-12-31',
   job_code INT,
   store_id INT
)
PARTITION BY HASH(store_id) PARTITIONS 4;

insert into test_hash
select floor(rand()*10000),
       substring(md5(rand()),1,5),
       concat(floor(rand()*(2025-2000)+2000),'-',lpad(floor(rand()*(12-1)+1),2,'0'),'-',lpad(floor(rand()*(28-1)+1),2,'0')),
       '9999-12-31',
       floor(rand()*10),
       floor(rand()*30)
from information_schema.tables a,information_schema.tables b;

范围哈希组合分区

CREATE TABLE test_range_hash (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired))
    SUBPARTITION BY HASH( TO_DAYS(hired) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN (2025),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

insert into test_range_hash
select floor(rand()*10000),
       substring(md5(rand()),1,5),
       concat(floor(rand()*(2025-2000)+2000),'-',lpad(floor(rand()*(12-1)+1),2,'0'),'-',lpad(floor(rand()*(28-1)+1),2,'0')),
       '9999-12-31',
       floor(rand()*10),
       floor(rand()*30)
from information_schema.tables a,information_schema.tables b;

posted @ 2025-05-17 10:09 kahnyao 阅读(47) 评论(0) 收藏举报

刷新页面返回顶部

knlbase

MySQL创建测试数据

构造测试数据

相关函数

构造数据

普通表

分区表