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;
