MySQL event事件，定时按年份动态创建表

参考资料：

1、MySQL事件（定时任务）：https://blog.51cto.com/u_15549234/5138457；
2、mysql创建存储过程语法（MySQL创建存储过程sql语句）：https://www.gaojipro.com/a/108616；
3、mysql计划任务每天定时执行：https://www.likecs.com/show-305863378.html；
4、MySQL存储过程使用动态表名：https://blog.51cto.com/u_15127617/4279455

思路

MySQL event事件可以通过定时任务执行指定的存储过程。需求是系统需要在新年到来之前创建新年的年份表，为了省去人工操作，计划使用数据库event事件实现该功能。
第一步：利用MySQL event事件创建一个定时任务；
第二步：写一个可以简化人工操作的存储过程（调试时可以写个简单的存储过程，可以看到定时任务生效即可；后续再根据实际业务调整存储过程需要执行的脚本内容）；
第三步：让event事件定时调用存储过程。

简单的存储过程

# 删除存储过程
DROP PROCEDURE IF EXISTS doSth;
# 创建存储过程
CREATE PROCEDURE doSth () BEGIN
    UPDATE test SET date = NOW();
END;

实现

# 删除存储过程
DROP PROCEDURE IF EXISTS create_tables;
# 创建存储过程
CREATE PROCEDURE create_tables () BEGIN
    SET @nianfen = YEAR(DATE_ADD(NOW(),INTERVAL 1 YEAR));
        
    # 入库单明细表
    SET @create_sql = CONCAT("CREATE TABLE xhp_t_in_stock_order_detail_",@nianfen,"(
        `id` int(11) NOT NULL COMMENT 'ID',
        `companyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `instkbillno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `materialno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `materialname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `unit` int(11) NULL DEFAULT NULL,
        `unitname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `specifications` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `manufactor` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `model` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `kuwei` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
        `num` double NULL DEFAULT NULL,
        `unitprice` double NULL DEFAULT NULL,
        `money` double NULL DEFAULT NULL,
        `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `podetailid` int(11) NULL DEFAULT NULL,
        `size` double NULL DEFAULT NULL,
        `isfree` tinyint(2) NULL DEFAULT NULL,
        `createtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `field1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field3` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field4` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field5` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;");
    PREPARE create_stmt FROM @create_sql;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
        
    # 入库单主表
    SET @create_sql = CONCAT("CREATE TABLE xhp_t_in_stock_order_main_",@nianfen,"(
        `id` int(11) NOT NULL COMMENT 'ID',
        `billdate` date NULL DEFAULT NULL,
        `billno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `businesstype` tinyint(2) NULL DEFAULT NULL COMMENT 'ҵ',
        `warehouse` int(11) NULL DEFAULT NULL,
        `storageman` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `totalnum` double NULL DEFAULT NULL,
        `totalmoney` double NULL DEFAULT NULL,
        `creator` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `checker` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `createtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `checktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `state` tinyint(2) NULL DEFAULT NULL COMMENT '״̬',
        `companyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `iswriteoff` tinyint(2) NULL DEFAULT NULL,
        `writeoffbillno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `createtype` tinyint(2) NULL DEFAULT NULL,
        `datasource` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ϵͳ',
        `taxrate` double NULL DEFAULT NULL,
        `currency` int(11) NULL DEFAULT NULL,
        `field1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field3` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field4` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `field5` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE,
        UNIQUE INDEX `instockorderindex`(`billno`) USING BTREE,
        INDEX `index_companyid`(`companyid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;");
    PREPARE create_stmt FROM @create_sql;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
    
END;
# 查看数据库所有存储过程
SHOW PROCEDURE STATUS WHERE db = 'jc_x1web';

# 执行存储过程
# CALL create_tables;

-- 开启 ON/关闭 OFF 事件调度器
SET GLOBAL event_scheduler = ON;

-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

#删除事件
DROP EVENT IF EXISTS init_tables;

-- 创建名为 init_tables 的事件，注意此处没有括号
CREATE EVENT init_tables
-- 创建周期定时的规则，每多长时间执行一次
#ON SCHEDULE EVERY 1 MINUTE STARTS CONCAT(YEAR(NOW()),'-01-03',' 16:43:00')
ON SCHEDULE EVERY 1 YEAR STARTS CONCAT(YEAR(DATE_ADD(NOW(),INTERVAL 0 YEAR)),'-12-30',' 23:59:59')
-- 创建后并不开始生效，后续手动生效
ON COMPLETION PRESERVE DISABLE
-- do call create_tables() 是该事件的操作内容，表示调用名为 create_tables() 的存储过程。
DO CALL create_tables();

#开启 ENABLE/关闭 DISABLE 事件
ALTER EVENT init_tables ON COMPLETION PRESERVE ENABLE;

#查看事件
SELECT event_schema 数据库,event_name,event_definition,interval_value,interval_field,status 存储过程是否可用,STARTS 事件生效开始时间 FROM information_schema.EVENTS;