MySQL动态计算3年循环圣经调度查询，兼容两个id区间

基础规则与现存问题 #

• 现有bibles表仅包含id、chapter_name两个字段，无预存调度日期字段，避免3年周期到期后批量更新的维护成本
• 表内id分两类固定范围：
  • 第一类：id 1~364，共364条固定内容，末条为《Revelation 22》
  • 第二类：id 365~1456
• 调度规则：
  • 周期覆盖2021-09-05至2024-08-31，共3年，同一章节内容循环映射3次
  • 固定映射锚点：id1（Mathews 1）分别对应2021-09-05、2022-09-04、2023-09-03
  • 同一循环内，日期每过1天，id递增1
  • 校验要求：查询2021-09-08需返回id4（Mathews 4）；查询2022-09-06需返回id3（Mathews 3）
• 现存问题：原有计算逻辑仅适配2021年的第一类id查询，2022、2023年的日期计算结果会飘移到第二类id区间，返回错误内容，无法兼容两类id的调度需求

核心计算逻辑 #

整个计算过程不需要依赖表内存储的日期字段，完全通过日期差值动态推导：

1. 以调度起始日2021-09-05为时间零点，计算任意查询日期与该零点的累计天数差
2. 相邻两个id1的映射日间隔为364天，即每364天id计数重置，进入下一个循环
3. 用累计天数差对364取模，得到当前循环内的偏移天数，结合当前循环对应的id区间起始值，即可算出目标id
4. 该方案的优势：调整循环对应的id区间时，仅需修改计算逻辑的常量值，不需要修改表结构，维护成本低

可直接使用的MySQL实现 #

基础版本（适配原有映射规则） #

-- 替换@query_date为实际查询日期
SET @query_date = '2022-09-06';

SELECT id, chapter_name
FROM bibles
WHERE id = 1 + MOD(
    DATEDIFF(@query_date, '2021-09-05'),
    364
);

兼容双id区间版本（支持第二类id调度） #

当后续需要为不同循环段匹配不同id范围的内容时（比如第一个循环用第一类id、后两个循环用第二类id），可直接扩展计算逻辑，无需调整表结构：

-- 替换@query_date为实际查询日期
SET @query_date = '2022-09-06';
SET @total_days = DATEDIFF(@query_date, '2021-09-05');
SET @cycle_index = FLOOR(@total_days / 364); -- 计算当前所在循环段（0/1/2）
SET @cycle_offset = MOD(@total_days, 364);

-- 定义每个循环段对应的id起始值，可根据实际业务调整
SET @target_id = CASE @cycle_index
    WHEN 0 THEN 1 + @cycle_offset    -- 第一循环用第一类1~364
    WHEN 1 THEN 365 + @cycle_offset -- 第二循环用第二类365~728
    WHEN 2 THEN 729 + @cycle_offset -- 第三循环用第二类729~1092
END;

SELECT id, chapter_name FROM bibles WHERE id = @target_id;

结果校验 #

• 测试1：查询2021-09-08，累计天数差为3，取模得3，计算id=4，符合预期
• 测试2：查询2022-09-06，累计天数差为366，取模得2，计算id=3，符合预期
• 当查询日期落在第二循环段时，计算的id会直接映射到第二类365~728的范围，实现双id区间兼容

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