【MYSQL】按月创建未来n年表，如TEST202005、TEST202006

 按月创建未来2年表 【表TEST202005、TEST202006、...TEST202204】

1、创建存储过程CREATE_TAB_BYMONTH【按年月循环创建表】

DELIMITER ;;
DROP PROCEDURE IF EXISTS CREATE_TAB_BYMONTH;
CREATE  PROCEDURE CREATE_TAB_BYMONTH()
BEGIN
    DECLARE MONTHNUM  INT;
    DECLARE CURNUM INT;
    DECLARE YYMM INT;
    DECLARE TABLENAME VARCHAR(20);
    SET MONTHNUM=24;
    SET CURNUM=0;
  　WHILE CURNUM<MONTHNUM DO
     SET YYMM= PERIOD_ADD(DATE_FORMAT(NOW(),'%Y%m'),CURNUM);
       SET TABLENAME=CONCAT('TEST',YYMM );
       IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=TABLENAME) THEN
         ##创建表
         CALL CREATE_TABLE(TABLENAME);
       END IF;
     SET CURNUM=CURNUM+1;
   END WHILE;
END;;
DELIMITER ;

2、创建存储过程CREATE_TABLE【新建表】

DELIMITER ;;
DROP PROCEDURE IF EXISTS CREATE_TABLE;
CREATE PROCEDURE CREATE_TABLE(TABLENAME VARCHAR(32))
BEGIN
DECLARE STR VARCHAR(4000);
SET STR=CONCAT('CREATE TABLE ',TABLENAME,
' (
ID            BIGINT(20) NOT NULL DEFAULT 0 ,
TASK_ID       BIGINT(20) NOT NULL DEFAULT 0 ,
NAME          VARCHAR(64) NOT NULL DEFAULT ''''
)ENGINE=INNODB DEFAULT CHARSET=UTF8;');
SET @SQL=STR;
PREPARE SL FROM @SQL;
EXECUTE SL;
DEALLOCATE PREPARE SL;
END;;

3、执行存储过程CREATE_TAB_BYMONTH【创建表TEST202005、TEST202006、...TEST202204】

CALL CREATE_TAB_BYMONTH();