创建所有数据库 调用: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;