使用MySQL存储过程批量生成或删除测试数据

1. 目标

有时需要在数据库中插入大量数据，用于测试应用在大数据量时的运行是否正常，可以使用MySQL存储过程，批量生成或删除测试数据。

2. 存储过程脚本

在以下脚本中，每插入/删除5000条数据提交一次。插入10万条数据耗时约3.7秒，删除耗时约1.2秒。

在执行批量插入/删除操作时，可以根据需要修改插入总记录数、每次事务插入的记录数、具体的插入操作、每次事务删除记录数、具体的删除操作等。

2.1. 批量生成测试数据

DROP PROCEDURE IF EXISTS insert_procedure;

# 将语句的结束符号从分号 ; 临时改为两个//
delimiter //

CREATE PROCEDURE insert_procedure ()
exit_label:
BEGIN
   -- 插入总记录数（根据需要修改）
   DECLARE total        int DEFAULT 100000;
   -- 每次事务插入的记录数（根据需要修改）
   DECLARE page         int DEFAULT 5000;
   -- 每次插入实际记录数
   DECLARE insert_num   int DEFAULT 0;
   -- 提交插入的总次数
   DECLARE num          int DEFAULT 0;
   -- 外层循环下标
   DECLARE n1           int DEFAULT 1;
   -- 内层循环下标
   DECLARE n2           int DEFAULT 1;
   -- 当前循环的起始数字
   DECLARE start        int DEFAULT 0;
   -- 每次插入的流水号字段
   DECLARE seq          VARCHAR(32);

   -- 除法只有整数部分，没有小数部分
   SET num = total / page;

   IF (num = 0)
   THEN
      -- 退出
      LEAVE exit_label;
   END IF;

   -- 判断模是否为0，非0时需要将总次数加1
   IF (total % page != 0)
   THEN
      SET num = num + 1;
   END IF;

   -- 外层循环
   WHILE n1 <= num
   DO
      -- 开始事务
      START TRANSACTION;

      IF (n1 < num)
      THEN
         -- 非最后一次插入
         SET insert_num = page;
      ELSE
         -- 最后一次插入
         SET insert_num = total - page * (num - 1);
      END IF;

      -- 内层循环
      SET start = page * (n1 - 1);

      WHILE n2 <= insert_num
      DO
         -- 执行插入（根据需要修改）
         SET seq = concat("testtime", unix_timestamp(now()), "num", start + n2);

         INSERT INTO test_table(id,flag,create_time,update_time)
         VALUES (seq,seq,now() - INTERVAL '5' DAY,now() - INTERVAL '5' DAY);

         SET n2 = n2 + 1;
      END WHILE;

      -- 提交事务
      COMMIT;

      -- 外层循环下标加1
      SET n1 = n1 + 1;
      -- 内层循环下标置1
      SET n2 = 1;
   END WHILE;
END
//
# 将语句的结束符号恢复为分号 ;
delimiter ;

# 执行存储过程
CALL insert_procedure;


SHOW PROCEDURE STATUS LIKE '%insert_procedure%';

SHOW PROCESSLIST;

2.2. 批量删除测试数据

DROP PROCEDURE IF EXISTS delete_procedure;

# 将语句的结束符号从分号 ; 临时改为两个//
delimiter //

CREATE PROCEDURE delete_procedure ()
exit_label:
BEGIN
   -- 每次事务删除记录数（根据需要修改）
   DECLARE page         int DEFAULT 5000;
   -- 记录每次删除返回记录数
   DECLARE delete_num   int DEFAULT 0;

   WHILE 1 = 1
   DO
      -- 开启事务
      START TRANSACTION;

      -- 执行删除操作（根据需要修改）
      DELETE FROM test_table
      WHERE id LIKE 'testtime%'
      LIMIT page;

      -- 获取被删除的行数，需要在提交事务之前执行
      SET delete_num = ROW_COUNT();

      -- 提交事务
      COMMIT;

      IF (delete_num = 0)
      THEN
         -- 退出
         LEAVE exit_label;
      END IF;
   END WHILE;
END
//
# 将语句的结束符号恢复为分号 ;
delimiter ;

# 执行存储过程
CALL delete_procedure;

3. 其他命令

3.1. 查看存储过程状态

执行“SHOW PROCEDURE STATUS”命令可以查看存储过程状态，命令如下所示：

SHOW PROCEDURE STATUS LIKE '%insert_procedure%';

输出信息示例：

Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
testdb	insert_procedure	PROCEDURE	test@localhost	2020/1/9 21:31:00	2020/1/9 21:31:00	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci

3.2. 查看当前执行的存储过程

执行“SHOW PROCESSLIST;”可以查看当前正在执行的存储过程，输出信息示例如下。

Id	User	Host	db	Command	Time	State	Info	Progress
218919458	test	localhost:64027	testdb	Query	0	call	my_procedure	0

3.3. 结束存储过程执行

当需要结束正在执行的存储过程时，可执行“KILL”命令加上述查询结果的Id值，如“KILL 218919458;”。