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

淡淡的烟草味﹌ 2023-10-07 08:17 28阅读 0赞

## 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%';

输出信息示例：

<table> 
 <thead> 
  <tr> 
   <th>Db</th> 
   <th>Name</th> 
   <th>Type</th> 
   <th>Definer</th> 
   <th>Modified</th> 
   <th>Created</th> 
   <th>Security_type</th> 
   <th>Comment</th> 
   <th>character_set_client</th> 
   <th>collation_connection</th> 
   <th>Database Collation</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>testdb</td> 
   <td>insert_procedure</td> 
   <td>PROCEDURE</td> 
   <td>test@localhost</td> 
   <td>2020/1/9 21:31:00</td> 
   <td>2020/1/9 21:31:00</td> 
   <td>DEFINER</td> 
   <td></td> 
   <td>latin1</td> 
   <td>latin1_swedish_ci</td> 
   <td>latin1_swedish_ci</td> 
  </tr> 
 </tbody> 
</table>

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

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

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th>User</th> 
   <th>Host</th> 
   <th>db</th> 
   <th>Command</th> 
   <th>Time</th> 
   <th>State</th> 
   <th>Info</th> 
   <th>Progress</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>218919458</td> 
   <td>test</td> 
   <td>localhost:64027</td> 
   <td>testdb</td> 
   <td>Query</td> 
   <td>0</td> 
   <td>call</td> 
   <td>my_procedure</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

### 3.3. 结束存储过程执行 ###

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