【数据库功能测试】之存储过程

参考：https://www.iteye.com/blog/825635381-2161290

 

场景1.创建1万个table，每个table种插入1条记录

DELIMITER $$

CREATE DATABASE `lr_test` $$
USE `lr_test` $$
 
DROP PROCEDURE IF EXISTS `cyc`  $$
 
CREATE PROCEDURE `cyc`(
)
BEGIN
    DECLARE i INT;
    DECLARE MAX INT DEFAULT 10000;
    DECLARE table_name VARCHAR(20);
 
    SET i = 0;
  
    WHILE i<MAX DO
        SET table_name = CONCAT('e_',i);

        SET @csql = CONCAT('CREATE TABLE ', table_name, '(ID int) ENGINE=Innodb default charset=utf8;');
        PREPARE create_stmt FROM @csql;
        EXECUTE create_stmt;

        SET @insertsql = CONCAT('INSERT INTO ', table_name, ' VALUES(', 1, ') ');
        PREPARE insert_stmt FROM @insertsql;
        EXECUTE insert_stmt;

        SET i = i + 1;
        END WHILE;
END $$
  
DELIMITER ;
 
call cyc;

 

 

 场景2.在1张table种插入1万条记录

DELIMITER $$

#CREATE DATABASE `lr_test` $$
#USE `lr_test` $$
 
DROP PROCEDURE IF EXISTS `cyc`  $$
 
CREATE PROCEDURE `cyc`(
)
BEGIN

    DECLARE i INT DEFAULT 0;
    DECLARE MAX INT DEFAULT 10000;
 
  
    WHILE i < MAX DO

        SET @insertsql = CONCAT('INSERT INTO lr_test.a_1  VALUES(', i, ') ');
        PREPARE insert_stmt FROM @insertsql;
        EXECUTE insert_stmt;

        SET i = i + 1;
    END WHILE;
END $$
  
DELIMITER ;
 
call cyc;