不存储日期列时 如何按每日3章节奏实现bibles表的按日内容查询

核心思路 #

依托你已有的连续固定id序列，只需定义全局固定的锚点参数，完全不需要在bibles表中存储日期字段，每次查询实时计算章节偏移量即可实现映射，后续调整排期规则只需要修改锚点参数，无需批量更新全表数据。 #

固定锚点参数（全局配置即可） #

• 排期起始锚点日期：2020-02-23（排期第一天对应的自然日）
• 排期起始锚点章节id：365（排期第一天对应阅读的第一章id）
• 固定阅读规则：每日读3个章节
  注：当前1092条章节总数据，刚好对应排期周期内的总天数（364天3=1092章），完全匹配现有排期周期*

计算逻辑 #

1. 拿到用户传入的查询日期，计算该日期和排期起始锚点日期的间隔天数：间隔天数 = 查询日期 - 2020-02-23（结果为整数天，比如查2020-02-24时间隔天数为1）
2. 计算该查询日期对应的起始章节id：起始id = 365 + 间隔天数 * 3
3. 从bibles表中取起始id开始、连续3条的章节记录，把查询日期作为自定义字段拼接后即可返回。

效果验证 #

• 查询日期为2020-02-24时：
  • 间隔天数为1
  • 起始id = 365 + 1*3 = 368
  • 取id为368、369、370的3条记录，拼上日期2020-02-24返回，和预期结果完全一致
• 查询日期为排期最后一天2021-02-20时：
  • 间隔天数为363（因为排期第一天2020-02-23的间隔为0）
  • 起始id = 365 + 363*3 = 1454
  • 取id为1454、1455、1456的3条记录，拼上日期2021-02-20返回，和排期样例完全匹配

参考SQL实现（以MySQL为例） #

-- 以下常量可直接做成全局配置，后续调整排期只需要修改这几个值
SET @schedule_start_date = '2020-02-23';  -- 排期起始锚点日期
SET @start_chapter_id = 365;               -- 排期第一天对应的起始章节id
SET @chapters_per_day = 3;                 -- 每日阅读章节数
SET @query_date = '2020-02-24';             -- 用户传入的查询日期

SELECT
  id,
  chapter_name,
  @query_date AS date
FROM bibles
WHERE
  -- 直接通过id范围筛选，走主键索引，查询效率极高
  id BETWEEN
    @start_chapter_id + DATEDIFF(@query_date, @schedule_start_date) * @chapters_per_day
    AND
    @start_chapter_id + DATEDIFF(@query_date, @schedule_start_date) * @chapters_per_day + @chapters_per_day - 1
ORDER BY id ASC;

配套优化建议 #

• 可在查询逻辑中增加边界校验：当查询日期早于排期起始日、晚于排期结束日，或计算出的id不在365-1456的固定范围内时，返回“当前日期无对应阅读排期”的提示
• 后续需要调整年度排期时，只需要修改锚点日期和对应的起始章节id，不需要批量更新bibles表的任何数据，零维护成本

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