MySQL如何實現(xiàn)快速插入大量測試數(shù)據(jù)

更新時間：2023年11月24日 08:51:16 作者：云滿筆記

這篇文章主要介紹了MySQL如何實現(xiàn)快速插入大量測試數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1.1. 簡述

開發(fā)過程中經(jīng)常需要測試 SQL 在大量數(shù)據(jù)集時候的執(zhí)行效率，這就需要我們在表中插入大量的測試數(shù)據(jù)，下面介紹如何使用存儲過程插入大量的測試數(shù)據(jù)

1.2. 定義常用方法

我們要確保生成的測試數(shù)據(jù)要有足夠的隨機性，測試結(jié)果才會更準(zhǔn)確，如果某個字段的測試數(shù)據(jù)都是一樣的，索引的效率會大大折扣，測試結(jié)果往往與真實數(shù)據(jù)的執(zhí)行結(jié)果大相徑庭

我們可以使用 MySQL 的自定義函數(shù)來實現(xiàn)隨機值的生成，下面羅列出幾種常見的字段的函數(shù)定義

1.2.1. 生成隨機時間

函數(shù)聲明:

CREATE DEFINER=`root`@`%` FUNCTION `genDate`(
    start_time VARCHAR(10),
    end_time VARCHAR(10)
) RETURNS VARCHAR(255) CHARSET utf8mb4
BEGIN
  DECLARE random_date DATETIME DEFAULT NULL;
  SET random_date = CONCAT(
    (DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(start_time) + FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) + 1))))), 
      ' ',  
            FLOOR(RAND() * 24),  ':', FLOOR(RAND() * 60), ':', FLOOR(RAND() * 60)
    );
  RETURN date_format(random_date,'%Y-%m-%d %H:%i:%s');
END

使用示例:

生成 2020-01-01 ～ 2023-01-01 時間段內(nèi)的隨機時間

> select genDate('2020-01-01','2023-01-01');
2020-06-22 4:25:35

1.2.2. 生成中文名

函數(shù)聲明:

CREATE DEFINER=`root`@`%` FUNCTION `genUsername`() RETURNS varchar(255) CHARSET utf8mb4
BEGIN
    DECLARE first_name_dict VARCHAR(2056) DEFAULT '趙錢孫李周鄭王馮陳楮衛(wèi)蔣沈韓楊朱秦尤許何呂施張孔曹嚴(yán)華金魏陶姜戚謝喻柏水竇章云蘇潘葛奚范彭郎魯韋昌馬苗鳳花方俞任袁柳酆鮑史唐費廉岑薛雷賀倪湯滕殷羅畢郝鄔安常樂于時傅皮齊康伍余元卜顧孟平黃和穆蕭尹姚邵湛汪祁毛禹狄米貝明臧計伏成戴談宋茅龐熊紀(jì)舒屈項祝董梁杜阮藍(lán)閩席季麻強賈路婁危江童顏郭梅盛林刁鍾徐丘駱高夏蔡田樊胡凌霍虞萬支柯昝管盧莫經(jīng)裘繆干解應(yīng)宗丁宣賁鄧郁單杭洪包諸左石崔吉鈕龔程嵇邢滑裴陸榮翁';
    DECLARE last_name_dict VARCHAR(2056) DEFAULT '嘉懿煜城懿軒燁偉苑博偉澤熠彤鴻煊博濤燁霖?zé)钊A煜祺智宸正豪昊然明杰誠立軒立輝峻熙弘文熠彤鴻煊燁霖哲瀚鑫鵬致遠(yuǎn)俊馳雨澤燁磊晟睿天佑文昊修潔黎昕遠(yuǎn)航旭堯鴻濤偉祺軒越澤浩宇瑾瑜皓軒擎蒼擎宇志澤睿淵楷瑞軒弘文哲瀚雨澤鑫磊夢琪憶之桃慕青問蘭爾嵐元香初夏沛菡傲珊曼文樂菱癡珊恨玉惜文香寒新柔語蓉海安夜蓉涵柏水桃醉藍(lán)春兒語琴從彤傲晴語蘭又菱碧彤元霜憐夢紫寒妙彤曼易南蓮紫翠雨寒易煙如萱若南尋真曉亦向珊慕靈以蕊尋雁映易雪柳孤嵐笑霜海云凝天沛珊寒云冰旋宛兒綠真盼兒曉霜碧凡夏菡曼香若煙半夢雅綠冰藍(lán)靈槐平安書翠翠風(fēng)香巧代云夢曼幼翠友巧聽寒夢柏醉易訪旋亦玉凌萱訪卉懷亦笑藍(lán)春翠靖柏夜蕾冰夏夢松書雪樂楓念薇靖雁尋春恨山從寒憶香覓波靜曼凡旋以亦念露芷蕾千蘭新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山懷蝶冰蘭山柏翠萱樂丹翠柔谷山之瑤冰露爾珍谷雪樂萱涵菡海蓮傲蕾青槐冬兒易夢惜雪宛海之柔夏青亦瑤妙菡春竹修杰偉誠建輝晉鵬天磊紹輝澤洋明軒健柏煊昊強偉宸博超君浩子騫明輝鵬濤炎彬鶴軒越彬風(fēng)華靖琪明誠高格光華國源宇晗昱涵潤翰飛翰海昊乾浩博和安弘博鴻朗華奧華燦嘉慕堅秉建明金鑫錦程瑾瑜鵬經(jīng)賦景同靖琪君昊俊明季同開濟凱安康成樂語力勤良哲理群茂彥敏博明達(dá)朋義彭澤鵬舉濮存溥心璞瑜浦澤奇邃祥榮軒';
    DECLARE first_name VARCHAR(3) DEFAULT substring(first_name_dict, floor(length(first_name_dict) / 3 * rand()), 1);
    DECLARE last_name VARCHAR(9);
    DECLARE full_name_length INT DEFAULT FLOOR(2+(RAND()*3))*3;
    DECLARE full_name VARCHAR(12) DEFAULT first_name;
    
  WHILE LENGTH(full_name) < full_name_length DO
        SET full_name = CONCAT(full_name, substring(last_name_dict, floor(length(last_name_dict) / 3 * rand()), 1));
    END WHILE;
    
    return full_name;
END

使用示例:

> select genUsername();
凌之澤

1.2.3. 字符串分割選取

函數(shù)聲明:

CREATE FUNCTION `splitStr` (
  str VARCHAR (1000),
  delimiter VARCHAR (5),
  str_order INT
) RETURNS VARCHAR (255) CHARSET utf8mb4 DETERMINISTIC
BEGIN
  DECLARE result VARCHAR (255) DEFAULT '';

  SET result = REVERSE(
    substring_index(
      REVERSE(
        substring_index(
          str,
          delimiter,
          str_order
        )
      ),
      delimiter,
      1
    )
  );
  RETURN result;
END

使用示例: 該函數(shù)用于將字符串按照指定的分割符進行分割，并返回分割后的第 n(n 由參數(shù)指定) 個字符串，如取字符串"I love MySQL"按空格分割后的第 2 個字符串

> select splitStr('I love MySQL',' ','2);
love

1.2.4. 生成隨機手機號

函數(shù)聲明:

CREATE DEFINER=`root`@`%` FUNCTION `genMobile`() RETURNS char(11) CHARSET utf8mb4 NOT DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(100) DEFAULT '132,133,139,183,186,187,130,131,189,151,156,157,176,134,135,137,138,136,000';
    DECLARE content CHAR(10) DEFAULT '0123456789';
    DECLARE phone CHAR(20) DEFAULT splitStr(head, ',', FLOOR(1 + RAND() * 19));
    DECLARE i int DEFAULT 1;
  
    WHILE i<9 DO
        SET i=i+1;
        SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * 10), 1));
    END WHILE;
    RETURN phone;
END

使用示例:

> select genMobile();
18975304923

1.3. 插入大量測試數(shù)據(jù)

如下面這張表，現(xiàn)在要插入 10w 的測試數(shù)據(jù)，我們可以定義一個 MySQL 存儲過程，通過存儲過程的方式插入數(shù)據(jù)到表中

表結(jié)構(gòu)

CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mobile` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

存儲過程定義

CREATE DEFINER=`root`@`%` PROCEDURE `t_user_batch_insert`(IN size INT)
BEGIN
  declare i int default 0;
    while i < size do
    insert into t_user(username,sex,mobile) values(genUsername(),floor(rand() * 2),genMobile());
        set i = i + 1;
    end while;
END

調(diào)用存儲過程