概述

有時我們要對系統做壓測，或者數據庫壓力測試，這時候需要對某些表插入幾百萬或者上千萬數據，下面介紹下怎麽利用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 BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'hwbtestABCDEFG23141HIJ6712dfghjkqwevbnqmwerqwerqwreqw214123e1214c12dssaf89';
	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( ) * 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 BEGIN
	DECLARE
		aDate 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 BEGIN
	DECLARE
		aDateTime 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 ( ) 
BEGIN
	DECLARE
		num INT;
	
	SET num = 1;
	WHILE
			num < 100000 DO
			INSERT 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方面的內容，感興趣的朋友可以關注下~