概述
有时我们要对系统做压测,或者数据库压力测试,这时候需要对某些表插入几百万或者上千万数据,下面介绍下怎么利用MySQL循环和存储过程对特定表的创建千万行数据。
1、准备测试表
CREATE TABLE `username` (`uid` INT( 11 ) UNSIGNED NOT NULL default 1235678901,`username` VARCHAR( 20 ) ,`email` VARCHAR( 30 ) ,`password` VARCHAR( 32 ) ,`birthday` date,`gender` VARCHAR(10) ,avatar MEDIUMBLOB,PRIMARY KEY ( `uid` )) ENGINE = INNODB DEFAULT CHARSET=utf8;
2、随机生成数据
MySQL里面自带一个随机数生成的函数RAND(),它能生成0-1的浮点数
RAND函数生成随机数:
3、随机生成给定数目的字符串
这里用mysql循环,循环采用WHILE循环,循环里面采用字符串拼接函数CONCAT
SET GLOBAL log_bin_trust_function_creators = 1; CREATE DEFINER = `root` @`%` FUNCTION `rand_string` ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8 BEGINDECLAREchars_str VARCHAR ( 100 ) DEFAULT 'hwbtestABCDEFG23141HIJ6712dfghjkqwevbnqmwerqwerqwreqw214123e1214c12dssaf89';DECLAREreturn_str VARCHAR ( 255 ) DEFAULT '';DECLAREi INT DEFAULT 0;WHILEi < n DOSET return_str = concat( return_str, substring( chars_str, FLOOR( 1 + RAND( ) * 80 ), 1 ) );SET i = i + 1;END WHILE;RETURN return_str;END;
用户名和密码都可以通过以上的方式添加
邮箱的添加方式:可直接在insert的value对应字段写上concat(rand_string(5), '@qq.com')
4、随机生成DATE和DATETIME类型
4.1、DATE类型
CREATE DEFINER = `root` @`%` FUNCTION `randDate` ( ) RETURNS VARCHAR ( 255 ) CHARSET utf8 BEGINDECLAREaDate CHAR ( 10 ) DEFAULT '';/*CONCAT (1949 + FLOOR( ( RAND( ) * 70 ) ) 表示 1949到2019的随机数据 --L PAD ( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ) 表示月份的生成,小于 10自动补0 --L PAD ( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 ) ) 表示 3号到10号的随机数据 */SET aDate = CONCAT(1949 + FLOOR( ( RAND( ) * 70 ) ),'-',LPAD( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ),'-',LPAD( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 ) );RETURN aDate;END;
关于LPAD函数,具体做为月份和日期的补零作用!生效如下介绍
Returns the string str:str, left-padded with the string padstr to a length of len characters. If str is longer than len:N, the return value is shortened to len characters.mysql> SELECT LPAD('hi',4,'??');-> '??hi'mysql> SELECT LPAD('hi',1,'??');-> 'h'
4.2、DATETIME类型
在刚刚写好的randDate上进行改造:
CREATE DEFINER = `root` @`%` FUNCTION `randDateTime` ( ) RETURNS VARCHAR ( 255 ) CHARSET utf8 BEGINDECLAREaDateTime CHAR ( 19 ) DEFAULT '';SET aDateTime = CONCAT(CONCAT(1949 + FLOOR( ( RAND( ) * 70 ) ),'-',LPAD( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ),'-',LPAD( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 ) ),' ',CONCAT(LPAD( FLOOR( 0 + ( RAND( ) * 23 ) ), 2, 0 ),':',LPAD( FLOOR( 0 + ( RAND( ) * 60 ) ), 2, 0 ),':',LPAD( FLOOR( 0 + ( RAND( ) * 60 ) ), 2, 0 ) ) );RETURN aDateTime;END;
5、将数据循环插入
定义将数据循环的存储过程:
DELIMITER //CREATE PROCEDURE insertUser ( ) BEGINDECLAREnum INT;SET num = 1;WHILEnum < 100000 DOINSERT INTO username ( uid,username, email, PASSWORD, birthday, gender, avatar )VALUES( num,rand_string ( 15 ),concat( rand_string ( 5 ), '@qq.com' ),rand_string ( 32 ),randDate ( ),'男',NULL );SET num = num + 1;END WHILE;commit;END;//
调用存储过程:
CALL insertUser;
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~