`
hudeyong926
  • 浏览: 2034781 次
  • 来自: 武汉
社区版块
存档分类
最新评论

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

 
阅读更多

创建所有数据库 调用：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);
参数说明：
1、要删除的数据库前缀
2、目标数据库后缀最小数 
3、目标数据库后缀最大数（数据库总数量）

4、目标数据库数字的位数

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);
参数说明：
1、目标数据库的前缀 
2、要创建到目标数据库中的表名称 
3、目标数据库后缀最小数 
4、目标数据库后缀最大数（数据库总数量）

5、目标数据库数字的位数

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);
参数说明：
1、目标数据库（在该数据库中建立很多分表） 
2、要创建到目标数据库中的表前缀 
3、目标数据库后缀最小数 
4、目标数据库后缀最大数（数据库总数量）

5、目标数据库数字的位数

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; 

参数说明：

1、目标数据库中的表前缀  
2、目标数据库后缀最大数（数据库总数量）

3、目标数据库数字的位数

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;

 

分享到：
| phpcms v9网站搬家更换域名的方法
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    如何连接2个数据库合并2张表

    通过以上内容可以看出，连接两个数据库并合并其中的两张表是一个涉及多个步骤的过程。不仅需要了解数据库的基本操作，还需要掌握一定的编程技能来实现数据的整合。希望本文能帮助读者更好地理解和实践这一过程。

    如何实现将Excel表(含多张数据库表)导入数据库(SQLServer).doc

    为了将数据导入SQL Server，你需要创建一个数据库表结构，其字段与Excel工作表中的列匹配。这可以通过SQL脚本或在SQL Server Management Studio中手动完成。然后，可以使用 `SqlBulkCopy` 类来高效地批量插入数据。`...

    sql server实现在多个数据库间快速查询某个表信息的方法

    在SQL Server中，实现跨多个数据库快速查询特定表信息的需求是一项常见的任务，特别是在拥有大量数据库的环境中。本文将介绍一种利用游标和系统存储过程的方法来实现这一目标。首先，我们要了解SQL Server中的系统...

    Oracle连接查询子查询以及存储过程触发器讲解

    PL/SQL包括声明变量、控制流程结构（如IF-THEN-ELSE、CASE、FOR循环）、异常处理以及存储过程和函数。这使得开发者可以编写复杂的业务逻辑，提高代码的可读性和可维护性。 **存储过程**是PL/SQL的一个重要组成部分...

    数据库。。。。。。。。。。。。。。。。

    在没有存储过程的情况下，这需要编写多个独立的SQL语句，如插入客户数据、更新销售人员的目标和更新销售公司目标。但如果使用存储过程，这些操作可以被整合为一次调用，提高效率并减少错误的可能性。 总的来说，...

    存储过程的作用

    2. **事务处理**：对于复杂的数据库操作（例如涉及多张表的更新、插入、查询和删除操作），可以通过存储过程将其封装起来，并与数据库的事务处理机制结合使用，确保数据操作的一致性和完整性。 3. **代码重用**：...

    存储过程编写经验和优化措施

    在数据库管理中，存储过程（Storage Procedure，简称SP）扮演着重要的角色，它们用于封装复杂的业务逻辑和数据库操作。在大型项目中，合理编写和优化存储过程至关重要，因为这不仅影响系统的可维护性，还直接影响...

    7-通向架构师的道路之基于数据库的权限系统的设计.pdf

    4. **多角色归属**：一个用户可以属于多个角色，实现权限的复用和组合。 5. **动态管理菜单**：系统菜单允许动态的添加、删除、修改和查看，以适应业务变化。 在满足这些需求的基础上，我们可以设计如下的数据库表...

    php+mysqli批量查询多张表数据的方法

    当需要从多个表中查询数据时，我们通常使用mysqli扩展（MySQL Improved extension），它提供面向对象和过程两种编程风格，以及对预处理语句、事务以及存储过程等的支持。在本知识点中，将详细介绍如何使用PHP结合...

    《SQL-Server数据库技术》试卷J卷答案.docx

    以上就是试卷J卷涉及的主要SQL Server知识点，涵盖了数据库管理、查询语言、表操作和存储过程等多个方面，这些都是理解和操作SQL Server数据库所必需的基本技能。通过这份试卷，考生可以检验自己对SQL Server数据库...

    基于Oracle的小型数据库应用系统设计实现样本.doc

    本实验旨在设计和实现一个基于Oracle的小型网络数据库应用系统，以人事资源管理系统为例，涵盖了系统需求分析、结构设计、编码实现及调试运行等多个环节，旨在提升对数据库设计和开发的理解与实践能力。 首先，实验...

    Mysql实验2.pdf

    在本实验中，我们将探索如何使用...通过这些操作，我们不仅能够建立一个企业销售系统的数据库模型，还能学会利用MySQL中的高级特性，如存储过程、事件、触发器和事务控制等，来完成更加复杂的数据库操作和管理任务。

    数据库知识的葵花宝典学习,

    数据库是信息时代的核心组成部分，它负责存储、管理和检索数据，使得数据的处理和分析变得高效且有序。而SQL（Structured Query Language），即结构化查询语言，是用于管理关系数据库的标准语言。下面将详细阐述SQL...

    kettle批量导出mysql建表语句

    - 若要批量处理多个数据库或表，可以使用Kettle的作业（Job）功能，将上述转换串联起来，形成一个完整的流程。 - 考虑到实际应用场景，可能还需要将这个作业配置到定时任务中，例如通过Cron表达式，以定期自动执行...

    数据库操作语句大全(sql)

    UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 ...

    数据库新PPT学习教案.pptx

    数据库是存储和管理数据的核心工具，SQL（Structured Query Language）是关系型数据库的标准语言，用于创建、查询、更新和管理数据库。本教程主要讲解了SQL中的一个重要概念——连接查询，这是处理多表间数据交互的...

    MySQL 入门到高级：基础篇 下篇-尚硅谷 2021年

    本文将基于"MySQL 入门到高级：基础篇 下篇——尚硅谷 2021年"的主题，深入探讨MySQL的基础知识，涵盖从安装配置到实际操作的多个方面。 一、MySQL安装与配置 MySQL的安装过程相对简单，可适用于多种操作系统，包括...

Magicbox
Global site tag (gtag.js) - Google Analytics