存储过程循环创建多张表和多个数据库

最新推荐文章于 2023-10-25 09:35:22 发布

原创最新推荐文章于 2023-10-25 09:35:22 发布 · 801 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

MYSQL 专栏收录该内容

62 篇文章

订阅专栏

本文介绍了一套完整的数据库和表批量操作方案，包括创建和删除多个数据库及其内部表的方法。通过存储过程实现了数据库及表的批量创建与删除，并提供了分表创建与删除的功能。

创建所有数据库调用：call create_databases('znke_z_log_', 0, 128);
参数说明：
1、要创建的数据库前缀
2、目标数据库后缀最小数
3、目标数据库后缀最大数（数据库总数量）

CREATE PROCEDURE `create_databases`(IN db_pre VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
     DECLARE i INT;
     DECLARE db_name VARCHAR(20);
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET db_name='';
     SET sql_text='';
     WHILE i<max_num DO
      SET db_name=CONCAT(db_pre, LPAD(i, LENGTH(max_num), '0'));
    
      SET sql_text=CONCAT('CREATE DATABASE ', db_name, '' );   
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
END;

删除所有数据库调用：call delete_databases('znke_z_log_', 0, 60,3);
参数说明：

要删除的数据库前缀
目标数据库后缀最小数
目标数据库后缀最大数（数据库总数量）
目标数据库数字的位数

CREATE PROCEDURE `delete_databases`(IN db_pre VARCHAR(20),IN min_num INT,IN max_num INT, IN size INT)
BEGIN
     DECLARE i INT;
     DECLARE db_name VARCHAR(20); 
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET db_name='';
     SET sql_text='';
     WHILE i<max_num DO
      SET db_name=CONCAT(db_pre, LPAD(i, size, '0'));
     
      SET sql_text=CONCAT('DROP DATABASE ', db_name, '' );
       
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
END;

创建所有数据库中的表.调用：CALL create_tables_of_databases('znke_z_log_', 'log' ,'znke_z_log.log' ,0 , 128);
参数说明：
1、目标数据库的前缀
2、要创建到目标数据库中的表名称
3、源数据库名称.源表名称(原理从已创建的库中复制，所以先要在源数据库中创建好设计好的表结构)
4、目标数据库后缀最小数
5、目标数据库后缀最大数（数据库总数量）

CREATE PROCEDURE `create_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN source_db_and_tableName VARCHAR(30),IN min_num INT,IN max_num INT)
BEGIN
     DECLARE i INT;
     DECLARE db_name VARCHAR(20);
    
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET db_name='';
    
     SET sql_text='';
     WHILE i<max_num DO
      SET db_name=CONCAT(db_pre, LPAD(i, LENGTH(max_num), '0'));
      
      SET sql_text=CONCAT('CREATE TABLE ', db_name , '.' ,table_name ,' LIKE ' ,source_db_and_tableName); 
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
   
END;

删除所有数据库中的表调用：CALL delete_tables_of_databases('znke_z_log_', 'log' ,0 , 128, 3);
参数说明：

目标数据库的前缀
要创建到目标数据库中的表名称
目标数据库后缀最小数
目标数据库后缀最大数（数据库总数量）
目标数据库数字的位数

CREATE PROCEDURE `delete_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN min_num INT,IN max_num INT, IN size INT)
BEGIN
     DECLARE i INT;
     DECLARE db_name VARCHAR(20);
    
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET db_name='';
    
     SET sql_text='';
     WHILE i<max_num DO
      SET db_name=CONCAT(db_pre, LPAD(i, size, '0'));
       
      SET sql_text=CONCAT('DROP TABLE ', db_name , '.' ,table_name); 
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
   
END;

创建数据库中所有分表（单库多表分片模式）调用：call create_tables('test', 'user_', 0 ,128);
参数说明：
1、目标数据库（在该数据库中建立很多分表）
2、要创建到目标数据库中的表前缀
3、目标数据库后缀最小数
4、目标数据库后缀最大数（数据库总数量）

CREATE PROCEDURE `create_tables`(IN db_name VARCHAR(20),IN table_pre VARCHAR(20), IN min_num INT,IN max_num INT)
BEGIN
     DECLARE i INT;
     DECLARE table_name VARCHAR(20);
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET table_name='';
     SET sql_text='';
     WHILE i<max_num DO
      SET table_name=CONCAT(table_pre, LPAD(i, LENGTH(max_num), '0'));
     
      SET sql_text=CONCAT('CREATE TABLE ',db_name ,'.',table_name, '(
          id VARCHAR(32) NOT NULL,
          user_id VARCHAR(32),
          PRIMARY KEY (id)
        ) ENGINE=INNODB DEFAULT CHARSET=utf8' );
       
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
END;

删除数据库中所有分表（单库多表分片模式）调用：call delete_tables('test', 'user_', 0 ,128, 3);
参数说明：

目标数据库（在该数据库中建立很多分表）
要创建到目标数据库中的表前缀
目标数据库后缀最小数
目标数据库后缀最大数（数据库总数量）
目标数据库数字的位数

CREATE PROCEDURE `delete_tables`(IN db_name VARCHAR(20),IN table_pre VARCHAR(20), IN min_num INT,IN max_num INT, IN size INT)
BEGIN
     DECLARE i INT;
     DECLARE table_name VARCHAR(20);
     DECLARE sql_text VARCHAR(2000);
     SET i=min_num;
     SET table_name='';
     SET sql_text='';
     WHILE i<max_num DO
      SET table_name=CONCAT(table_pre, LPAD(i, size, '0'));
     
      SET sql_text=CONCAT('DROP TABLE ', db_name , '.' ,table_name);       
      SELECT sql_text;
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 
      SET i=i+1;
    END WHILE;
END;

删除数据库中指定前缀的表调用：call Drop_Db_Table('test', 'user_');

参数说明：

1、目标数据库
2、目标数据库中的表前缀

CREATE PROCEDURE Drop_Db_Table(
  DB_NAME varchar(50),   # 数据库名称
  TB_PREFIX varchar(150) # 表前缀
)
BEGIN
  DECLARE done INT DEFAULT 0; #游标的标志位
  DECLARE a varchar(260);
  DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME AND table_name LIKE CONCAT(TB_PREFIX, '%');
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
  OPEN cur1;
    REPEAT
    FETCH cur1 INTO a;
      IF NOT done THEN
        SET @E=concat('DROP TABLE ',DB_NAME,'.',a); # 拼删除命令
        PREPARE stmt1 FROM @E;
        EXECUTE stmt1; # 执行命令
        DEALLOCATE PREPARE stmt1; #释放对象
      END IF;
    UNTIL done END REPEAT;
  CLOSE cur1;
END;

调用：call merage_branch_table('user', 100, 3); select @m_merage_sql;

参数说明：

目标数据库中的表前缀
目标数据库后缀最大数（数据库总数量）
目标数据库数字的位数

DROP PROCEDURE IF EXISTS `merage_branch_table`;
CREATE PROCEDURE `merage_branch_table`(
    IN     p_table_name   VARCHAR(200),    
    IN     p_branch_size  INT,
    in     p_lpad         INT
)
BEGIN
    DECLARE m_begin_row INT DEFAULT 0;
    SET @m_merage_sql = '';
    WHILE m_begin_row<p_branch_size DO
        IF(m_begin_row=p_branch_size-1)
            THEN SET @m_merage_sql = CONCAT(@m_merage_sql, 'SELECT * FROM ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0));
            ELSE SET @m_merage_sql = CONCAT(@m_merage_sql, 'SELECT * FROM ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0),' UNION ALL ');
        END IF;

        SET m_begin_row = m_begin_row+1;
    END WHILE;

    PREPARE main_stmt FROM @m_merage_sql;  
    EXECUTE main_stmt;             
END;