mysql应用之通过存储过程方式批量插入数据

我们平时的测试过程中有一个环节就是准备测试数据，包括准备基础数据，准备业务数据，使用的场景包括压力测试，后台批量数据传输，前端大数据查询导出，或者分页打印等功能，准备测试数据我们通俗点讲就是造数据，根据不同的使用场景我们有不同的造数据的方式，比如需要大数据时我们可以用jmeter压测获取大数据，也可以导入生产数据，简单少许的测试数据我们直接在数据库中插入，本篇主要记录的是另外一种造数据的方式——通过编写存储过程来实现批量数据插入。

一、编写调用存储过程：

数据库类型：mysql 

需求：在user表中插入数据，user表主键为id，id方式为长度为32位的字母+数字随机字符串

1、创建存储过程

DELIMITER $$
USE `db_test`$$
DROP PROCEDURE IF EXISTS `proc_user`$$

CREATE DEFINER=`root`@`%` PROCEDURE `proc_user`()
BEGIN
   DECLARE user_name VARCHAR(60);
   DECLARE email VARCHAR(150);
   DECLARE rand_id VARCHAR(120);
   DECLARE id VARCHAR(120);
   DECLARE i INT DEFAULT 1000;
   DECLARE createtime DATETIME;
   DECLARE tel_body VARCHAR(40);
   DECLARE tel VARCHAR(60);
   -- 调试过程, 先插入5条
   WHILE i <= 1005 DO
     -- user_name = test + i
     SET user_name = CONCAT('test', i);
     SET email = CONCAT(user_name,'@qq.com');
     SET rand_id= SUBSTRING(MD5(RAND()),1,28);
     -- id = rand_id + i, +i的目的主要是为了区分测试数据与user_name对应
     SET id = CONCAT(rand_id, i);
     SET createtime = NOW();
     SET tel_body = FLOOR(RAND()*100000000);
     -- tel = 159开头随机号码
     SET tel = CONCAT('159', tel_body);

     INSERT INTO  `user`
     VALUES(id,
       user_name, 
      '202cb962ac59075b964b07152d234b70', 
      '0cc495f78776486294ebc7c08831aabe', 
      NULL,
      createtime,
      tel,
      email
      );
     SET i=i+1;
     END WHILE;
END$$

2、调用存储过程

CALL `proc_user`();

3、查看表数据

4、模拟批量插入5000条数据，

1）调用存储过程，全部数据插入完成耗时如下：

2）核对数据库插入数据总量

二、上述方法是将插入记录数作为一个固定值，直接写在存储过程中，其实还可以设置参数，调用存储过程时传递需要插入的记录行数，如下：

-- 传参数的方法
DELIMITER $$
USE `db_test`$$
DROP PROCEDURE IF EXISTS `proc_user`$$

CREATE DEFINER=`root`@`%` PROCEDURE `proc_user`(IN cn INT(4))
BEGIN
   DECLARE user_name VARCHAR(60);
   DECLARE email VARCHAR(150);
   DECLARE rand_id VARCHAR(120);
   DECLARE id VARCHAR(120);
   DECLARE i INT DEFAULT 1000;
   DECLARE createtime DATETIME;
   DECLARE tel_body VARCHAR(40);
   DECLARE tel VARCHAR(60);
   -- 判定条件，i<=cn则插入
   WHILE i <= cn DO
     SET user_name = CONCAT('test', i);
     SET email = CONCAT(user_name,'@qq.com');
     SET rand_id= SUBSTRING(MD5(RAND()),1,28);
     SET id = CONCAT(rand_id, i);
     SET createtime = NOW();
     SET tel_body = FLOOR(RAND()*100000000);
     SET tel = CONCAT('159', tel_body);

     INSERT INTO  `user`
     VALUES(id,
       user_name, 
      '202cb962ac59075b964b07152d234b70', 
      '0cc495f78776486294ebc7c08831aabe', 
      NULL,
      createtime,
      tel,
      email
      );
     SET i=i+1;
     END WHILE;
END$$

-- 传参数的方法CALL `proc_user`(cn); -- cn为任意大于1000小于9999的数值
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位的子字符串