mysql應用之通過存儲過程方式批量插入數據

我們平時的測試過程中有一個環節就是準備測試數據，包括準備基礎數據，準備業務數據，使用的場景包括壓力測試，後臺批量數據傳輸，前端大數據查詢導出，或者分頁列印等功能，準備測試數據我們通俗點講就是造數據，根據不同的使用場景我們有不同的造數據的方式，比如需要大數據時我們可以用jmeter壓測獲取大數據，也可以導入生產數據，簡單少許的測試數據我們直接在資料庫中插入，本篇主要記錄的是另外一種造數據的方式——通過編寫存儲過程來實現批量數據插入。

一、編寫調用存儲過程：

資料庫類型：mysql 

需求：在user表中插入數據，user表主鍵為id，id方式為長度為32位的字母+數字隨機字元串

1、創建存儲過程

 1 DELIMITER $$
 2 USE `db_test`$$
 3 DROP PROCEDURE IF EXISTS `proc_user`$$
 4 
 5 CREATE DEFINER=`root`@`%` PROCEDURE `proc_user`()
 6 BEGIN
 7    DECLARE user_name VARCHAR(60);
 8    DECLARE email VARCHAR(150);
 9    DECLARE rand_id VARCHAR(120);
10    DECLARE id VARCHAR(120);
11    DECLARE i INT DEFAULT 1000;
12    DECLARE createtime DATETIME;
13    DECLARE tel_body VARCHAR(40);
14    DECLARE tel VARCHAR(60);
15    -- 調試過程, 先插入5條
16    WHILE i <= 1005 DO
17      -- user_name = test + i
18      SET user_name = CONCAT('test', i);
19      SET email = CONCAT(user_name,'@qq.com');
20      SET rand_id= SUBSTRING(MD5(RAND()),1,28);
21      -- id = rand_id + i, +i的目的主要是為了區分測試數據與user_name對應
22      SET id = CONCAT(rand_id, i);
23      SET createtime = NOW();
24      SET tel_body = FLOOR(RAND()*100000000);
25      -- tel = 159開頭隨機號碼
26      SET tel = CONCAT('159', tel_body);
27 
28      INSERT INTO  `user`
29      VALUES(id,
30        user_name, 
31       '202cb962ac59075b964b07152d234b70', 
32       '0cc495f78776486294ebc7c08831aabe', 
33       NULL,
34       createtime,
35       tel,
36       email
37       );
38      SET i=i+1;
39      END WHILE;
40 END$$

2、調用存儲過程

1 CALL `proc_user`();

3、查看表數據

4、模擬批量插入5000條數據，

1）調用存儲過程，全部數據插入完成耗時如下：

2）核對資料庫插入數據總量

二、上述方法是將插入記錄數作為一個固定值，直接寫在存儲過程中，其實還可以設置參數，調用存儲過程時傳遞需要插入的記錄行數，如下：

 1 -- 傳參數的方法
 2 DELIMITER $$
 3 USE `db_test`$$
 4 DROP PROCEDURE IF EXISTS `proc_user`$$
 5 
 6 CREATE DEFINER=`root`@`%` PROCEDURE `proc_user`(IN cn INT(4))
 7 BEGIN
 8    DECLARE user_name VARCHAR(60);
 9    DECLARE email VARCHAR(150);
10    DECLARE rand_id VARCHAR(120);
11    DECLARE id VARCHAR(120);
12    DECLARE i INT DEFAULT 1000;
13    DECLARE createtime DATETIME;
14    DECLARE tel_body VARCHAR(40);
15    DECLARE tel VARCHAR(60);
16    -- 判定條件，i<=cn則插入
17    WHILE i <= cn DO
18      SET user_name = CONCAT('test', i);
19      SET email = CONCAT(user_name,'@qq.com');
20      SET rand_id= SUBSTRING(MD5(RAND()),1,28);
21      SET id = CONCAT(rand_id, i);
22      SET createtime = NOW();
23      SET tel_body = FLOOR(RAND()*100000000);
24      SET tel = CONCAT('159', tel_body);
25 
26      INSERT INTO  `user`
27      VALUES(id,
28        user_name, 
29       '202cb962ac59075b964b07152d234b70', 
30       '0cc495f78776486294ebc7c08831aabe', 
31       NULL,
32       createtime,
33       tel,
34       email
35       );
36      SET i=i+1;
37      END WHILE;
38 END$$
39 
40 -- 傳參數的方法CALL `proc_user`(cn); -- cn為任意大於1000小於9999的數值
41 CALL `proc_user`(1100);

三、函數簡要解釋： 

1、CONCAT()函數：

     拼接函數，將多個字元串拼接成一個字元串，語法為：CONCAT(str1,str2,…)

2、RAND()函數，FLOOR()函數：

     RAND()，0-1之間的隨機數，帶小數點

     FLOOR()，取整；

     FLOOR(RAND() *10)，獲得0-10之間的整數（包含0，不包含10）；FLOOR(RAND() *100000000)，獲取10000000-99999999之間的隨機整數

3、SUBSTRING()函數：

     截取子字元串函數，從特定位置開始的字元串返回一個給定長度的子字元串

     語法：SUBSTRING(字元串，位置，長度)，如SUBSTRING(MD5(RAND()),1,28)，從MD5隨機字元串的第1位開始截取長度為28位的子字元串