MySQL如何快速的创建千万级测试数据

最新推荐文章于 2023-06-25 15:48:32 发布

codedot

最新推荐文章于 2023-06-25 15:48:32 发布

阅读量236

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/m0_53474063/article/details/111939207

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

在进行查询操作的性能测试或者sql优化时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。

这里采用函数、存储过程和非InnoDB引擎（MyISAM、MEMORY）来实现。

一、各种函数定义

随机手机号

DELIMITER //
CREATE FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(100) DEFAULT '000,156,136,176,183'; 
    DECLARE content char(10) DEFAULT '0123456789'; 
    DECLARE phone char(11) DEFAULT substring(head, 1+(floor(1 + (rand() * 3))*4), 3); 
    DECLARE i INT DEFAULT 1; 
    DECLARE len INT DEFAULT length(content); 
    WHILE i<9 DO
        SET i=i+1;
        SET phone = concat(phone, substring(content, floor(1 + rand() * len), 1));
    END WHILE;
    RETURN phone;
END//
DELIMITER ;

-- 查看
SELECT generatePhone();

随机中文姓名

-- 创建随机生成姓名函数 rand_name
DELIMITER $$

CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(16)
BEGIN
    -- 初始化一个16姓氏字符串，作为姓氏字符库
    DECLARE family_str VARCHAR (128) DEFAULT '赵钱孙李周吴郑王冯陈蒋沈韩杨朱秦';
    -- 初始化一个32名字字符串，作为名字字符库
    DECLARE name_str VARCHAR (128) DEFAULT '平书文若山向秋凡白斌绮烟从蕾天曼润又亦从语绮彤之玉凡梅依琴沛槐敏';
    -- 记录当前是第几个
    DECLARE i INT DEFAULT 0;
    -- 记录生成结果
    DECLARE full_name VARCHAR(16) DEFAULT '';
    -- 随机名字1、2位标记
    DECLARE rand_num INT DEFAULT 0;

    WHILE i < n DO
	    -- 若获取多个姓名，则用逗号','区分
	    SET full_name = if(i > 0, concat(full_name, ','), full_name);
	    -- 随机取姓氏
	    SET full_name = concat(full_name, substr(family_str, floor(1+rand()*16), 1));
	    -- 随机取名字
	    SET full_name = concat(full_name, substr(name_str, floor(1+rand()*16), 1));
	    -- 名字是否为双字
	    SET rand_num = rand()*10;
	    SET full_name = if(rand_num > 5, concat(full_name, substr(name_str, floor(1+rand()*16), 1)), full_name);
	    SET i = i + 1;
    END WHILE;

    RETURN full_name;

END$$ 
DELIMITER ; 

-- 查看
SELECT rand_name(1);

随机字符串

DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , floor(1 + rand()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

-- 生成长度为20的字符串
SELECT rand_string(20);

随机日期

DROP FUNCTION IF EXISTS func_RandomDateTime; 

DELIMITER $$
CREATE
 FUNCTION func_RandomDateTime(
    sd DATETIME,
    ed DATETIME)
 RETURNS DATETIME
 
BEGIN
 
 DECLARE sub INT DEFAULT 0;
 DECLARE ret DATETIME;
 SET sub = abs(unix_timestamp(ed)-unix_timestamp(sd));
 SET ret = date_add(sd,INTERVAL floor(1+rand()*(sub-1)) SECOND);
 
 RETURN date_add(sd,INTERVAL floor(1+rand()*((abs(unix_timestamp(ed)-unix_timestamp(sd)))-1)) SECOND);
 
END $$
DELIMITER ;

查看：

SELECT func_RandomDateTime(date_format('2010-12-1 10:10:10','%Y-%m-%d %H:%i:%s'),date_format('2010-12-1 11:10:13','%Y-%m-%d %H:%i:%s'));

随机邮箱

DROP FUNCTION IF EXISTS generate_email; 

DELIMITER $$
CREATE FUNCTION `generate_email`(emailType VARCHAR(36)) RETURNS char(100) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE head VARCHAR (100) DEFAULT '000,182,150,136,152,158,183';
  DECLARE content char(10) DEFAULT '0123456789';
  DECLARE phone char(11) DEFAULT substring(head, 1+ (floor(1 + (rand() * 3)) * 4), 3);#定义手机号变量且长度为11
  DECLARE email char(100); #定义邮箱变量,且长度充足
  DECLARE i INT DEFAULT 1;
  DECLARE len INT DEFAULT length(content);
  WHILE
    i < 9 DO SET i = i + 1;
    SET phone = concat(phone, substring(content, floor(1 + rand() * len), 1));
  END WHILE;
	SET email = concat(phone,emailType); -- 随机手机号与邮箱后缀拼接（如：入参 emailType = @163.com ）
  RETURN email;
END $$
DELIMITER ;

查看：

-- 生成指定类型的邮箱
SELECT generate_email('163.com');

随机数字

DROP FUNCTION IF EXISTS random_num; 

DELIMITER $$
CREATE FUNCTION `random_num`( ) RETURNS INT(5)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = floor(100+rand()*10);  
RETURN i;  
 END$$
DELIMITER ;

-- 查看
SELECT random_num();

DELIMITER $$
CREATE FUNCTION `random_string_phone`(n INT) RETURNS VARCHAR(255) CHARSET latin1
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT '1234567890';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =concat(return_str,substring(chars_str,floor(1+rand()*10),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END$$
DELIMITER ;

-- 生成随机数字，返回varchar类型数据组合，例如手机号
SELECT random_string_phone(2);

-- 产生0到1000间的随机数(有小数)
SELECT rand() * 10000;

-- 产生整数
SELECT floor(rand() * 10000);

-- 产生500-1000间的整数
SELECT floor(rand() * 500) + 500;

-- 使用md5()产生32位随机字符串
SELECT md5(rand() * 10000);

二、创建内存表

DROP TABLE sys_user_myisam;
CREATE TABLE `sys_user_myisam` (
  `user_id` BIGINT(100) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) DEFAULT NULL COMMENT '用户名',
  `password` VARCHAR(100) DEFAULT NULL COMMENT '密码',
  `salt` VARCHAR(100) DEFAULT NULL COMMENT '盐',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  `mobile` VARCHAR(100) DEFAULT NULL COMMENT '手机号',
  `status` INT(1) DEFAULT '1' COMMENT '状态  0：禁用   1：正常',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='测试用户表';

三、插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE `insert_sys_user_myisam`(IN START INT(10),IN max_num INT(10))
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO sys_user_myisam (user_id,username,PASSWORD,salt,email,mobile,STATUS) VALUES (START+i,random_string(10),random_string(6),random_string(10),random_string(20),random_string_phone(12),1);  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$
DELIMITER ;

-- 调用存储过程：第一个参数是起始的id, 第二个参数是生成的总记录数
CALL insert_sys_user_myisam(1000001, 1);