简介面向需要处理公历、农历日期的 MySQL 开发者这份资源提供了可直接导入使用的日历数据表覆盖 1900 年至 2100 年共 200 年的完整日期记录。资源包中两个 SQL 文件分别承担不同功能一个用于建立农历表包含农历年份、月份、日期及中文名称、节日标记等字段并预填了对应数据另一个用于建立公历表除日期外还提供星期、周末与公历节日判断字段。两张表通过公历日期字段即可关联查询某天的农历信息与节假日状态只需一条 JOIN能显著简化日期换算、节假日排班、财务结算等业务场景中的 SQL 逻辑。整体资源约 1.07MB仅含两个 .sql 文件导入成本低适合中小型项目快速集成。目前已有 297 人学习下载对于需要兼顾公历农历日历能力的数据库设计而言是一份实用且格式清晰的参考实现。 做业务系统这些年我越来越觉得日历数据是个“平时想不起、用到就抓瞎”的东西。很多项目跑着跑着就冒出来一个需求订单要按农历日期统计、会员要查阴历生日、排班系统要显示节气、财务要核对农历节假日。你翻遍代码仓库发现要么是网上扒的年份不全的转换函数要么是同事留的一个不知道还准不准的旧表。与其每次临时抱佛脚不如一次性在MySQL里建一张覆盖1900到2100年的日历数据表公历农历一起存两百年跨度足够绝大多数业务用到底。这篇文章就把我这套日历表的完整设计思路、建表SQL、数据生成方法、常用查询和踩过的坑全部摊开讲。用的是MySQL 8.0环境但文里的表结构和思路在5.7以及MariaDB上一样能用。适合正在做报表、排班、订单、人事系统的开发同学也适合想给项目打牢数据地基、不想再让日期转换反复折腾的人。1. 设计思路为什么要把日历存进数据库在动手建表之前先说清楚一个核心问题日期转换用代码写个函数不就行了为什么非要在数据库里存一张表确实农历转公历、公历转农历都有现成的算法和JavaScript库但放到真实业务里问题就来了。第一算法函数通常是单条数据转换你一旦要做范围查询比如“把2024年所有订单按农历日期分组统计”用函数逐行算性能就是灾难几万行数据直接卡到怀疑人生。第二农历数据有很强的地域和版本差异有的地方过的是农历腊月二十九有的算法推出来是三十网上抄来的代码往往来路不明你敢拿生产数据去赌吗。第三还有个很现实的场景报表、排班、考勤这类功能需要一次性拿一整年的日历数据存成表就能一条SQL拉出来和订单表做关联也顺滑。把公历农历做成一张固定的日历表本质上是把“算日期”变成“查数据”。查询走索引结果稳定可复现还兼容任何语言——前端、后端、报表工具都只认数据库谁需要谁去查不用每个项目都重写一套转换逻辑。这套设计在数据仓库里叫做“日期维度表”在业务系统里其实同样适用。1.1 公历与农历混存的表结构设计表结构设计的核心矛盾在于公历是阳历系统简单规律农历是阴阳合历有闰月、有大月小月、每年天数还不一样。想要一张表同时覆盖最稳妥的方式是“以公历日期为主键农历信息作为附属字段”。以公历为主键有讲究。公历日期连续且唯一天然适合做主键和索引农历数据则作为冗余字段存进同一行里。这样设计按公历查询、按农历查询、按年份统计都是单表操作完全不需要关联两张表。如果你非要拆成公历表和农历表两张那查询时还得join不仅麻烦索引优势也发挥不出来。1.2 日期范围与数据来源的确定我选的是1900年到2100年这个区间。1900年是公历和农历对应关系在计算上相对公认的起点很多日历转换库都是从这一年开始支持2100年则是为了给未来业务留足空间两个世纪的跨度对绝大多数商业系统来说已经完全够用。这个范围内一共是73410天左右算上2100年12月31日数据量很小对MySQL来说一张表完全没压力。数据来源方面不建议自己写算法推算一方面容易出错另一方面工作量也大。比较靠谱的来源有几个一是现成的开源项目里的农历数据表比如很多日历组件库里都有JSON或SQL格式的数据二是Python的lunar_python这类库直接用代码生成整张表的插入语句三是网上整理好的CSV文件清洗后导入。数据拿到之后一定要抽样验证找一个公历闰年和农历闰月交替的年份重点核对比如2023年有闰二月、2025年也有闰六月这些年份最容易出错。2. 表结构详解与字段设计要点建表听起来简单但字段怎么定、类型怎么选、索引怎么加直接影响后续查询好不好写、性能稳不稳。下面是我反复调整后最终在项目里落地的版本。CREATE TABLE calendar_date ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增主键, date_solar date NOT NULL COMMENT 公历日期, date_solar_year smallint NOT NULL COMMENT 公历年份, date_solar_month tinyint NOT NULL COMMENT 公历月份, date_solar_day tinyint NOT NULL COMMENT 公历日, weekday tinyint NOT NULL COMMENT 星期几1-71代表周一, weekday_cn varchar(4) NOT NULL COMMENT 中文星期, date_lunar_year smallint NOT NULL COMMENT 农历年份, date_lunar_month tinyint NOT NULL COMMENT 农历月份1-12闰月用负数标记, date_lunar_day tinyint NOT NULL COMMENT 农历日1-30, date_lunar_month_cn varchar(8) NOT NULL COMMENT 农历月中文含闰月, date_lunar_day_cn varchar(4) NOT NULL COMMENT 农历日中文, is_leap_month tinyint NOT NULL DEFAULT 0 COMMENT 是否闰月1为闰月, lunar_festival varchar(32) DEFAULT NULL COMMENT 农历节日, solar_terms varchar(16) DEFAULT NULL COMMENT 二十四节气, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_date_solar (date_solar), KEY idx_lunar_month (date_lunar_year, date_lunar_month), KEY idx_solar_terms (solar_terms) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT公历农历日历数据表(1900-2100);2.1 核心字段说明公历部分怎么设计date_solar用date类型这是整个表的灵魂字段。按它建唯一索引既能保证数据不重复又能让所有按公历日期的查询走索引速度极快。date_solar_year和date_solar_month这两个冗余字段很多人觉得多余但实际做“按月统计订单”“按年汇总报表”的时候直接在where里写date_solar_year 2024 and date_solar_month 11比写date_solar between 2024-11-01 and 2024-11-30要直观得多索引也依然能用。weekday我用1-7代表周一到周日而不是MySQL默认的0-6。原因很简单业务方和产品经理习惯的就是周一代表一周开始如果存0-6每次统计周报都得现算遇到“周一为一周起点”这种业务语义就是坑。weekday_cn存中文字符串报表直接展示用省得查询时还得case when。2.2 农历字段与闰月处理农历部分最关键的是闰月。我的方案是用负数标记闰月date_lunar_month位1到12代表正常月份如果是闰月就存负数比如闰二月存-2同时is_leap_month置1。这样设计的好处是既能精确定位每个具体的农历日又能直接通过where is_leap_month 1筛选出所有闰月数据。date_lunar_month_cn和date_lunar_day_cn是给展示层准备的。农历腊月、正月初一这种说法终端用户只认这个你要是让前端拿数字去拼初一写成初一没问题但十一月和腊月排序、正月显示各种边界情况能让你改到崩溃。所以宁可建表时多两个字段一次性生成好后面所有地方直接查出来用。lunar_festival和solar_terms这两个扩展字段是我在实际项目里加的。春节、中秋、清明这些节日直接标注在表里做活动运营排期、考勤计算时一条SQL就能把节假日拉出来。二十四节气对农业、物流行业特别有用存一个字段排产计划直接关联。3. 数据生成实操从万年历数据到SQL脚本表结构定了最费工夫的就是往里面灌数据。网上能下到的现成人SQL文件不少但格式、表结构各不相同拿到手基本都要清洗转换。我这次是用Python写脚本调用lunar_python库生成全部数据再批量导入MySQL。3.1 数据准备与格式转换先装库lunar_python是基础库直接装就行pip install lunar_python然后跑生成脚本。这里给出我第一次用的核心逻辑生成CSV文件之后再导入MySQLfrom lunar_python import Solar, Lunar import csv start_year 1900 end_year 2100 with open(calendar_data.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([date_solar, date_solar_year, date_solar_month, date_solar_day, weekday, weekday_cn, date_lunar_year, date_lunar_month, date_lunar_day, date_lunar_month_cn, date_lunar_day_cn, is_leap_month, lunar_festival, solar_terms]) def get_weekday_cn(w): return [周一, 周二, 周三, 周四, 周五, 周六, 周日][w - 1] # 从1900-01-31开始这是农历1900年正月初一 solar Solar.fromYmd(1900, 1, 31) end_solar Solar.fromYmd(2100, 12, 31) while solar.toYmd() end_solar.toYmd(): lunar solar.getLunar() # 农历月1-12闰月用负数标记 lunar_month lunar.getMonth() if lunar.getMonth() 0: lunar_month lunar.getMonth() writer.writerow([ solar.toYmd(), solar.getYear(), solar.getMonth(), solar.getDay(), solar.getWeek(), get_weekday_cn(solar.getWeek()), lunar.getYear(), lunar_month, lunar.getDay(), lunar.getMonthInChinese(), lunar.getDayInChinese(), 1 if lunar.getMonth() 0 else 0, lunar.getFestival() or , lunar.getJieQi() or ]) solar solar.next(1)脚本里有个细节必须注意公历1900年1月1日对应的是农历己亥年腊月初一属于上一年不是完整的农历年开头。所以我从1900年1月31日开始迭代这一天才是农历1900年正月初一。如果你从1月1日开始跑前半段会落到农历1899年年份就乱了。3.2 导入MySQL与常用查询实战CSV生成后导入用MySQL的LOAD DATA最靠谱比一行一行insert快太多LOAD DATA LOCAL INFILE /tmp/calendar_data.csv INTO TABLE calendar_date CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (date_solar, date_solar_year, date_solar_month, date_solar_day, weekday, weekday_cn, date_lunar_year, date_lunar_month, date_lunar_day, date_lunar_month_cn, date_lunar_day_cn, is_leap_month, lunar_festival, solar_terms);导入完成后验证一下数据量。1900年1月31日到2100年12月31日应该是73403行左右。如果行数不对优先怀疑是不是起始日期选错或者2100年终止日期没有覆盖完整。之后就是日常查询的写法。比如查2025年农历春节对应的公历日期SELECT date_solar, date_lunar_month_cn, date_lunar_day_cn FROM calendar_date WHERE date_lunar_year 2025 AND date_lunar_month 1 AND date_lunar_day 1;再比如查某个月所有含节日的日子做运营活动排期就很有用SELECT date_solar, lunar_festival, solar_terms FROM calendar_date WHERE date_solar BETWEEN 2025-03-01 AND 2025-03-31 AND (lunar_festival IS NOT NULL OR solar_terms IS NOT NULL);4. 常见报错与排错经验这套表我自己从第一版到最终落地中间踩了不少坑每个坑都很典型写出来给大家避雷。4.1 日期类型选择错误引发的隐患第一版我图省事把date_solar定义成了varchar(10)。当时想的很简单反正存的就是“2024-11-15”这种字符串排序也不会乱。结果后来做范围查询用户要查11月1日到11月30日的数据varchar确实也能查出正确结果但一旦表数据量上来或者需要和订单表joinvarchar类型的比较比date类型慢得多索引优化器也没法正确估算范围。后面踩到实际坑才彻底改掉有次按BETWEEN 2024-11-01 AND 2024-11-31查字符串比较根本不会提示你11月31日不存在直接把数据查空了业务方第二天投诉才发现。改成date类型之后MySQL会提示日期值不合法从根源上避免这种低级错误。所以日期字段必须用date类型这是原则问题不是风格偏好。4.2 唯一索引冲突与重复数据处理做数据清洗时还有个高频报错就是Duplicate entry 2024-05-01 for key uk_date_solar。第一次生成数据时脚本里某个月份判断写错导致同一天生成了两行。后来加了唯一索引导入时报错才发现。处理这个问题的经验是两句话第一唯一索引一定要建宁可导入时慢一点也要保证数据干净第二导入失败后不要急着改数据重跑先用SQL查重复项看看是不是逻辑问题SELECT date_solar, COUNT(*) FROM calendar_date GROUP BY date_solar HAVING COUNT(*) 1;如果查出重复基本可以确定是生成脚本里日期迭代有bug比如某个月份边界判断写成而不是或者某个闰月处理漏了条件。把脚本修好再重新生成比在数据库里手动delete快得多也稳得多。4.3 闰月字段设计不当的坑最早我处理闰月是直接用一个varchar字段存“闰二月”这种字符串。展示是方便了但做查询时傻眼了——想筛出所有闰月数据where date_lunar_month_cn like %闰%能查但性能差想按农历月做排序或分组字符串排序全是乱的“闰二月”排在“三月”后面逻辑全乱。后来才改成现在的方案数字字段加上is_leap_month标志位。查询时统一用数字字段比较展示时用中文字段两边各司其职。这个修改也让按农历月份排序变得简单可靠SELECT date_solar, date_lunar_month_cn, date_lunar_day_cn FROM calendar_date WHERE date_lunar_year 2025 ORDER BY ABS(date_lunar_month), date_lunar_day;用ABS()取绝对值排序就能把1月、2月、闰2月、3月按自然顺序排出来然后再用is_leap_month区分是不是闰月。5. 实用的扩展玩法从日历表到业务价值基础日历表建好之后后面这些扩展是我在实际系统里加过的每一个都有明确的业务场景。5.1 增加节假日与调休字段日历表最直接的价值是支撑节假日管理。可以加一列holiday_type或workday_type来标记工作日、周末、法定节假日、调休日。有了这个字段考勤系统算每月应出勤天数就变成一条SQL的事SELECT date_solar_year, date_solar_month, COUNT(*) AS work_days FROM calendar_date WHERE workday_type 工作日 GROUP BY date_solar_year, date_solar_month;我一般把当年的法定节假日安排整理成UPDATE语句直接写进表里第二年再整体更新一次。比写死在代码里灵活也比每年临时维护一套规则库简单。5.2 结合订单系统做农历周期分析另一个让我觉得这张表值回票价的场景是结合订单做农历维度分析。有个做食品礼盒的客户订单高峰期跟中秋、春节强相关。传统做法是拿订单时间自己去转换农历然后统计下单量复杂又容易出错。有了日历表直接joinSELECT c.date_lunar_year, c.date_lunar_month, COUNT(o.order_id) AS order_cnt FROM order_table o JOIN calendar_date c ON DATE(o.create_time) c.date_solar WHERE c.date_lunar_year 2024 GROUP BY c.date_lunar_year, c.date_lunar_month ORDER BY c.date_lunar_year, c.date_lunar_month;查询性能极好因为join走的是date_solar的唯一索引。这个方案比任何自定义函数都稳。5.3 农历生日提醒会员系统里做农历生日提醒也是这类表的高频用途。存用户农历生日每天跑一次定时任务扫当天农历日期对应的会员发推送消息。有了日历表这个任务就是一个简单的joinSELECT u.user_id, u.user_name FROM user_table u JOIN calendar_date c ON c.date_solar CURDATE() WHERE u.lunar_month c.date_lunar_month AND u.lunar_day c.date_lunar_day AND u.is_lunar_birthday 1;6. 数据准确性验证与维护建议日历数据的准确性是整套方案的生命线。数据灌进去之后一定要做几轮验证。第一轮抽查关键节点。2024年的春节是2月10日2025年的春节是1月29日2023年闰二月用这几个已知准确的日子反查表数据。查不到或者对不上说明数据源或生成脚本有问题就得停下来检查。第二轮验证连续性。公历日期必须是连续的一天不多一天不少。直接用SQL算相邻两天的间隔有间隔超过1天的就是缺数据SELECT a.date_solar AS pre_date, b.date_solar AS next_date, DATEDIFF(b.date_solar, a.date_solar) AS day_diff FROM calendar_date a JOIN calendar_date b ON b.id a.id 1 WHERE DATEDIFF(b.date_solar, a.date_solar) ! 1;如果这个查询返回任何一行说明中间有缺失日期数据不连续。第三轮校验今年的节日和节气是否和官方日历一致。清明节的公历日期每年不同2025年是4月4日2026年是4月5日和官方日历比对一下就行。这轮测试通过基本可以放心入库。维护上也有个建议表结构定下来之后建表SQL脚本、生成数据脚本、验证SQL脚本要一起放进项目文档库或者代码仓库里。日历数据虽然不动但万一哪年要更新节假日、或者要调整字段有原脚本在手就不用重新从零折腾。我个人在实际操作中的体会是像日历表这种看起来基础的数据基建一旦做好能给后续业务省下大量时间。很多你以为是算法问题的需求比如农历转换、节气统计、节假日计算本质上都是数据问题一张设计合理的表就能解决。用数据库存日历数据既要保证数据准确也要保证结构扩展性这才是这张表真正值钱的地方。本文还有配套的精品资源点击获取