詳解MySQL如何按表創(chuàng)建千萬級的壓測數(shù)據(jù)

作者：波波說運維 2019-11-19 07:56:30

有時我們要對系統(tǒng)做壓測，或者數(shù)據(jù)庫壓力測試，這時候需要對某些表插入幾百萬或者上千萬數(shù)據(jù)，下面介紹下怎么利用MySQL循環(huán)和存儲過程對特定表的創(chuàng)建千萬行數(shù)據(jù)。

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. 隨機生成數(shù)據(jù)

MySQL里面自帶一個隨機數(shù)生成的函數(shù)RAND()，它能生成0-1的浮點數(shù)

RAND函數(shù)生成隨機數(shù)：

詳解MySQL如何按表創(chuàng)建

3. 隨機生成給定數(shù)目的字符串

這里用mysql循環(huán)，循環(huán)采用WHILE循環(huán)，循環(huán)里面采用字符串拼接函數(shù)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 ii = i + 1; 
         
    END WHILE; 
RETURN return_str; 
END;

詳解MySQL如何按表創(chuàng)建

用戶名和密碼都可以通過以上的方式添加。

郵箱的添加方式:可直接在insert的value對應字段寫上concat(rand_string(5), '@qq.com')

4. 隨機生成DATE和DATETIME類型

(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的隨機數(shù)據(jù)  
    --L PAD ( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ) 表示月份的生成，小于 10自動補0  
    --L PAD ( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 ) ) 表示 3號到10號的隨機數(shù)據(jù) */ 
     
    SET aDate = CONCAT( 
        1949 + FLOOR( ( RAND( ) * 70 ) ), 
        '-', 
        LPAD( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ), 
        '-', 
        LPAD( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 )  
    ); 
RETURN aDate; 
END;

詳解MySQL如何按表創(chuàng)建

關于LPAD函數(shù)，具體做為月份和日期的補零作用!生效如下介紹：

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'

(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. 將數(shù)據(jù)循環(huán)插入

定義將數(shù)據(jù)循環(huán)的存儲過程：

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 numnum = num + 1; 
         
    END WHILE; 
    commit; 
END; 
//

調用存儲過程：

CALL insertUser;

責任編輯：趙寧寧來源：今日頭條

MySQL 壓測數(shù)據(jù)表