分库分表创建sql

xiaoxiao2021-02-28  3

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;
转载请注明原文地址: https://www.6miu.com/read-1600337.html

最新回复(0)