按天分表按周分表

按天

drop procedure IF EXISTS news_user_receive_daytable;
CREATE procedure receive_daytable()
BEGIN
DECLARE i int;
SET i=0;
SET @dateStr = now();
SET @table1 = '创建表结构语句';
WHILE i < 600 DO
SET @sqlstr1 = CONCAT('CREATE table IF NOT EXISTS receive',date_format(DATE_SUB(@dateStr,INTERVAL -i DAY),'%Y%m%d'),@table1); 

prepare sqlstr1 from @sqlstr1;
execute sqlstr1;

SET i = i + 1;
END WHILE;
END;
call receive_daytable();
drop procedure IF EXISTSreceive_daytable;

按周

drop procedure IF EXISTS receive_daytable;
CREATE procedure receive_daytable()
BEGIN
DECLARE i int;
SET i= 1;
SET @dateStr = now();
SET @table1 = '';
WHILE i < 54 DO
SET @sqlstr1 = CONCAT('CREATE table IF NOT EXISTS receive',date_format(@dateStr,'%Y'),i,@table1); 

prepare sqlstr1 from @sqlstr1;
execute sqlstr1;

SET i = i + 1;
END WHILE;
END;
call receive_daytable();
drop procedure IF EXISTS receive_daytable;

修改字段属性

drop procedure IF EXISTS receive_daytable;
CREATE procedure receive_daytable()
BEGIN
DECLARE i int;
SET i = 8;
SET @dateStr = now();
SET @table1 = 'MODIFY COLUMN content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT \'内容\' AFTER title;';
WHILE i < 54 DO
SET @sqlstr1 = CONCAT('ALTER TABLE receive',date_format(@dateStr,'%Y'),i,@table1); 

prepare sqlstr1 from @sqlstr1;
execute sqlstr1;

SET i = i + 1;
END WHILE;
END;
call receive_daytable();
drop procedure IF EXISTS receive_daytable;