如何使用MySQL事件调度器更新当前财年状态并新增下一财年记录

前置操作 #

首先开启MySQL事件调度器（默认是关闭状态）：

SET GLOBAL event_scheduler = ON;
-- 验证是否开启成功，返回值为ON说明生效
SHOW VARIABLES LIKE 'event_scheduler';

注意：如果重启MySQL后事件调度器恢复关闭，需要在my.cnf（Linux）或my.ini（Windows）配置文件中添加event_scheduler = ON永久生效。

2021财年切换专属事件代码 #

-- 先删除已存在的同名事件（可选）
DROP EVENT IF EXISTS evt_fy_rollover_2021;

DELIMITER //
CREATE EVENT evt_fy_rollover_2021
ON SCHEDULE AT '2021-06-30 00:00:00' -- 指定2021年6月30日零点执行
ON COMPLETION PRESERVE -- 执行完成后保留事件记录，不需要留存可改为NOT PRESERVE自动删除
DO
BEGIN
  -- 开启事务保证操作原子性，避免半更新导致数据异常
  START TRANSACTION;
    -- 将当前活跃财年状态改为N，同步更新更新时间，格式和原有表保持一致
    UPDATE tbl_fy 
    SET fy_active_sts = 'N', update_datetime = DATE_FORMAT(NOW(), '%c/%e/%Y %H:%i')
    WHERE fy_active_sts = 'Y';

    -- 插入新财年记录，日期格式完全匹配现有表规则
    INSERT INTO tbl_fy (fy_start_date, fy_end_date, fy_year, fy_active_sts, maker_datetime)
    VALUES (
      '7/1/2021',
      '6/30/2022',
      '2021-22',
      'Y',
      DATE_FORMAT(NOW(), '%c/%e/%Y %H:%i')
    );
  COMMIT;
END //
DELIMITER ;

年度自动切换扩展版（可选） #

如果需要实现每年自动切换财年，无需每年手动创建事件，可以修改调度规则和插入逻辑为动态生成：

DROP EVENT IF EXISTS evt_fy_yearly_rollover;

DELIMITER //
CREATE EVENT evt_fy_yearly_rollover
ON SCHEDULE EVERY 1 YEAR 
STARTS '2021-06-30 00:00:00' -- 首次执行时间，之后每年同时间自动执行
ON COMPLETION PRESERVE
DO
BEGIN
  START TRANSACTION;
    UPDATE tbl_fy 
    SET fy_active_sts = 'N', update_datetime = DATE_FORMAT(NOW(), '%c/%e/%Y %H:%i')
    WHERE fy_active_sts = 'Y';

    INSERT INTO tbl_fy (fy_start_date, fy_end_date, fy_year, fy_active_sts, maker_datetime)
    VALUES (
      DATE_FORMAT(NOW(), '%c/%e/%Y'), -- 自动生成当年7月1日
      DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR), '%c/%e/%Y'), -- 自动生成下一年6月30日
      CONCAT(YEAR(NOW()), '-', RIGHT(YEAR(NOW())+1, 2)), -- 自动生成财年标识如2021-22
      'Y',
      DATE_FORMAT(NOW(), '%c/%e/%Y %H:%i')
    );
  COMMIT;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Adil Riaz