create procedure parttablecreatecfProc()
BEGIN
declare yearsuffix varchar(20);
declare quartersuffix varchar(20);
declare createParttableSql varchar(500);
declare existFlag int default 0;
declare lastTableYear int;
declare loop_quarter int;
declare loop_year int;
declare loop_year_str varchar(30);
declare pksql varchar(300); -- 主键sql;
declare index1sql varchar(300);-- 索引1的sql
declare index2sql varchar(300);-- 索引2的sql
set lastTableYear = cast(substring(year(now()),3,4) as unsigned int)+1;
select substring(year(min(fcreatetime)),3,4),QUARTER(min(fcreatetime))
into yearsuffix,quartersuffix
from t_bc_customerfollow;
set loop_quarter=cast(quartersuffix as unsigned int);
set loop_year = cast(yearsuffix as unsigned int);
loop_label:loop
if loop_year<=lastTableYear then
-- 如果不是最早的日期,则需要从第一季度开始循环表
if cast(yearsuffix as unsigned int) < loop_year then
set loop_quarter=1;
end if;
while loop_quarter<=4 do
if loop_year < 10 then
set loop_year_str=concat('0',loop_year);
else
set loop_year_str=loop_year;
end if;
select count(1) into existFlag from information_schema.`TABLES` s
where LOWER(s.TABLE_NAME) = LOWER(concat('T_BC_CUSTOMERFOLLOW',loop_year_str,'0',loop_quarter))
AND LOWER(s.TABLE_SCHEMA) =
LOWER(
@current_database); -- 一定要加上table_schema的过滤,不然的话如果再其他库存在的话,就创建不了
if existFlag = 0 then
set createParttableSql ='create table T_BC_CUSTOMERFOLLOW';
set createParttableSql=concat(createParttableSql,loop_year_str,'0',loop_quarter
,' as select * from t_bc_customerfollow where 1=2');
set pksql=concat('alter table T_BC_CUSTOMERFOLLOW',loop_year_str,'0',loop_quarter,' add constraint pk_customerfollow_',loop_year_str,'0',loop_quarter,' primary key (FID)');
set index1sql=concat('create index INDEX_CUTOMERID',loop_year_str,'0',loop_quarter,' on T_BC_CUSTOMERFOLLOW',loop_year_str,'0',loop_quarter,' (FKCUSTOMERID)');
set index2sql=concat('create index INDEX_PERSONID',loop_year_str,'0',loop_quarter,' on T_BC_CUSTOMERFOLLOW',loop_year_str,'0',loop_quarter,' (FKPERSONID)');
set @ex_sql = createParttableSql;
prepare stmt from @ex_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
set @ex_sql = pksql;
prepare stmt from @ex_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt;
set @ex_sql = index1sql;
prepare stmt from @ex_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt;
set @ex_sql = index2sql;
prepare stmt from @ex_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt;
deallocate prepare stmt; -- 释放掉预处理段
commit;
end if;
set loop_quarter=loop_quarter+1;
end while;
set loop_year=loop_year+1;
else
LEAVE loop_label;
end if;
end loop;
end