分库分表创建sql

于 2018-03-05 18:27:31 发布
阅读量1.5k 收藏 1
点赞数
分类专栏： MySQL 文章标签： 分库分表 存储过程 多表 创建多表 修改多表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chen1280436393/article/details/79449182
版权 
use DBName;
-- existsSQL 检查当前表是否存在
-- createTableSQL 创建表的sql
-- set @dbCount=5;--数据库数量
-- set @recordStep=100000000;--自增id初始值
-- set @tbCountPerDb=100;--每个库中表的数量
-- set @tbNamePrefix='tbFile';-- 创建tbale的名字
-- set @existsSQL='drop table if exists ${tbFullName};';
-- set @createTableSQL='tableSQL;ALTER TABLE ${tbFullName}  AUTO_INCREMENT ${curRcdNo}';
DROP PROCEDURE IF EXISTS pro_alterTable;
create PROCEDURE pro_alterTable(dbCount int,tbCountPerDb int,tbNamePrefix char(100),alterTableSql text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					set @tmpAlterTableSql=REPLACE(alterTableSql,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpAlterTableSql;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;	
	
DROP PROCEDURE IF EXISTS pro_createTable;
create PROCEDURE pro_createTable(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),existsSQL text,createTableSql text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpExistsSQL=REPLACE(existsSQL,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpExistsSQL;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
	
					-- 修改自增长初始值
					set @tmpCreateTableSQL=REPLACE(createTableSQL,'${tbFullName}',@tbFullName);
						
					SET @tmpSQL=@tmpCreateTableSQL;
					
					PREPARE createTableSTMT from @tmpSQL;
					EXECUTE createTableSTMT ;
					deallocate PREPARE createTableSTMT;				
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;

DROP PROCEDURE IF EXISTS pro_createTable_byStart;
CREATE PROCEDURE pro_createTable_byStart(dbCount int,dbStart int,tbCountPerDb int,tbNamePrefix char(100),existsSQL text,createTableSql text)
begin
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=dbStart;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbNum= Convert(i*100+j, CHAR(1000));
					set @tbName=CONCAT(tbNamePrefix, @tbNum);
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpExistsSQL=REPLACE(existsSQL,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpExistsSQL;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
	
					-- 修改自增长初始值
					set @tmpCreateTableSQL1=REPLACE(createTableSQL,'${tbFullName}',@tbFullName);
					SET @tmpCreateTableSQL=REPLACE(@tmpCreateTableSQL1,'${tb_auto}',@tbNum*100000000+1);
						
					SET @tmpSQL=@tmpCreateTableSQL;
					
					PREPARE createTableSTMT from @tmpSQL;
					EXECUTE createTableSTMT ;
					deallocate PREPARE createTableSTMT;				
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;
-- --------------------------------------
-- 初始化表 自增长初始值

-- set @dbCount=5;
-- set @recordStep=100000000;
-- set @tbCountPerDb=100;
-- set @tbNamePrefix='tbFile';-- 创建tbale的名字
-- set @autoIncrementSQL='ALTER TABLE ${tbFullName}  AUTO_INCREMENT = ${curRcdNo};';

DROP PROCEDURE IF EXISTS  pro_initTableAutoIncrement;
create PROCEDURE pro_initTableAutoIncrement(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),autoIncrementSQL text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					-- 自增初始值
					SET @curRcdNo = 1000 ;
					IF ( i + j = 0 ) THEN
						SET @curRcdNo = 1000 ;
					ELSE 
						SET  @curRcdNo = ( i * 100.0 + j ) * recordStep ;
					END IF;	
					
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpAutoIncSQL=REPLACE(autoIncrementSQL,'${tbFullName}',@tbFullName);
					set @tmpAutoIncSQL=REPLACE(@tmpAutoIncSQL,'${curRcdNo}',@curRcdNo);
					set @tmpSQL = @tmpAutoIncSQL;
					
					PREPARE autoIncreSTMT from @tmpSQL;
					execute autoIncreSTMT;  
					deallocate prepare autoIncreSTMT; 
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;



-- ---创建索引
-- set @dbCount=5;
-- set @recordStep=100000000;
-- set @tbCountPerDb=100;
-- set @tbNamePrefix='tbFile';-- 创建tbale的名字
-- set @indexSQL='alter table ${tbFullName} add index indexName${indexNum}_0 (${indexColName})';
-- set @indexColName='colName';
DROP PROCEDURE IF EXISTS pro_createTableIndex;
create PROCEDURE pro_createTableIndex(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),indexSQL text,indexColName char(100))
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
				 	set @tmpIndexSQL=REPLACE(indexSQL,'${tbFullName}',@tbFullName);
				
					SET @indexFlag=POSITION('${indexColName}' in @tmpIndexSQL);
					IF(@indexFlag>0)THEN
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${tbFullName}',@tbFullName);
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${indexNum}',CONVERT(j,char(1000)));
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${indexColName}',indexColName);
					END IF;		
					
					set @tmpSQL = @tmpIndexSQL;
					PREPARE indexSTMT from @tmpSQL;
					execute indexSTMT;  
					deallocate prepare indexSTMT;		
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;



-- --删除表
DROP PROCEDURE IF EXISTS pro_dropTable;
create PROCEDURE pro_dropTable(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),existsSQL text)
begin
DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpExistsSQL=REPLACE(existsSQL,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpExistsSQL;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
	
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;

end;

-- ----------Report库------------------------------------------------
-- existsSQL 检查当前表是否存在
-- createTableSQL 创建表的sql
DROP PROCEDURE IF EXISTS pro_createTable_User;
create PROCEDURE pro_createTable_User(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),existsSQL text,createTableSql text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpExistsSQL=REPLACE(existsSQL,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpExistsSQL;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
	
					-- 修改自增长初始值
					set @tmpCreateTableSQL=REPLACE(createTableSQL,'${tbFullName}',@tbFullName);
						
					SET @tmpSQL=@tmpCreateTableSQL;
					
					PREPARE createTableSTMT from @tmpSQL;
					EXECUTE createTableSTMT ;
					deallocate PREPARE createTableSTMT;				
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;

-- 初始化表 自增长初始值
DROP PROCEDURE IF EXISTS pro_initTableAutoIncrement_User;
create PROCEDURE pro_initTableAutoIncrement_User(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),autoIncrementSQL text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					-- 自增初始值
					SET @curRcdNo = 1000 ;
					IF ( i + j = 0 ) THEN
						SET @curRcdNo = 1000 ;
					ELSE 
						SET  @curRcdNo = ( i * 100.0 + j ) * recordStep ;
					END IF;	
					
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpAutoIncSQL=REPLACE(autoIncrementSQL,'${tbFullName}',@tbFullName);
					set @tmpAutoIncSQL=REPLACE(@tmpAutoIncSQL,'${curRcdNo}',@curRcdNo);
					set @tmpSQL = @tmpAutoIncSQL;
					
					PREPARE autoIncreSTMT from @tmpSQL;
					execute autoIncreSTMT;  
					deallocate prepare autoIncreSTMT; 
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;

-- ---创建索引
DROP PROCEDURE IF EXISTS pro_createTableIndex_User;
create PROCEDURE pro_createTableIndex_User(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),indexSQL text,indexColName char(100))
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
				 	set @tmpIndexSQL=REPLACE(indexSQL,'${tbFullName}',@tbFullName);
				
					SET @indexFlag=POSITION('${indexColName}' in @tmpIndexSQL);
					IF(@indexFlag>0)THEN
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${tbFullName}',@tbFullName);
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${indexNum}',CONVERT(j,char(1000)));
						set @tmpIndexSQL=REPLACE(@tmpIndexSQL,'${indexColName}',indexColName);
					END IF;		
					
					set @tmpSQL = @tmpIndexSQL;
					PREPARE indexSTMT from @tmpSQL;
					execute indexSTMT;  
					deallocate prepare indexSTMT;		
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;

-- --删除表
DROP PROCEDURE IF EXISTS pro_dropTable_User;
create PROCEDURE pro_dropTable_User(dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),existsSQL text)
begin
DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpExistsSQL=REPLACE(existsSQL,'${tbFullName}',@tbFullName);
					set @tmpSQL = @tmpExistsSQL;
					
					PREPARE existsSTMT from @tmpSQL;
					execute existsSTMT;  
					deallocate prepare existsSTMT; 
	
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;

-- -----------------------
-- 调用示例
-- set @dbNamePrefix="Report";
-- set @dbCount=5;
-- set @recordStep=100000000;
-- set @tbCountPerDb=100;
-- set @tbNamePrefix="tbFile";
-- set @autoIncreSQL="alter table ${tbFullName} MODIFY COLUMN ${colName} ${colType} not null";
-- #set @autoIncreSQL="alter table ${tbFullName} MODIFY COLUMN ${colName} ${colType} not null ;"; --关闭语句
-- #set @autoIncreSQL="alter table ${tbFullName} MODIFY COLUMN ${colName} ${colType} not null AUTO_INCREMENT ;"; --开启语句
-- set @colName="lId";
-- set @colType="BIGINT"; 
-- call pro_setAutoIncrement(@dbNamePrefix,@dbCount,@recordStep,@tbCountPerDb,@tbNamePrefix,@autoIncreSQL,@colName,@colType);

DROP PROCEDURE IF EXISTS pro_setAutoIncrement;
create PROCEDURE pro_setAutoIncrement(dbNamePrefix char(100),dbCount int,recordStep int,tbCountPerDb int,tbNamePrefix char(100),autoIncreSQL text,colName char(100),colType char(100))
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT(dbNamePrefix,CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
					
					-- 建表时删除之前存在的表
					set @tmpAutoIncreSQL=REPLACE(autoIncreSQL,'${tbFullName}',@tbFullName);
					set @tmpAutoIncreSQL=REPLACE(@tmpAutoIncreSQL,'${colName}',@colName);
					set @tmpAutoIncreSQL=REPLACE(@tmpAutoIncreSQL,'${colType}',@colType);
					set @tmpSQL = @tmpAutoIncreSQL;
					
					PREPARE autoIncreSTMT from @tmpSQL;
					execute autoIncreSTMT;  
					deallocate prepare autoIncreSTMT; 
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;


-- --------------------------------------------------------------------------------------------------------------------
-- 循环表执行语句存储过程
DROP PROCEDURE IF EXISTS pro_cycleTable;
create PROCEDURE pro_cycleTable(dbCount int,tbCountPerDb int,tbNamePrefix char(100),cycleTableSQL text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
	
	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT('DBName',CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = CONCAT(@sql_useDb,@tbName);
	
					-- 执行语句
					set @tmpCreateTableSQL=REPLACE(cycleTableSQL,'${tbFullName}',@tbFullName);
						
					SET @tmpSQL=@tmpCreateTableSQL;
					
					PREPARE createTableSTMT from @tmpSQL;
					EXECUTE createTableSTMT ;
					deallocate PREPARE createTableSTMT;				
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;
-- pro_initSequence
-- @autoIncrementSQL="call createseq(${tbFullName},${curRcdNo},1)";
DROP PROCEDURE IF EXISTS  pro_initSequence;
create PROCEDURE pro_initSequence(dbCount int,recordStep int,tbCountPerDb int,dbNamePrefix char(100),tbNamePrefix char(100),autoIncrementSQL text)
begin
	-- 声明参数
 
  DECLARE j int ;
  DECLARE i int ;
  DECLARE getMaxIdSQL VARCHAR(2000) ;
	DECLARE id BIGINT ;

	set j=0;
	set i=0;

	-- -----------------
	WHILE(i<dbCount) DO
	
				set @tmpSQL='';
				SET @sql_useDb = CONCAT(dbNamePrefix,CONVERT(i,CHAR(10)),'.') ;
				
				WHILE j < tbCountPerDb DO
					-- 自增初始值
					set @tbName=CONCAT(tbNamePrefix, Convert(i*100+j, CHAR(1000)));
					set @tbFullName = @tbName;
					set @tbFullNames = CONCAT(@sql_useDb,@tbName);
					set getMaxIdSQL = CONCAT(	'select IFNULL(max(lId),0) into @id from ',@tbFullNames);
					set @tmpSQL = getMaxIdSQL;
					
					PREPARE autoIncreSTMT from @tmpSQL;
					execute autoIncreSTMT;
				  deallocate prepare autoIncreSTMT ; 
					set id = @id;
				  SET @curRcdNo = 0 ;
					
					IF (id!= 0) THEN
						SET  @curRcdNo= @id;
					ELSEIF ( i + j = 0 ) THEN
						SET @curRcdNo = 0 ;
					ELSE 
						SET  @curRcdNo = ( i * 100.0 + j ) * recordStep ;
					END IF;	
					SET  @curRcdNo= @curRcdNo+1;
					
					-- 建表时删除之前存在的表
					set @tmpAutoIncSQL=REPLACE(autoIncrementSQL,'${dbName}',@sql_useDb);
					set @tmpAutoIncSQL=REPLACE(@tmpAutoIncSQL,'${tbFullName}',@tbFullName);
					set @tmpAutoIncSQL=REPLACE(@tmpAutoIncSQL,'${curRcdNo}',@curRcdNo);
					set @tmpSQL = @tmpAutoIncSQL;
					
					-- select @tmpSQL;
					PREPARE autoIncreSTMT from @tmpSQL;
					execute autoIncreSTMT;  
					deallocate prepare autoIncreSTMT; 
					
					SET j = j+1 ;
					
				END WHILE;
				
			SET i = i+1;
			SET j = 0;
	END WHILE;
end;


DROP PROCEDURE IF EXISTS AddSiteStat;
create PROCEDURE AddSiteStat (timeId bigint,domain char(30),url char(255),statItem char(30))
BEGIN
	START TRANSACTION;
			
			INSERT INTO tbSiteStat (lTimeId,strDomain,strURL,strStatItem,lCount) VALUES (timeId,domain,url,statItem,1) ON DUPLICATE KEY UPDATE lCount=lCount+1;
  
	COMMIT;
END;


依然核桃
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值