MySQL农历数据库表结构设计与批量导入实践-北京尧图网络

简介这份面向后端开发与业务系统的MySQL农历数据库覆盖1970—2100年共131年数据包含农历日期、闰月、24节气、星期及法定假日等可解决农历转换结果不一致、节假日信息不全的问题。压缩包仅2.56MB共3个文件其中2个SQL脚本分别存放农历主表与法定假日表另有1个XML文件提供连表查询示例便于二次开发。农历主表可查询每日农历、闰月、节气和星期假日表除国家法定假日的上班/休息安排外还支持自定义班休日期两表连表即可获得任意日期的工作属性例如快速判断某天是否为工作日、节假日调休安排等。这套字典表特别适合排班、考勤、节日提醒、薪资计算等业务系统作为公共数据源接入即可使用且无需额外依赖。目前已有1453人学习下载结构清晰可作为MySQL农历数据方案选型的直接参考。1. 一张覆盖两个世纪的日期表为什么要把农历库直接写进 MySQL做排班和考勤系统的同学大概都经历过这种尴尬库里存着公历日期业务却要按农历生日、二十四节气、法定假日和周几来算「这个月到底哪几天要上班」。靠 Excel 翻日历效率太低靠在线历法接口又怕它限流和延迟而且一到调休补班这种政策数据接口往往还没有你手里更新得及时。所谓 mysql 农历数据库 1970-2100就是把这 131 年的公历农历对照、闰月标记、二十四节气、法定假日、星期和班休区分一次性落到 MySQL 里让所有日期判断都在 SQL 层完成。这篇文章面向做考勤、排班、报表统计、农历生日提醒和日历类 App 数据层的开发者目标是讲清楚表结构怎么拆、数据怎么导、查询怎么写以及哪些地方最容易翻车。2. 拆解数据项与表结构闰月标记、节气表、节假日表怎么分2.1 数据项要先分类天文数据与政策数据不能混用一个表农历不是纯阴历而是阴阳合历。月份的长短跟随月相周期年份长度又必须跟随回归年走两者没法整除所以靠闰月来调和二十四节气本身就是农历里的阳历成分按太阳黄经划分。这个本质决定了闰月和节气属于天文历法数据只要历法规则不变1970 到 2100 年的数据可以一次性生成、长期不动。法定假日和调休则完全相反它属于政策数据。哪几天放假、哪几个周末补班是每年由相关部门发布安排公告确定的不是靠公式能提前算出来的。两类数据的来源、更新频率和稳定性完全不同混在一张表里会导致每次政策更新都要去刷同一张全量大表刷错一个区间就把节气数据覆盖了。常见做法是拆成三张基础表日期主表、节气表、节假日规则表再在查询层或者物化层把三者聚合。政策数据按年维护历法数据基本只导入一次这个边界从一开始就要划清楚。另外班休区分不是简单地把「周六日法定假日」加起来。法定假日可能落在工作日调休又可能把周末变成上班日这两种情况叠加之后才能得到某一天最终是上班还是休息。把「自然周末」「法定假日」「调休补班」三个维度分开存储最后再合并成最终状态比直接做一张大宽表要安全得多。2.2 日期主表存「农历星期」建表 SQL 与字段说明日期主表是整库的核心一个公历日期一行行上带农历年、农历月、农历日、闰月标记、星期名和节气名冗余字段。我用solar_date直接做主键因为日历表天然按日期唯一不需要额外的自增 id。农历年最小 1970、最大 2100用SMALLINT足够节气名和节假日名都用VARCHAR(8)到VARCHAR(30)中文按utf8mb4存储。建表脚本如下CREATE TABLE lunar_day ( solar_date DATE NOT NULL PRIMARY KEY COMMENT 公历日期主键, lunar_year SMALLINT NOT NULL COMMENT 农历年如 2025, 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非闰月, lunar_month_name VARCHAR(8) NOT NULL COMMENT 显示用农历月名如四月/闰四月, lunar_day_name VARCHAR(8) NOT NULL COMMENT 显示用农历日名如初十/十五, weekday_name VARCHAR(4) NOT NULL COMMENT 冗余星期名星期一/星期日, term_name VARCHAR(8) NULL COMMENT 节气名非节气日则为 NULL, KEY idx_lunar_year_month (lunar_year, is_leap_month, lunar_month) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT公历农历对照日期主表;字段逻辑说明is_leap_month是整个表里最容易用错的字段。遇到闰四月年份时农历四月和闰四月的lunar_month都是 4唯一区别就是is_leap_month的 0 和 1。所有按农历查询业务都必须带这个条件否则会出现一个日期同时命中两条记录。weekday_name属于冗余存储虽然DATE类型能用WEEKDAY()直接算但排班报表高频按星期过滤冗余后可以直接走索引也方便后续把整表导出给 BI 工具。term_name在日期主表里只做展示冗余严谨的节气查询还是走独立的节气表。2.3 法定假日、调休单独建表为什么我不做成大宽表法定假日有一个特点官方发布的是「区间」比如某节日从 10 月 1 日放到 10 月 7 日中间不区分周几。调休补班是另一个区间比如某个周末要从休息改成上班。如果把每一天都拆成一行存到一张大宽表里维护者看到的是几百条没有语义的碎片记录而且政策一调整就要重刷一大批行。我的做法是建一张区间语义的节假日规则表一行表示一个连续区间BETWEEN关联就能展开到天。CREATE TABLE holiday_rule ( rule_id INT PRIMARY KEY AUTO_INCREMENT, rule_year SMALLINT NOT NULL COMMENT 政策发布所属年份, holiday_name VARCHAR(30) NOT NULL COMMENT 节假日名称或调休说明, start_date DATE NOT NULL COMMENT 区间开始日期, end_date DATE NOT NULL COMMENT 区间结束日期可等于开始日期, is_rest TINYINT NOT NULL DEFAULT 1 COMMENT 1该区间休息0该区间调休上班, remark VARCHAR(100) NULL COMMENT 备注如国庆节、春节前补班, UNIQUE KEY uk_rule_year_name (rule_year, holiday_name, start_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT法定假日与调休补班规则表;这里is_rest0的行专门用来表达调休补班比如某个周六本来是周末但政策要求上班就插入一行起止日期都是那一天的记录。注意唯一键设成rule_year holiday_name start_date这样同一个节日同一年可以多次导入而不会产生重复行同时也方便做幂等更新。不要把这个表和日期主表混在一起因为日期主表是天文数据几乎不变而节假日规则表是政策数据每年都要刷混在一起会让全量导入和增量更新的边界变得非常模糊。3. 把数据装进去数据来源、批量导入与增量更新的落地流程3.1 农历、节气、节假日数据分别从哪来农历和节气属于天文历法数据常见做法是基于现行农历编算规则的开源日历库一次性生成 CSV再做全量导入。我一般会先拿一个已经验证过的数据源做交叉校验重点看三件事1970 到 2100 全区间是否完整闰月年份和闰月序号是否符现行历法规则节气日期是否与天文历书口径一致。这里提醒一句节气日期每年都浮动清明能落在 4 月 4 日到 6 日之间春节最早 1 月 21 日、最晚 2 月 21 日只有天文口径的数据才可信近似公式算出来的只能叫「大概日期」不能入库。法定假日和调休没有算法可以推算只能每年等官方安排公告发布后人工整理成结构化 CSV。数据量很小一个年份不过几十行但必须保证区间边界准确。做这一步时我习惯把「原始公告的段落文字」也留一份在备注字段里方便后期追溯某条调休记录到底依据的是什么安排。三个数据源的更新频率天然不同农历表和节气表灌一次就不再动节假日规则表每年底或次年初更新一次最终用于对外查询的日历事实表则要跟着节假日规则表同步重建。把这个频率差异想清楚就不会写出「每年全量重刷所有表」的脚本。3.2 用 Python CSV 批量写入 MySQL导入脚本与参数说明CSV 文件建议按这样的列顺序组织solar_date,lunar_year,lunar_month,lunar_day,is_leap_month,lunar_month_name,lunar_day_name,weekday_name,term_name。导入脚本用executemany批量写入而不是逐条execute否则 131 年的数据要跑几万次网络往返慢得没法接受。# -*- coding: utf-8 -*- import csv import mysql.connector def load_lunar_csv(path): rows [] with open(path, r, encodingutf-8) as f: reader csv.DictReader(f) for item in reader: rows.append(( item[solar_date], int(item[lunar_year]), int(item[lunar_month]), int(item[lunar_day]), 1 if item[is_leap_month] 1 else 0, item[lunar_month_name], item[lunar_day_name], item[weekday_name], item.get(term_name) or None, )) return rows rows load_lunar_csv(lunar_1970_2100.csv) conn mysql.connector.connect( host127.0.0.1, port3306, userroot, passwordyour_password, databasecalendar_db, charsetutf8mb4, ) cursor conn.cursor() upsert_sql INSERT INTO lunar_day (solar_date, lunar_year, lunar_month, lunar_day, is_leap_month, lunar_month_name, lunar_day_name, weekday_name, term_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE lunar_year VALUES(lunar_year), lunar_month VALUES(lunar_month), lunar_day VALUES(lunar_day), is_leap_month VALUES(is_leap_month), term_name VALUES(term_name) cursor.executemany(upsert_sql, rows) conn.commit() print(imported rows:, cursor.rowcount) cursor.close() conn.close()逻辑说明脚本先读 CSV 拼成元组列表再交给executemany一次性发给 MySQL。ON DUPLICATE KEY UPDATE保证遇到已存在的solar_date时更新农历字段和节气名而不是报错中断。charsetutf8mb4是必须写的参数否则中文月名和节气名大概率乱码。参数说明executemany在数据量大时可以分批提交比如每 5000 行commit一次避免单事务过大拖慢 InnoDB 的 redo 日志刷盘cursor.rowcount在 upsert 场景下返回的是「插入更新」的总行数不能用来判断哪些是新增哪些是更新。另外要注意VALUES()函数在 MySQL 8.0.20 起标记为废弃如果数据库版本是 8.0.20 以上建议改成AS new ON DUPLICATE KEY UPDATE lunar_year new.lunar_year的新语法如果还要兼容 5.7就继续用VALUES()不要混用。3.3 增量更新节假日与班休数据幂等导入与覆盖策略节假日规则表的数据量小但每年都要更新而且官方公告发布后偶尔会有细节调整。增量更新的核心是幂等同一行数据可以反复导入结果保持一致而不是越导越乱。我的习惯是每年公告出来后把新一年的节假日和调休整理成另一个 CSV列结构为rule_year,holiday_name,start_date,end_date,is_rest,remark然后跑一个和农历导入类似的脚本。唯一键uk_rule_year_name保证了重复导入不会产生重复行而ON DUPLICATE KEY UPDATE会覆盖旧的起止日期和休息标记。rule_rows [ (2025, 国庆节, 2025-10-01, 2025-10-07, 1, 国庆节放假), (2025, 国庆节调休补班, 2025-09-28, 2025-09-28, 0, 周末补班), ] rule_sql INSERT INTO holiday_rule (rule_year, holiday_name, start_date, end_date, is_rest, remark) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE start_date VALUES(start_date), end_date VALUES(end_date), is_rest VALUES(is_rest), remark VALUES(remark) cursor.executemany(rule_sql, rule_rows) conn.commit()这里有个细节值得注意不能用INSERT IGNORE。如果政策调整导致同一节日同一起始日期的is_rest从 1 变成 0INSERT IGNORE会静默跳过旧数据保留查询结果就错了。只有ON DUPLICATE KEY UPDATE才能把变化覆盖进去。日期主表和节气表不需要跟着做增量更新它们是一次性导入的基线数据。如果需要修正某条农历数据直接对lunar_day执行定点UPDATE即可不要整个表重刷。增量更新只面向holiday_rule和后面要讲到的日历事实表。4. 四个高频查询闰月、节气、法定假日与班休区分怎么写 SQL4.1 农历生日与闰月多条件过滤保证不串月最典型的翻车案例是农历生日。一个用户农历四月十五出生遇到闰四月年份时表里会同时存在「四月十五」和「闰四月十五」两条记录。如果查询只写lunar_month4 AND lunar_day15就会在同一年返回两个公历日期生日提醒发两次线上立刻被投诉。正确写法必须带is_leap_month条件SELECT solar_date FROM lunar_day WHERE lunar_year 2025 AND lunar_month 8 AND lunar_day 15 AND is_leap_month 0;查「某一年存在几个闰月」也有固定写法SELECT lunar_year, GROUP_CONCAT(lunar_month ORDER BY lunar_month) AS leap_months FROM lunar_day WHERE is_leap_month 1 GROUP BY lunar_year ORDER BY lunar_year;逻辑说明农历置闰不是每年都有一个闰月年份一般只闰一个月。上面这组 SQL 直接按is_leap_month1聚合就能列出全部闰月年份和对应的闰月序号用于校验数据源是否完整。4.2 二十四节气按节气名查询别用公历日期硬猜节气数据单独存在solar_term表里一节气一行。查询场景通常是「明年的清明是哪天」「近十年的冬至分布」核心写法是按term_name过滤而不是按固定公历日期猜。SELECT solar_year, DATE_FORMAT(term_date, %Y-%m-%d) AS date_txt FROM solar_term WHERE term_name 清明 AND solar_year BETWEEN 2025 AND 2035 ORDER BY solar_year;逻辑说明节气在公历上的日期是浮动的清明在 4 月 4 日到 6 日之间移动冬至在 12 月 21 日到 23 日之间移动。用固定日期去匹配必然有年份会错位。数据入库时已经按天文口径生成所以查询层不需要任何算法直接做等值匹配就能拿到准确日期。4.3 法定假日和周末合并统计一个月实际休息几天统计「某个月实际休息几天」不能只数周六日也不能只数法定假日区间因为两者有重叠。正确的做法是先分别统计自然周末天数、落在工作日上的法定假日天数再减去调休补班造成的周末上班天数。先看基础统计以 2025 年 10 月为例WITH oct_days AS ( SELECT solar_date, weekday_name FROM lunar_day WHERE solar_date BETWEEN 2025-10-01 AND 2025-10-31 ) SELECT COUNT(*) AS total_days, SUM(CASE WHEN weekday_name IN (星期六, 星期日) THEN 1 ELSE 0 END) AS natural_weekend_days, SUM(CASE WHEN h.holiday_name IS NOT NULL AND weekday_name NOT IN (星期六, 星期日) THEN 1 ELSE 0 END) AS holiday_weekdays FROM oct_days d LEFT JOIN holiday_rule h ON d.solar_date BETWEEN h.start_date AND h.end_date AND h.is_rest 1;逻辑说明natural_weekend_days是自然周六日数量holiday_weekdays是法定假日落在工作日上的天数。两者相加是基础休息日但还没考虑调休补班。如果一个周末被调成上班日自然周末里就要扣掉对应的天数反之如果一个工作日被调成休息日is_rest0的记录会参与修正。这样做统计的缺点是 SQL 越写越复杂而且每个统计需求都要重复写 CASE WHEN。更省事的方案是直接建一张日历事实表把每一天的最终状态提前算好见下一节。4.4 汇总成日历事实表班休区分最终落到一个字段班休区分的最终目标是回答「某一天最终是上班还是休息」。与其每次查询时临时算不如把结果物化成一张calendar_fact表。这张表以lunar_day为基础LEFT JOINholiday_rule和调休标记最终生成is_workday字段排班、考勤、报表都直接查这个字段。CREATE TABLE calendar_fact AS SELECT d.solar_date, d.lunar_month_name, d.lunar_day_name, d.weekday_name, d.term_name, d.is_leap_month, h.holiday_name, CASE WHEN d.weekday_name IN (星期六, 星期日) THEN 1 ELSE 0 END AS is_natural_weekend, CASE WHEN h.holiday_name IS NOT NULL THEN 1 ELSE 0 END AS is_legal_holiday FROM lunar_day d LEFT JOIN holiday_rule h ON d.solar_date BETWEEN h.start_date AND h.end_date AND h.is_rest 1; ALTER TABLE calendar_fact ADD COLUMN is_workday TINYINT NOT NULL DEFAULT 0 COMMENT 最终是否上班1上班, ADD COLUMN is_adjusted TINYINT NOT NULL DEFAULT 0 COMMENT 是否被调休过1是;生成完基础表后需要用两条UPDATE把调休结果叠加上去把is_rest0对应日期标记成上班日并置is_adjusted1如果某条自然工作日因为放假变成休息日同样由调休表的is_rest1区间覆盖。最终统计一个月的实际上班天数就变成一条简单的聚合SELECT SUM(is_workday) AS workday_count FROM calendar_fact WHERE solar_date BETWEEN 2025-10-01 AND 2025-10-31;逻辑说明calendar_fact是一次物化操作它把「自然周末」「法定假日」「调休补班」三层逻辑折叠成一个字段。代价是每年节假日规则更新后必须重建这张表但重建一次不过万行级别MySQL 执行时间在秒级以内完全可接受。这张表的价值在于把复杂判断收敛到一处应用层写排班逻辑时只需要看一个字段。5. 落地最容易翻车的 5 个问题边界年、闰月和调休政策5.1 节气日期浮动硬编码 4 月 5 日必然翻车现象有人觉得清明就是 4 月 5 日直接在业务代码里写死。结果某年 4 月 4 日才是清明节气提醒提前了一天统计口径全乱。原因节气是按太阳黄经计算的一个节气持续约 15 天日期在公历上逐年浮动不是固定在某一天。同一个节气在不同年份可能差两到三天。解决节气日期只从solar_term表读取入库时用天文口径数据生成。应用层不要写任何「大概日期」的兜底逻辑宁可查不到也不要猜。5.2 闰月年的「四月」和「闰四月」会串数据现象农历四月十五生日的人在闰四月年份收到两次生日提醒或者查询农历节日时返回了两个公历区间。原因lunar_month4时表里同时有「四月」和「闰四月」两组数据没过滤is_leap_month导致结果集翻倍。解决所有农历查询统一加上is_leap_month条件。业务要表达「出生在农历四月」时语义上默认是非闰月必须显式写is_leap_month0只有在处理「当年闰月」这个明确意图时才放开过滤。5.3 未来的调休数据根本不存在班休表只能按年维护现象用户想查 2035 年的班休安排库里查不到于是有人写代码「按最近几年规律推算」结果推算出来的调休日期和真正发布的对不上。原因调休是政策数据不是天文数据。官方只发布已确认年份的安排未来年份是未决策状态任何推算都是猜测。解决设计上一开始就区分「历法数据覆盖 1970-2100」和「政策数据按年覆盖」。没有政策数据的年份calendar_fact只按自然周末生成默认状态等官方公告发布后再增量更新对应年份。给用户展示时明确标注「该年度调休安排未发布」而不是返回一个错误结果。5.4 导入时中文乱码与重复键编码设置与幂等写法现象导入后lunar_month_name显示成???或者同一个日期重复执行导入脚本后出现主键冲突直接报错。原因连接参数没带charsetutf8mb4或者表字符集建成了latin1。重复键报错是因为用了普通INSERT没有处理主键冲突。解决建库建表统一utf8mb4连接串显式指定字符集导入用ON DUPLICATE KEY UPDATE而不是INSERT IGNORE。注意 MySQL 8.0.20 以上推荐用别名语法替代VALUES()老版本继续用VALUES()保持兼容。5.5 1970 和 2100 两个边界年数据两端最容易缺现象系统上线后某天发现查 1970 年 1 月 1 日之前的农历数据返回空或者 2100 年 12 月 31 日之后的数据莫名其妙出现断档。原因数据源生成时可能只覆盖到 2100 年 12 月 30 日或者起始日期从 1970 年 1 月 2 日开始导入流程没有做边界校验应用层也没有做日期范围拦截。解决导入完成后立刻查两端的记录确认真实覆盖范围。应用层读取前统一加一层范围判断超出 1970-01-01 到 2100-12-31 的请求直接返回明确的越界提示。这个判断在业务代码里是必须的不能依赖数据库自己去兜底。6. 验证数据质量把日历表接进排班与统计系统6.1 三个 SQL 自检确认数据没缺、没串、没重复数据导完别急着上线先跑三组自检 SQL。第一组查每年节气数量是否为 24第二组查闰月年份分布第三组查日期主表是否有重复主键。三组都通过基本可以判断全量导入没有重大问题。SELECT solar_year, COUNT(*) AS term_cnt FROM solar_term GROUP BY solar_year HAVING COUNT(*) 24;SELECT lunar_year, GROUP_CONCAT(lunar_month) AS leap_months FROM lunar_day WHERE is_leap_month 1 GROUP BY lunar_year ORDER BY lunar_year;SELECT solar_date, COUNT(*) AS c FROM lunar_day GROUP BY solar_date HAVING COUNT(*) 1;查询结果说明第一组如果查出某年节气数不是 24说明节气数据缺失或重复不能上线第二组可以人工抽查几个已知闰月年份比如 2020 年闰四月如果和已知历法对不上说明数据源有偏差第三组理论上永远为空因为主键已经唯一但批量导入时如果用了非主键表结构这个检查就是最后一道防线。6.2 接进排班和统计一张日历事实表能少写几百行应用代码有了calendar_fact排班系统的核心逻辑就变成两个字段的查询is_workday决定是否排班holiday_name决定是否生成节假日提醒。员工生日提醒则直接反查农历月日用lunar_month lunar_day is_leap_month三个条件就能表达「按农历生日」这种需求不再需要应用层做公农历转换。月报统计也简单了。以前要用多个 CTE 去拼自然周末和调休补班现在一个SUM(is_workday)就能算出一个月的实际上班天数。如果报表还要按星期分布weekday_name字段已经冗余好直接GROUP BY即可。这一步节省的不只是 SQL 长度更是团队成员理解业务规则的成本。6.3 记得留一张元数据表否则三年后你会后悔最后说一个可能后悔的习惯。我经手过的日期类数据表基本都会附一张meta_info表记录数据版本、生成日期、导入日期、数据源类型、最后更新时间。这个做法救过我一次某年发现调休数据对不上翻元数据表才发现当年导入用的是旧版本公告重新导一次就解决了。没有这张表面对一堆日期数据只能抓瞎。所以建库时顺手建一张元数据表哪怕只有四五个字段长期看都值得。这套农历库的落地过程总结下来就是把天文数据、政策数据分开存再用事实表把复杂规则折叠成一个字段。日期维度的问题从此不再是黑匣子。希望帮到你。本文还有配套的精品资源点击获取

MySQL农历数据库表结构设计与批量导入实践

相关推荐

WPTools v6.29.1：WordPress静态安全扫描CLI工具详解

伴随灵敏度分析驱动肿瘤时空放疗优化：Matlab实现全解析

双目视觉工程实战：从标定到深度图生成与点云重建的全流程解析

更多资讯

微信小程序页面路径配置的底层原理与避坑指南

红外船只检测数据集：8402张VOC+YOLO双格式夜海目标数据

片上温度传感器设计：BJT前端与Cyclic+ΣΔ融合ADC架构解析

2026 AI 时代，前端工程师的“危”与“机”：从代码工人到智能协作者，TaoToken 统一 Key 接入实战

一文带你入门DeepAgents：用TaoToken统一Key跑通FunctionCall与SubAgent

SQL Server 2000 SP4个人版安装与运维：从环境准备到数据迁移

今日更新

PHP htmlentities()函数用法讲解

AI Agent工程实战：从七要素到七个决策点的系统设计指南

AI入局芯片设计验证：验证收敛50倍加速的真相与应对

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？