简介这份面向 MySQL 开发者的农历与节假日数据资源针对农历转换、二十四节气查询及法定假日班休安排等业务场景提供可直接导入使用的数据表方案。包内共 3 个文件以 2 个 SQL 脚本为主分别承载农历主数据和年度假日数据另附 1 个 XML 查询示例便于快速理解关联查询方式压缩包约 2.56MB体量轻便。数据覆盖 1970—2100 年包含闰月标识、二十四节气、星期、法定假日及班休区分并支持自定义假日与班休日期通过农历表与假日表连表查询得到完整安排。已有 1453 人学习下载适合需要完善排班、日程、节气展示或节假日判断功能的后端开发与数据库设计人员参考也可在此基础上按自身业务补充和修正数据。1. 排班系统上线前夜发现的一件事这份 MySQL 农历库到底能干什么排班系统上线前一天测试同学发来一句这天明明是周六系统为什么标成休息日它应该是调休上班。类似的坑还有春节前后农历年跨了公历年、生日按农历查会命中两行、24 节气只能靠第三方包硬算。最后我把整套历法逻辑从业务代码里搬进了 MySQL——一份覆盖 1970 到 2100 年的农历数据库一天一行把公历日期、农历年月日、闰月标记、24 节气、法定假日、星期以及班休区分全部存成表中的记录。查农历不用再写算法查节气不用再引依赖一句 SQL 就能问出某天到底上不上班。做考勤、排班、日历组件、生日提醒的开发者基本都能直接用上。2. 表结构拆解一天一行把农历、节气、假日和班休存进同一张表拿到这份数据第一件事是决定怎么存。常见的两种存法是「一天一行」的大宽表或者「公历表 农历表 假日表」拆开。我选的是前者原因很实在1970-01-01 到 2100-12-31 一共 47847 天一天一行也就四万多条整表加起来几 MB一次全量加载到内存都行。既然数据量小到可以忽略就没必要为了范式去拆表、去 JOIN查询越简单越好。2.1 主表字段设计公历、农历、节气、班休共存主表直接拿solar_date当主键省掉自增 id也让所有点查都走主键。农历侧用lunar_year / lunar_month / lunar_day三个数字字段存再额外冗余lunar_month_cn / lunar_day_cn两个中文名前端渲染时不用自己拼「闰四月」「廿三」这种字符串。闰月用一个独立的is_leap_month标记而不是塞进月份数字里——这一点很关键后面避坑章节会细说。-- 主表公历一天一行农历/节气/假日/班休全部冗余在同一行 CREATE TABLE calendar_day ( solar_date DATE NOT NULL COMMENT 公历日期直接做主键, solar_year SMALLINT UNSIGNED NOT NULL COMMENT 公历年, solar_month TINYINT UNSIGNED NOT NULL COMMENT 公历月 1-12, solar_day TINYINT UNSIGNED NOT NULL COMMENT 公历日 1-31, lunar_year SMALLINT UNSIGNED NOT NULL COMMENT 农历年以正月初一为界, lunar_month TINYINT UNSIGNED NOT NULL COMMENT 农历月序号 1-12, lunar_day TINYINT UNSIGNED NOT NULL COMMENT 农历日 1-30, is_leap_month TINYINT(1) NOT NULL DEFAULT 0 COMMENT 所属农历月是否闰月, lunar_month_cn VARCHAR(8) NOT NULL COMMENT 正月/闰四月/腊月, lunar_day_cn VARCHAR(8) NOT NULL COMMENT 初一/十五/廿三, ganzhi_year VARCHAR(8) DEFAULT NULL COMMENT 干支纪年如甲辰, zodiac VARCHAR(4) DEFAULT NULL COMMENT 生肖, solar_term VARCHAR(8) DEFAULT NULL COMMENT 24节气名非节气日为 NULL, week_day TINYINT UNSIGNED NOT NULL COMMENT 1周一 … 7周日, day_type TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 0普通工作日 1周末 2法定假日 3调休上班, holiday_name VARCHAR(16) DEFAULT NULL COMMENT 所属节假日名称, is_workday TINYINT(1) NOT NULL DEFAULT 1 COMMENT 1上班 0休息, PRIMARY KEY (solar_date), KEY idx_solar_ym (solar_year,solar_month), KEY idx_lunar (lunar_year,lunar_month,lunar_day,is_leap_month), KEY idx_term (solar_term), KEY idx_type_date (day_type,solar_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT公历-农历-节气-假日日历主表;字段里有两个容易被人问的点。day_type和is_workday看着重复其实分工不同day_type是「这天是什么性质」is_workday是「这天到底上不上班」。调休上班日就是典型例子它是周六week_day 6但day_type 3、is_workday 1。业务代码里判断上不上班永远只看is_workday判断要不要标红、标「休」字才看day_type。另一个是solar_term一年只有 24 天有值其余全为 NULL单列存完全够用没必要为它单独建表。2.2 附属表农历月表与节假日定义表不能省主表是「展开后的结果」但在维护和校验层面还需要两张配置型小表。第一张是农历月表记录每个农历月的天数29 或 30和对应的公历起止日期。它的作用是支撑「这个农历月有多少天」「按农历月生成月历」这类需求同时用来校验主表——如果某年出现了 13 个农历月那这 13 个月里必须恰好有一个is_leap_month 1。-- 农历月表大小月 闰月主表的数据源之一 CREATE TABLE lunar_month ( lunar_year SMALLINT UNSIGNED NOT NULL, lunar_month TINYINT UNSIGNED NOT NULL COMMENT 1-12, is_leap_month TINYINT(1) NOT NULL DEFAULT 0, month_days TINYINT UNSIGNED NOT NULL COMMENT 29 或 30, start_date DATE NOT NULL COMMENT 该农历月对应的公历首日, end_date DATE NOT NULL COMMENT 该农历月对应的公历末日, PRIMARY KEY (lunar_year,lunar_month,is_leap_month) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT农历月表;第二张是节假日定义表。这张表的价值在于「可维护」每年的放假安排是会变的如果所有假日信息只写死在主表的holiday_name里第二年就得重新导一份全量数据。把节假日抽成一张按年起止区间定义的配置表运营侧只改这几行再用一条 UPDATE 展开回主表维护成本立刻降下来这也是我一般会推荐的做法。-- 节假日定义表按区间配置再展开回主表 CREATE TABLE holiday_def ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, holiday_year SMALLINT UNSIGNED NOT NULL, holiday_name VARCHAR(16) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, day_type TINYINT UNSIGNED NOT NULL COMMENT 2法定假日 3调休上班, source VARCHAR(32) DEFAULT NULL COMMENT 数据批次标记便于回滚, PRIMARY KEY (id), KEY idx_year (holiday_year), KEY idx_range (start_date,end_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT节假日区间配置;2.3 索引与数据边界四万多行该怎么建索引索引不用想太复杂按真实查询模式来即可。最高频的是「拉某年某月整月日历」所以(solar_year, solar_month)联合索引必须有它是范围扫描命中后直接按主键顺序返回不需要额外排序。第二高频的是「农历反查公历」对应(lunar_year, lunar_month, lunar_day, is_leap_month)这条联合索引四个字段的顺序不能乱把is_leap_month放最后是因为它的区分度最低放前面反而破坏前缀匹配。第三个是节气查询solar_term单独建索引配合IS NOT NULL过滤一年也就 24 行随手一查就出来。数据边界方面有两个地方要提前说清。起点 1970-01-01 是为了兼容 Unix 时间戳的常见下限终点 2100-12-31 则是因为再往后的农历推算误差会累积公开的历法数据一般也就到 2100。这意味着如果你的业务里有「用户出生于 2101 年」这种数据现实中当然不会有但测试数据可能有查询会直接返回空代码里必须留一条兜底分支别让它把整个接口带崩。3. 建库与导入把 SQL 文件写入 MySQL 并校验到行级结构定了接下来是把它真正运行起来。这一步最容易出错的地方在于字符集和导入方式很多人其实都卡在这一步。下面按顺序来每一步都给出可直接执行的命令。3.1 建库建表与字符集设定建库时把utf8mb4显式写上别指望服务端默认值。有些环境默认还是latin1或者utf8三字节那个中文月名「闰四月」在utf8下是能存的但节气名和生僻字比如「廿」在某些排序规则下会出问题。统一用utf8mb4utf8mb4_general_ci一劳永逸。# 1) 建库强制 utf8mb4避免中文月名/节气名落库变问号 mysql -h 127.0.0.1 -P 3306 -u root -p \ --default-character-setutf8mb4 \ -e CREATE DATABASE IF NOT EXISTS calendar_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; # 2) 先跑结构文件再跑数据文件顺序不能反 mysql -h 127.0.0.1 -u root -p --default-character-setutf8mb4 calendar_db schema.sql mysql -h 127.0.0.1 -u root -p --default-character-setutf8mb4 calendar_db calendar_data_1970_2100.sql命令行里的--default-character-setutf8mb4是必须的它管的是客户端与服务端之间的握手字符集。少了它即使表是 utf8mb4导入过程中中文也会在传输层被截断最后的报错信息里全是??????非常不好排查。3.2 三种导入方式source、LOAD DATA 与程序批插如果数据文件本身就是.sql格式直接在交互式客户端里source或者像上面那样用重定向是执行最快的。几万行的 INSERT 语句默认配置下大概十几秒到一分钟取决于max_allowed_packet。如果文件很大、单条 INSERT 太长而报Packet too large先在会话里把max_allowed_packet调大。-- 在 mysql 客户端里执行防止大文件导入被包大小限制打断 SET GLOBAL max_allowed_packet 256 * 1024 * 1024; SET NAMES utf8mb4; -- 导入期间临时关掉唯一性检查能快不少导完记得开回来 SET UNIQUE_CHECKS 0; SET FOREIGN_KEY_CHECKS 0; -- source /path/to/calendar_data_1970_2100.sql; SET UNIQUE_CHECKS 1; SET FOREIGN_KEY_CHECKS 1;如果手里是 CSV 而不是 SQL走LOAD DATA LOCAL INFILE效率高一个量级。这时候要注意两点一是文件编码必须是 UTF-8 无 BOM有 BOM 的话第一行第一个字段会带一个不可见字符二是 CSV 里的空格子会导入成空字符串而不是 NULL节气字段尤其明显导完得补一条 UPDATE。-- CSV 导入字段顺序必须和文件列一一对应 LOAD DATA LOCAL INFILE /data/calendar_day.csv INTO TABLE calendar_day CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (solar_date, solar_year, solar_month, solar_day, lunar_year, lunar_month, lunar_day, is_leap_month, lunar_month_cn, lunar_day_cn, ganzhi_year, zodiac, solar_term, week_day, day_type, holiday_name, is_workday); -- CSV 里的空串会变成 统一刷回 NULL否则 IS NOT NULL 查节气会多出一堆脏行 UPDATE calendar_day SET solar_term NULL WHERE solar_term ;LOAD DATA里的IGNORE 1 LINES跳过表头OPTIONALLY ENCLOSED BY 处理带引号的字段CHARACTER SET utf8mb4声明文件编码这三个参数少一个都可能出错。客户端启动时还需要带--local-infile1服务端也要local_infile ON否则会直接报The used command is not allowed。3.3 导入后的完整性校验导入完成后不要急着接业务代码先跑几条校验 SQL确认数据真的全了。这几条 SQL 我每次导完都会跑已经成了习惯。-- 1) 行数与日期边界1970-01-01 到 2100-12-31 应为 47847 天 SELECT COUNT(*) AS total_rows, DATEDIFF(2100-12-31,1970-01-01) 1 AS expect_rows, MIN(solar_date) AS min_date, MAX(solar_date) AS max_date FROM calendar_day;如果total_rows和expect_rows对不上八成是数据文件缺段或者IGNORE 1 LINES把某个年份的开头吃掉了先把这两个数对齐再往下走。-- 2) 农历字段合法性日必须是 1-30闰月标记只能是 0/1 SELECT SUM(lunar_day NOT BETWEEN 1 AND 30) AS bad_lunar_day, SUM(is_leap_month NOT IN (0,1)) AS bad_leap_flag, SUM(week_day NOT BETWEEN 1 AND 7) AS bad_week_day FROM calendar_day; -- 3) 每年农历月数应为 12 或 13且 13 个月的年份只能有一个闰月 SELECT lunar_year, COUNT(DISTINCT CONCAT(lunar_month,-,is_leap_month)) AS month_cnt, SUM(is_leap_month) AS leap_cnt FROM lunar_month GROUP BY lunar_year HAVING month_cnt NOT IN (12,13) OR leap_cnt 1; -- 4) 每个公历年应有 24 个节气日缺了说明该年数据有洞 SELECT solar_year, COUNT(*) AS term_cnt FROM calendar_day WHERE solar_term IS NOT NULL GROUP BY solar_year HAVING term_cnt 24;第 4 条特别值得跑。节气数据的错误往往不是整年丢失而是某一年中间少了一两天肉眼看文件根本发现不了只有按年 group by 数一下才能暴露。4. 查询实战考勤、生日与节气提醒的 6 条 SQL数据齐了接下来是这份库真正创造价值的地方。下面几条 SQL 覆盖了绝大部分实际需求可以直接抄到项目里改。4.1 按年月拉出整月班休日历日历组件、考勤月视图这类需求本质就是把某个月的 30 来行数据原样取出来。走(solar_year, solar_month)联合索引返回结果天然按主键有序不需要ORDER BY也能保证顺序但为了语义明确还是写上。-- 拉取某个公历月的整月日历直接给前端渲染 SELECT solar_date, week_day, lunar_month_cn, lunar_day_cn, is_leap_month, solar_term, day_type, holiday_name, is_workday FROM calendar_day WHERE solar_year 2025 AND solar_month 10 ORDER BY solar_date;返回的day_type让前端可以直接映射样式0 和 1 走普通样式2 标红加「休」3 标灰加「班」。is_workday用来算当月应出勤天数COUNT(*) WHERE is_workday 1一行就出来了比在应用层遍历日历数组快得多尤其是批量算几十个人考勤的时候。4.2 公历转农历与农历转公历闰月必须显式指定公历转农历是纯主键点查一条就够。-- 公历 → 农历主键点查顺带把节气也带出来 SELECT solar_date, lunar_year, lunar_month_cn, lunar_day_cn, is_leap_month, solar_term, week_day FROM calendar_day WHERE solar_date 2025-07-25;反过来农历转公历就必须小心了。这条查询如果不带is_leap_month遇到闰月会返回两行——平月和闰月的月序号、日序号完全一样只有闰月标记不同。我见过有同事在这里踩坑测试环境数据少没暴露上线后用户反馈「生日提醒提前了一个月」。-- 农历 → 公历is_leap_month 必须显式传否则闰月命中两行 SELECT solar_date, is_leap_month, lunar_month_cn, lunar_day_cn FROM calendar_day WHERE lunar_year 2025 AND lunar_month 6 AND lunar_day 1 AND is_leap_month 1; -- 0 查平月1 查闰月业务上必须二选一 -- 兼容写法查「这个农历年六月初一」把平月和闰月都列出来由应用层决定取哪个 SELECT solar_date, is_leap_month FROM calendar_day WHERE lunar_year 2025 AND lunar_month 6 AND lunar_day 1 ORDER BY is_leap_month;生日提醒这类业务通常的约定是「闰月出生的人平年按平月过」那就固定传is_leap_month 0。这个规则最好写在接口文档里否则不同的调用方给不同答案数据对不上又要查半天。4.3 工作日加减与节气提醒「合同签订后 10 个工作日内」这种需求用这份库算起来比写代码循环靠谱得多。MySQL 8.0 用窗口函数一条 SQL 直接定位。-- 起点之后第 N 个工作日MySQL 8.0 窗口函数版 SET start : 2025-01-01; SET n : 10; SELECT solar_date FROM ( SELECT solar_date, SUM(is_workday) OVER (ORDER BY solar_date) AS seq FROM calendar_day WHERE solar_date start ) t WHERE t.seq n 1 -- 起点当天若是工作日 seq1所以「之后第 N 个」对应 seqN1 ORDER BY solar_date LIMIT 1;这里seq是从起点开始累加is_workday得到的序号调休上班日is_workday 1会被正常计入法定假日is_workday 0自动跳过这正是我们想要的语义。参数n就是「之后的第几个工作日」改成 5 就是五个工作日内改成 30 就是三十个工作日。如果数据库是 5.7没有窗口函数得用用户变量凑。这种写法在 8.0 里虽然还能跑但会报 deprecation warning因为 MySQL 不保证SELECT中变量赋值的求值顺序理论上结果是不确定的。-- 5.7 兼容写法用户变量累加依赖 ORDER BY 的执行顺序 SELECT solar_date FROM ( SELECT solar_date, seq : seq is_workday AS seq FROM calendar_day CROSS JOIN (SELECT seq : 0) init WHERE solar_date start ORDER BY solar_date ) t WHERE t.seq n 1 LIMIT 1;最后两条是偏展示型的。查某年全部节气走idx_term返回 24 行查未来 7 天内有没有节气用来做提醒推送。-- 某年全部 24 节气 SELECT solar_date, solar_term, week_day FROM calendar_day WHERE solar_term IS NOT NULL AND solar_year 2025 ORDER BY solar_date; -- 未来 7 天内的节气提醒用于推送任务 SELECT solar_date, solar_term, lunar_month_cn, lunar_day_cn FROM calendar_day WHERE solar_term IS NOT NULL AND solar_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);如果还想知道某个节假日区间里夹了哪几天调休上班直接按holiday_name过滤再按日期排序即可idx_type_date在这里帮不上忙但数据量太小全表扫也就几十毫秒。5. 避坑排查导入乱码、闰月重复命中、调休数据缺失的 5 个坑这一章记录的是实际运行中遇到过的具体问题。每一条都按「现象 → 原因 → 解决」写遇到类似症状可以直接对号入座。5.1 导入与连接层的坑坑一导完发现中文月名全是问号。现象是SELECT lunar_month_cn FROM calendar_day LIMIT 5返回一堆????或者干脆是空字符串。原因是字符集在三个地方可能不一致数据文件本身的编码、客户端连接字符集、表的默认字符集。只要有一个环节不是 utf8mb4中文就会在那一层被截断而且往往不报错只是静默变成问号。解决办法是三层都对齐——文件另存为 UTF-8 无 BOM命令行加--default-character-setutf8mb4应用连接串里写charsetutf8mb4。已经导坏了的话先TRUNCATE TABLE再重导别指望UPDATE能修回来因为原始字节已经丢了。坑二CSV 导入后节气字段多出一堆空值行。现象是用WHERE solar_term IS NOT NULL查出来的行数远大于 24 乘以年数。原因是 CSV 里没有节气的那一行是空字段LOAD DATA把它导成了空字符串而不是 NULL而是非 NULL 值。解决就是导完补一条UPDATE calendar_day SET solar_term NULL WHERE solar_term ;。这条语句建议固定写进导入脚本别靠人记。5.2 历法语义的坑坑三农历生日查询闰月出生的人一年被提醒两次。现象是用户反馈生日祝福发了两次或者日期提前了整整一个月。原因是查询里只写了lunar_month 6 AND lunar_day 1没有限定is_leap_month而闰月那一年的平月和闰月这两天数字完全相同两条都命中了。解决是业务侧必须先明确规则闰月生日按平月过就固定传is_leap_month 0如果确实要按闰月过就传 1并且在非闰月年份要有降级到平月的逻辑。这个规则写进接口文档比在代码里写注释管用。坑四按公历年统计春节数量永远对不上。现象是SELECT solar_year, COUNT(*) FROM calendar_day WHERE lunar_month 1 AND lunar_day 1 GROUP BY solar_year跑出来有的年份是 1有的年份是 0 甚至 2。原因是农历年跨公历年春节可能落在 1 月也可能落在 2 月而一个公历年里既可能包含当年春节、也可能包含下一年春节的一部分。正确做法是按lunar_year分组而不是按solar_year。-- 正确按农历年分组每个农历年有且只有一个正月初一 SELECT lunar_year, solar_date AS spring_festival FROM calendar_day WHERE lunar_month 1 AND lunar_day 1 AND is_leap_month 0 ORDER BY lunar_year;坑五未来年份的班休判断出错。现象是查 2027 年的考勤所有周六周日都被算成了上班日或者反过来调休上班日没被识别出来。原因很直白每年的放假安排是后公布的这份库覆盖到 2100 年但节假日配置只到已知的那一年往后的年份day_type只有 0 和 1没有 2 和 3。解决办法是在应用层留退路is_workday为空或者所在年份超出假日配置范围时按星期几做近似判断。这里还有个额外的小坑MySQL 的DAYOFWEEK()是 1周日、WEEKDAY()是 0周一跟表里「1周一…7周日」的约定都对不上要用WEEKDAY(dt) 1才等价。-- 退路逻辑优先用表里的判定缺失时按周一到周五近似 SELECT d.solar_date, d.week_day, d.holiday_name, CASE WHEN d.day_type 3 THEN 1 -- 调休上班撞周末也算上班 WHEN d.day_type IN (1,2) THEN 0 -- 周末与法定假日 WHEN d.day_type 0 THEN 1 -- 普通工作日 ELSE IF(WEEKDAY(d.solar_date) 5, 1, 0) -- 没有假日数据的年份按周一到周五 END AS is_workday_final FROM calendar_day d WHERE d.solar_year 2027 AND d.solar_month 5 ORDER BY d.solar_date;6. 二次开发把静态日历表改造成排班引擎的 3 个改造点直接让业务代码拼 SQL 查这张表短时间没问题一旦调用量上来就会难受。我在实际项目里做了三处改造成本都不高收益很明显。第一处是加视图把判定逻辑固定下来。与其在十几个接口里各写一遍 CASE不如在库里建一个v_workday把「最终上不上班」这个判断定死在一处业务侧只SELECT is_workday_final将来规则要改只改视图定义。CREATE OR REPLACE VIEW v_workday AS SELECT d.solar_date, d.week_day, d.day_type, d.holiday_name, CASE WHEN d.day_type 3 THEN 1 WHEN d.day_type IN (1,2) THEN 0 ELSE 1 END AS is_workday_final FROM calendar_day d;第二处是加一张人工覆盖表。节假日安排总会有临时调整或者运营侧的特殊需求直接改主表数据既不可追溯也不敢回滚。建一张holiday_override主键就是日期查询时LEFT JOIN一下COALESCE取覆盖值优先。这样运营改错了删一行就恢复比重新导数据安全得多。CREATE TABLE holiday_override ( solar_date DATE NOT NULL, is_workday TINYINT(1) NOT NULL, holiday_name VARCHAR(16) DEFAULT NULL, operator VARCHAR(32) DEFAULT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (solar_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT节假日人工覆盖优先级高于主表; -- 合并查询覆盖值优先没有则回落到主表 SELECT d.solar_date, COALESCE(o.holiday_name, d.holiday_name) AS holiday_name, COALESCE(o.is_workday, d.is_workday) AS is_workday FROM calendar_day d LEFT JOIN holiday_override o ON o.solar_date d.solar_date WHERE d.solar_year 2026 AND d.solar_month 2 ORDER BY d.solar_date;第三处是缓存预热。一年 365 行数据一次性拉进内存字典比每次都查库划算得多尤其是排班计算这种要在一个请求里反复比对几十本文还有配套的精品资源点击获取