MySQL农历数据库：覆盖130年节气与班休的日历表设计

发布时间：2026/10/9 20:22:09
MySQL农历数据库：覆盖130年节气与班休的日历表设计
简介一套MySQL农历数据库资料包覆盖1970年至2100年面向需要处理农历、节气、法定假日的业务开发或数据分析场景。库内以农历表为主数据除基础日期外还补充了闰月标识、24节气、星期及班休区分另配假日表存放历年国家规定假日同时支持自定义班休日期可通过连表查询获得某日的上班或休息安排。这样解决“多种语言算法结果不理想、现有数据库字段不全”的痛点适用于排班系统、日历应用、节日提醒等场景。资源共3个文件包括2个SQL脚本和1个XML查询文件压缩包仅2.56MBSQL脚本分别负责农历主表和假日表的建表与数据导入XML文件提供查询逻辑参考便于快速复制使用。已有1453人学习下载说明该方案在实践中经过一定验证。若现有假日规则不满足需求还可基于表结构自行完善灵活扩展。1. mysql农历数据库一张表装下 130 年的日期、节气与班休做排产、做考勤、做报表只要业务和“节假日”沾边就绕不开同一个问题公历日期好查农历难班休更难。很多人一开始以为农历是“算”出来的真动手才发现靠谱的做法是直接把 1970-2100 的农历数据落成一张 MySQL 表闰月、24 节气、法定假日、星期、班休状态全部预先标好让业务查询从“算”变成“查”。这套 mysql 农历数据库方案表结构不复杂真正花精力的是字段怎么设计、数据怎么生成、未来年份的法定假日怎么持续维护。本文适合需要做排班、考勤、报表、提醒服务的开发者照着建表、生成、验证就能直接用于生产。2. 表结构设计闰月、节气、班休怎么落字段2.1 从业务查询倒推字段设计一张日历表我习惯先列出业务方真正会问的问题再倒推字段。常见问题无非这几类某个公历日期对应的农历是哪天、是不是闰月某天有没有节气、是什么节气某天是法定假日还是普通周末某天到底上班还是休息包含调休补班。把这些需求翻译成字段表的核心结构就很清晰了公历日期作为主键农历的年月日拆开存储闰月用独立标记位节气名和假日名各占一个可空字段星期和班休状态单独存放。注意一点农历月日不建议只存一个“2024-01-01”这样的字符串排序和区间查询都会很别扭拆成数字字段更利于索引和聚合。另外班休状态不能只存一个“是否休息”的布尔值因为法定假日和调休补班之间存在交叉正常周末可能被调成上班日普通工作日也可能被调成休息日。用一个day_type枚举字段区分状态比单纯两个布尔位可靠得多。下面是我在模拟项目X里用到的一套设计经过多个版本迭代基本稳定。2.2 闰月与班休的编码约定农历数据里最容易出错的不是日期本身而是“闰月”的表达。一个农历年可能有两个四月如果不区分前端展示和后端计算都会乱套。我的方案是lunar_month存 1-12 的数字is_leap_month用 0/1 标记是否闰月同时冗余一个month_name字段直接存“闰四月”这样的展示文本查询结果不用二次拼接。班休区分的约定更重要。我定义四种状态WORKDAY表示普通工作日RESTDAY表示普通休息日周末但没被调休HOLIDAY表示法定节假日LEAP_WORKDAY表示调休补班日——也就是为了凑连休把原本的周末变成上班日。这四种状态互斥一张表里每天只可能有一个状态。这里有个关键选择is_workday和is_restday两个字段是不是冗余不是。它们是把day_type翻译成业务可以直接用的布尔位避免业务 SQL 里到处写 case when。比如LEAP_WORKDAY这天day_type是调休上班is_workday为 1is_restday为 0。两份数据完全一致但消费方的 SQL 能简单一个WHERE is_workday 1就拿到所有要出勤的日期。2.3 建表 SQL 与存储参数下面这段建表 SQL 是我实际在用的版本1970-2100 共约 4.8 万行单表存储压力很小但字段设计要一步到位否则后面改字符集或加索引代价都很大CREATE TABLE lunar_calendar ( solar_date DATE NOT NULL COMMENT 公历日期主键, lunar_year SMALLINT NOT NULL COMMENT 农历年如 2024, lunar_month TINYINT NOT NULL COMMENT 农历月1-12, lunar_day TINYINT NOT NULL COMMENT 农历日1-30, is_leap_month TINYINT NOT NULL DEFAULT 0 COMMENT 是否闰月1是0否, month_name VARCHAR(10) NOT NULL COMMENT 月份展示名如 正月、闰四月, day_name VARCHAR(10) NOT NULL COMMENT 日展示名如 初一、十五, ganzhi_year VARCHAR(10) DEFAULT NULL COMMENT 干支纪年如 甲辰, shengxiao VARCHAR(4) DEFAULT NULL COMMENT 生肖如 龙, jieqi VARCHAR(4) DEFAULT NULL COMMENT 节气名无节气则为NULL, festival VARCHAR(50) DEFAULT NULL COMMENT 法定假日名如 春节、国庆节, weekday TINYINT NOT NULL COMMENT 星期1-7对应周一至周日, is_workday TINYINT NOT NULL COMMENT 是否上班1上班0休息, is_restday TINYINT NOT NULL COMMENT 是否休息1休息0上班, day_type VARCHAR(20) NOT NULL COMMENT WORKDAY/RESTDAY/HOLIDAY/LEAP_WORKDAY, PRIMARY KEY (solar_date), KEY idx_lunar_month (lunar_year, lunar_month, is_leap_month), KEY idx_day_type (day_type), KEY idx_jieqi (jieqi) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT农历节气法定假日班休日历表;几个设计细节说明一下。主键直接选solar_date因为所有业务查询最终都要落到公历日期上不需要额外自增 id。lunar_year和lunar_month拆开存而不是存一个字符串是因为要支持“查某年闰四月所有日期”这类范围查询拆开后能走联合索引。jieqi和day_type加索引是为了应付“查所有节气日期”“查某年所有调休补班日”这类定时任务扫描。存储参数上这张表数据量小InnoDB 默认配置就够不需要分区。唯一建议是字符集务必用utf8mb4因为节气名和假日名里有生僻字utf8mb3在个别字上会报错。排序规则我用默认的utf8mb4_0900_ai_ci中文按拼音排序在这种表里基本用不到不用额外折腾。如果业务上要频繁按农历月份统计可以把idx_lunar_month的索引顺序调整为(lunar_year, lunar_month, is_leap_month, lunar_day)把日也带进去避免回表。3. 生成 1970-2100 数据从算法到落地脚本3.1 农历推算查表法而不是“纯计算”农历没有简单的公历换算公式这是很多初学者翻车的地方。现在主流做法是查表法用一个常数数组描述从某个基准年开始每一年的农历月结构——每个月是大月 30 天还是小月 29 天、哪个月是闰月、闰月跟在谁后面。这个数组在开源社区里流传很广是历代开发者根据权威农历资料整理出来的覆盖范围通常是 1900-2100 年正好包含我们需要的 1970-2100。查表法的原理不复杂数组的每一个元素是一个 4 字节十六进制数拆成二进制后某些位表示闰月位置另外 12 个位分别表示 12 个农历月是大月还是小月。拿到了每一年的月结构再从基准日通常是 1900 年正月初一对应的公历日期开始累加天数就能得到任意公历日期对应的农历日期。我一般不会自己逐位解析这个十六进制数组而是直接用别人验证过的成品算法但会把核心逻辑读一遍避免把闰月位理解反。下面这段 Python 代码是农历转换的最小实现骨架注释里标注了每个参数的含义# lunar_info 数组的第 n 项描述 lunar_info_base_year n 年的农历月结构 # 每个元素是 4 字节整数位 0-11 表示 12 个月的大小月1大月30天0小月29天 # 位 16-19 表示闰月位置0 表示无闰月 # 位 20 表示闰月是否为大月 def lunar_to_solar(lunar_year, lunar_month, lunar_day, is_leap): 将农历日期转换为公历日期查表法核心 offset 0 # 先累加从基准年到目标年的总天数 for y in range(lunar_base_year, lunar_year): offset year_days(y) # year_days 从 lunar_info 解析当年天数 # 再累加目标年内从正月初一到目标月日的天数 for m in range(1, lunar_month): offset month_days(lunar_year, m, False) if is_leap: offset month_days(lunar_year, lunar_month, True) offset lunar_day - 1 # 基准日加上 offset 天就是对应的公历日期 return lunar_base_date timedelta(daysoffset)这个骨架的逻辑是先算目标农历年在基准年之后第几年累加前面所有整年的天数再累加目标年内前面月份的天数最后加上日偏移。month_days需要额外判断闰月参数因为闰月和正月的天数可能不同。实际生产脚本里我会把整个lunar_info数组单独放在一个 py 文件里不手改只调用函数。3.2 Python 脚本生成基础数据生成基础数据的过程分三步先按公历日期从 1970-01-01 遍历到 2100-12-31每行调用农历转换函数拿到农历年月日再计算节气最后标记星期和默认班休状态。遍历 130 年约 4.8 万天Python 脚本跑一遍在秒级完全不需要优化。下面是生成 CSV 的核心循环注意我把节气和假日留到后面步骤处理先生成“骨架数据”import csv from datetime import date, timedelta start_date date(1970, 1, 1) end_date date(2100, 12, 31) current start_date with open(lunar_base.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([solar_date, lunar_year, lunar_month, lunar_day, is_leap_month, month_name, day_name, weekday]) while current end_date: ly, lm, ld, leap solar_to_lunar(current) # 查表法转换 writer.writerow([ current.isoformat(), ly, lm, ld, leap, lunar_month_name(ly, lm, leap), # 生成“闰四月”等展示名 lunar_day_name(ld), # 生成“初一”等展示名 current.isoweekday() # 1-7 对应周一至周日 ]) current timedelta(days1)solar_to_lunar是前面查表法代码的完整实现输入公历日期输出农历年、月、日、是否闰月。lunar_month_name和lunar_day_name是查展示名数组比如正月、冬月、腊月、初一、十五这些都是固定映射写成字典即可。这里有个参数容易踩坑isoweekday()返回 1 是周一、7 是周日而很多业务习惯用 0 表示周日导入 MySQL 前一定要确认口径。我在模拟项目X里就遇到过同一张表一个服务按isoweekday判断、另一个服务按CURDATE() % 7判断两边结果差一天最后统一改成isoweekday才消停。3.3 节气与法定假日公式算节气维护表存假日节气不适合用查表法硬编码因为节气是天文时刻同一节气在不同年份的公历日期可能差一到两天。简单可靠的做法是用太阳黄经公式近似计算春分是太阳黄经 0 度之后每 15 度一个节气跑一遍回归年 24 个节气就能拿到全部日期。精度上这种近似算法对“哪一天是节气”这种粒度完全够用误差不会超过几小时不会影响日期归属。节气的 Python 计算网上有很多现成公式核心思路是算每个节气时刻对应的公历日期。需要说明的是计算出来的时刻是东八区时间还是 UTC 取决于公式实现里的时区常量我一般统一按东八区处理避免出现节气落在边界时刻、日期归属差一天的尴尬。法定假日则完全不同它不遵守任何公式。每年国家发布的节假日安排通知要到前一年年底才公布调休补班日也是每年唯一确定的。所以法定假日数据不能靠脚本算必须维护一个独立的假日表每年手动录入再和基础日历表关联。维护表结构很简单CREATE TABLE holiday_policy ( holiday_year SMALLINT NOT NULL COMMENT 年份, solar_date DATE NOT NULL COMMENT 公历日期, festival VARCHAR(50) NOT NULL COMMENT 假日名如 春节、国庆节, day_type VARCHAR(20) NOT NULL COMMENT HOLIDAY 或 LEAP_WORKDAY, PRIMARY KEY (solar_date), KEY idx_holiday_year (holiday_year) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT每年法定假日与调休安排表;这张表只在每年年底更新一次。更新时把新一年的假日和调休日插入然后跑一条 UPDATE 把主表里对应日期的festival、day_type、is_workday、is_restday同步过来。这样主表始终是业务直接查询的最终数据holiday_policy是每年变更的“配置表”两者职责分离历史数据也不会被误改。4. 在 MySQL 里查农历和班休SQL 与索引用法4.1 高频查询 SQL数据生成完业务上最常用的就是三类查询今天是否上班、某个月有哪些休息日、某个农历日期对应哪几天公历。第一类查询最频繁直接查单行SELECT solar_date, lunar_year, lunar_month, lunar_day, is_leap_month, month_name, day_name, jieqi, festival, weekday, is_workday, is_restday, day_type FROM lunar_calendar WHERE solar_date CURDATE();这条 SQL 走主键是全表最快的查询。CURDATE()取的是数据库服务器当前日期注意服务器时区要设置正确否则在夜里 0 点前后会有跨天问题。我习惯在连接串里显式指定时区参数不让它依赖服务器默认配置。第二类查询是排班表最常见的——查某月所有休息日。注意这里有个性能细节不要写WHERE YEAR(solar_date) 2025 AND MONTH(solar_date) 5因为对solar_date用了函数后主键索引就失效了4 万多行全扫一遍虽然不慢但后面数据量大了或有其他复杂筛选时容易养成坏习惯。正确写法是范围查询SELECT solar_date, month_name, day_name, festival, weekday, day_type FROM lunar_calendar WHERE solar_date BETWEEN 2025-05-01 AND 2025-05-31 AND is_restday 1 ORDER BY solar_date;BETWEEN两个明确日期是标准的索引范围扫描即使再加其他条件优化器也能先用主键把数据圈定在一个小范围里。is_restday 1会同时命中法定假日和普通周末正好是排班需要的“不用上班的日子”全集。第三类查询是从农历反查公历比如用户输入“明年闰四月十五”要查对应公历日期。这时候联合索引idx_lunar_month就派上用场了SELECT solar_date, solar_date, month_name, day_name, festival FROM lunar_calendar WHERE lunar_year 2025 AND lunar_month 4 AND is_leap_month 1 AND lunar_day 15;lunar_year lunar_month is_leap_month三个等值条件走联合索引非常快。需要提醒的是is_leap_month这个字段千万别省否则“四月十五”和“闰四月十五”会一起查出来业务上无法区分。我见过不止一次因为漏掉这个条件把祭祖提醒发错日期的翻车现场。4.2 把常用判断封装成视图高频 SQL 每次重复写容易出错尤其day_type的判断逻辑散落在多个服务里时改一处漏一处。我会建一个视图把“今天是否上班”“今天是什么节假日”这类口径固定下来CREATE VIEW v_today_schedule AS SELECT solar_date, month_name, day_name, CASE WHEN festival IS NOT NULL THEN festival WHEN jieqi IS NOT NULL THEN jieqi ELSE 普通日 END AS day_label, day_type, is_workday, is_restday, weekday FROM lunar_calendar;视图的价值不是性能而是统一口径。业务方只需要SELECT * FROM v_today_schedule WHERE solar_date ?不用关心底层是day_type判断还是is_workday判断。后续如果要调整“除夕是否算春节假日”这类规则只改视图不动业务代码。4.3 索引与执行计划验证索引建完一定要用EXPLAIN验证不能靠猜。我最常检查的两条一是WHERE solar_date BETWEEN ? AND ?是否走的 PRIMARY 索引且type为range二是WHERE lunar_year? AND lunar_month? AND is_leap_month?是否走的idx_lunar_month且key_len符合预期。EXPLAIN SELECT solar_date FROM lunar_calendar WHERE lunar_year 2025 AND lunar_month 4 AND is_leap_month 0;如果看到type是ALL或者rows超过几千说明索引没建对或者字段类型不匹配。常见原因就是lunar_year存成了字符串或者建表时is_leap_month用了BIT类型导致索引无法精确匹配。在 1970-2100 这个数据规模下任何全表扫描都能跑完但不代表可以放任不管因为后续这张表很可能被 JOIN 到几百万行的业务表上。5. 常见问题与排查农历对不上、节气偏一天、班休标错的 5 个坑5.1 现象1970 年初的农历日期对不上有人用脚本生成数据后发现1970 年 1 月的农历日期和日历软件对不上而且越往 1 月初越离谱。原因是查表法的基准日通常设在 1900 年但如果拿到的lunar_info数组只覆盖 1900-2100而脚本里基准日偏移计算写错了一位就会导致整个时间段整体偏移。更隐蔽的是时区问题公历日期按东八区算但算法内部若用了 UTC 日期对象在 1970 年初会有 8 小时的偏移导致个别日期错一天。解决方法是先校准基准日。用已知日期对拍比如取一个自己确定无误的农历日期跑脚本验证输出的公历是否正确。确认基准偏移量再把lunar_base_date修正。这类问题排查时不建议用“随机抽样”要挑边界日期验证1 月 1 日、12 月 31 日、闰月出现的月份这些位置最容易暴露偏移。5.2 现象节气日期整体偏一天节气用天文公式计算时如果公式输出的时刻是 UTC而业务在东八区使用那么当节气时刻落在 UTC 当天 16 点到 24 点之间时东八区已经进入第二天日期归属就会差一天。这不是公式精度问题是时区换算问题。解决方法是明确公式输出时区并在计算后统一加 8 小时再取日期。另一个建议是永远输出完整时间戳而不是只输出日期因为节气时刻在某些应用比如养生提醒、农业指导里本身就有业务价值只存日期会丢失信息。我在表里只留了jieqi日期字段但生成脚本里是算了完整时刻再截断日期的这样以后要加时刻字段还有后悔药。5.3 现象2026 年及以后的法定假日全是空的数据生成时2024 年之前的假日可以按历史通知补录但从生成那天起未来年份的节假日安排还没有发布表里自然查不到。这不是 bug是数据维护节奏问题。业务方如果在 2025 年就查 2026 年国庆节得到空结果是正常的但产品上要提前提示不能让用户以为是系统故障。解决方法是把节假日维护流程固定下来每年 11 月左右关注国家发布的次年节假日安排通知拿到后第一时间更新holiday_policy表再跑同步脚本更新主表。我在某跨平台系统里踩过这个坑——上线时只导入了当年假日第二年 1 月被用户投诉“春节不显示了”其实只是没做年度维护。从那以后我把假日更新做成了每年固定运维事项而不是一次性工作。5.4 现象闰月数据错位春节对不上如果某年开始农历月份整体错位比如正月变成了二月大概率是lunar_info数组中某一年的数据解析错误导致从这一年开始后续所有累加天数全部偏移。这种错误比较隐蔽因为不是所有日期都错而是某个时间点之后全错。排查方法是定位第一个出错日期然后用二分法缩小范围先查 1980 年再查 1990 年找到出错区间后单独检查对应年份的lunar_info解析结果看当年月结构是否合理比如是否存在两个相同月份、闰月位置是否异常。我通常会在脚本里加一条自检逻辑每一年的农历天数必须等于该年各月天数之和且总天数必须是 29 的倍数附近异常时直接报错。5.5 现象导入 MySQL 后中文乱码或 CSV 日期被电子表格工具改写生成 CSV 后用 MySQL 的LOAD DATA导入中文节气名和假日名全部变成问号这基本是字符集问题CSV 文件是 UTF-8 编码但导入会话的character_set_client是 latin1。解决方法是导入前显式设置字符集或者在建表后用LOAD DATA ... CHARACTER SET utf8mb4指定。另一个经典的坑是生成的 CSV 用电子表格工具打开过再另存日期列被改成了2025/5/1这样的格式导入时 MySQL 不认。建议生成 CSV 后不要用电子表格工具二次编辑直接用文本编辑器或命令行处理。如果必须编辑确认日期列格式没有被改动否则LOAD DATA导入会静默把日期变成0000-00-00这种脏数据最麻烦。我后来直接在生成脚本里输出 SQL 的INSERT语句或者用mysql命令直连导入绕开中间环节。6. 进阶查询函数化、数据核对与年度增量刷新数据表稳定之后我会再做三件事把它从“能查”变成“好用”。第一件事是封装存储函数把“某天是否上班”变成可直接调用的函数比如CREATE FUNCTION is_work_day(d DATE) RETURNS TINYINT RETURN (SELECT is_workday FROM lunar_calendar WHERE solar_date d);业务方调用SELECT is_work_day(2025-05-01)即可。函数的好处是把查表逻辑藏在数据库里应用层不再依赖日历来表名和字段。第二件事是做数据核对取最近几年的农历日期和节气日期和已有的日历工具对拍重点抽查闰月出现年份以及冬至、夏至这些节气日期容易因时区算错的节点。每次生成数据后我都会固定跑一遍核对脚本不核对不上线。第三件事是年度增量刷新。主表数据本身是 1970-2100 全量生成的不需要每年重新生成但holiday_policy表必须每年追加。我的习惯是上一年的节假日数据保留不动新一年数据用INSERT ... ON DUPLICATE KEY UPDATE更新这样既能保留历史又能覆盖因为临时政策调整而变更的日期。等到 2100 年这张表的历史使命就完整了。这套表我已经在多个项目里复用每次新业务接入时只需要问清楚一个问题他们说的“节假日”指的是法定节假日还是包含调休在内的完整休息日这两个口径差了很远表结构虽然一样但查询条件完全不同。我的习惯是建表时就把两种口径都做成独立视图让业务方自己选避免后续扯皮。希望这些设计思路和踩坑记录能帮到你也建议你在自己的生产环境里先跑通 1970-2100 的生成脚本再决定要不要完全信任这套数据。本文还有配套的精品资源点击获取