mysql 动态按季度创建表

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