MySQL日期维度表设计：公历农历双表构建200年日历数据

简介面向MySQL开发者与数据统计人员的日历数据表资源内含两个SQL脚本分别创建公历表和农历表完整覆盖1900—2100年共200年的日期数据。公历表设计有week_day、is_weekend、is_holiday等字段农历表包含农历年、月、日以及中文月名、日期名称和农历节日标记两表通过gregorian_date关联可一次查询同时获得公历与农历信息辅助工作日计算、节假日判断、节气提醒等业务场景。压缩包体积仅1.07MB共2个SQL文件结构简洁导入即可用适合直接集成到现有MySQL库中也便于开发者在此基础上扩展自身业务字段。已有297人学习使用说明其具备一定的实用参考价值能帮助快速搭建日期基础数据层省去手工换算与逐条维护的重复工作提升整体开发效率。 做后端开发这些年我越来越觉得数据库里最容易被低估的一张表往往是“日期表”。尤其是接到排班、日程、节日提醒、财务结算这类需求时如果每次都用DATE_FORMAT现场算日期写出来的 SQL 不但啰嗦还会在农历、节气、闰月面前直接崩溃。今年年初我做的一个客户项目里需要同时支持公历和农历的日历展示从 1900 年到 2100 年整整两百年我索性一次性设计了两张 MySQL 日期维表公历表和农历表。今天把整套建表、生成、应用和排坑的思路完整分享出来希望帮你少走弯路。这个方案做出来之后我自己的感觉是它不适合两三天的临时查询但非常适合做“主数据”级别的底层表。日历应用、OA 系统、教务系统、甚至电商的大促日历都能直接复用。无论你是刚学 MySQL 的新手还是已经在数据仓库里摸爬滚打的开发者这篇文章里的存储过程、递归 CTE 和程序批量导入思路都能拿来即用。1. 为什么要建“日期维度表”业务场景决定了它的价值1.1 日期不是查询条件而是主数据大多数业务系统里日期只是订单、日志表里的一个字段。但当你需要做“某天是周几”“今年第几周”“是不是节假日”这类计算时程序员的第一反应是写函数。MySQL 内置了WEEK()、DAYOFWEEK()、DAYOFYEAR()这些函数确实够用但一旦涉及农历、节气、调休数据库本身就没有现成能力了。我见过不少项目在代码里维护一份SolarUtil类里面塞满了农历转换的算法和硬编码数据。这种做法在单个服务里还好一旦换成多语言、多服务架构或者需要数据团队做报表分析这套逻辑就没办法共享了。把它落成 MySQL 表让任何服务、任何报表工具都能直接SELECT这才是日期维表的核心价值。1.2 公历表、农历表分别解决什么问题公历表解决的是“规律性计算”的通用问题日期递增、星期、季度、自然周、工作日标记。农历表解决的是“无规律可循”的传统历法问题农历月、农历日、闰月、生肖、节气、传统节日。别小看农历它计算规则相当绕既不是简单的 30 天一月也不是固定闰月位置而是基于天文观测的“定朔定气”结果。1900 年到 2100 年这段区间因为数据公开、网上有完整对照是业务系统最常覆盖的范围也是我做这张表限定区间的主要原因。2. 表结构设计用最少字段撑起十年需求2.1 公历表以solar_date为唯一主键公历表的核心是“一天一行”字段不用贪多常见业务够用就行。我最终用的是这套结构CREATE TABLE t_calendar_solar ( solar_date DATE NOT NULL COMMENT 公历日期, solar_year SMALLINT NOT NULL COMMENT 年, solar_month TINYINT NOT NULL COMMENT 月, solar_day TINYINT NOT NULL COMMENT 日, day_of_week TINYINT NOT NULL COMMENT 星期几1周一 ... 7周日, day_of_year SMALLINT NOT NULL COMMENT 年内第几天, week_of_year TINYINT NOT NULL COMMENT 年内第几周, is_weekend TINYINT NOT NULL COMMENT 是否周末1是0否, is_workday TINYINT NOT NULL COMMENT 是否工作日1是0否默认按周一至周五, period_tag VARCHAR(20) DEFAULT COMMENT 可扩展字段如早中晚班、学期周次等, PRIMARY KEY (solar_date), KEY idx_solar_ym (solar_year, solar_month) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT公历日历表;这里有两个细节day_of_week我按“1周一7周日”存储因为国内业务习惯把周一当一周开始和WEEK()函数的mode参数要区分清楚。is_workday只是默认的周一到周五真正的法定节假日调休不在这一层处理我会在业务侧另加“实际工作日”字段或者用节假日配置表去覆盖。2.2 农历表重点字段放到lunar_date复合键上农历表的结构和公历表一一对应核心是通过一个“农历日期标识”字段把农历年、月、日组合起来方便查询和关联。CREATE TABLE t_calendar_lunar ( lunar_date VARCHAR(10) NOT NULL COMMENT 农历日期key如 2024-01-15 表示农历2024年正月初一闰月用L前缀表示2024-02L-01, solar_date DATE NOT NULL COMMENT 对应的公历日期, lunar_year SMALLINT NOT NULL COMMENT 农历年, lunar_month TINYINT NOT NULL COMMENT 农历月1-12闰月用负值或单独标记, lunar_day TINYINT NOT NULL COMMENT 农历日1-30, is_leap_month TINYINT NOT NULL COMMENT 是否闰月1是0否, lunar_month_name VARCHAR(10) NOT NULL COMMENT 农历月中文名如 正月、二月、闰四月, lunar_day_name VARCHAR(10) NOT NULL COMMENT 农历日中文名如 初一、十五、廿三, jieqi VARCHAR(20) DEFAULT COMMENT 节气名如 立春、雨水无则为空, lunar_year_name VARCHAR(20) DEFAULT COMMENT 干支纪年如 甲辰, zodiac VARCHAR(10) DEFAULT COMMENT 生肖如 龙, PRIMARY KEY (lunar_date), UNIQUE KEY idx_solar (solar_date), KEY idx_lunar_ym (lunar_year, lunar_month) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT农历日历表;农历月份的存储需要特别当心。我用is_leap_month字段单独标记闰月同时lunar_date里用L前缀做区分比如2024-02L-01表示 2024 年闰二月初一。这样既方便直接按字符串查询又不会和正常二月混淆。2.3 为什么用DATE类型而不是字符串很多新手喜欢用VARCHAR存日期觉得“2024-01-01”看起来直观。但一旦涉及范围查询、排序、索引DATE类型的优势就出来了存储空间更小3 字节 vs 至少 10 字节索引效率更高还能直接用BETWEEN、DATE_ADD等日期函数。所以公历表主键用DATE农历表也保留了solar_date这个DATE列做唯一键。两表通过solar_date直接关联应用层不用做任何转换。3. 公历表数据填充从 1900 到 21007 万行的生成方案3.1 用存储过程批量生成简单直接如果你还在用 MySQL 5.7 或更早版本递归 CTE 用不了最稳妥的方式是存储过程。思路很简单从起始日期开始循环DATE_ADD一天直到结束日期。生成 1900-01-01 到 2100-12-31一共 73415 天左右循环 7 万多次实测在普通开发机上跑完不到 5 秒。DELIMITER $$ CREATE PROCEDURE sp_generate_solar_calendar(IN start_date DATE, IN end_date DATE) BEGIN DECLARE cur_date DATE DEFAULT start_date; DECLARE cur_year SMALLINT; DECLARE cur_month TINYINT; DECLARE cur_day TINYINT; WHILE cur_date end_date DO SET cur_year YEAR(cur_date); SET cur_month MONTH(cur_date); SET cur_day DAY(cur_date); INSERT INTO t_calendar_solar (solar_date, solar_year, solar_month, solar_day, day_of_week, day_of_year, week_of_year, is_weekend, is_workday) VALUES (cur_date, cur_year, cur_month, cur_day, DAYOFWEEK(cur_date), DAYOFYEAR(cur_date), WEEKOFYEAR(cur_date), IF(DAYOFWEEK(cur_date) IN (1, 7), 1, 0), IF(DAYOFWEEK(cur_date) IN (1, 7), 0, 1)); SET cur_date DATE_ADD(cur_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL sp_generate_solar_calendar(1900-01-01, 2100-12-31);这个存储过程里最关键的一点是DAYOFWEEK()的返回值在 MySQL 里 1周日2周一一直到 7周六。所以判断周末时直接用IN (1, 7)不需要额外转换。如果你想按“1周一”存业务字段记得要做一次(DAYOFWEEK(cur_date) 5) % 7 1之类的换算或者直接像我结构里那样用别的字段存映射关系。3.2 MySQL 8.0 用递归 CTE一行 SQL 搞定MySQL 8.0 支持WITH RECURSIVE生成连续日期变得非常优雅。递归 CTE 的效率也很高因为 MySQL 内部做了迭代优化7 万行级别完全不是问题。INSERT INTO t_calendar_solar (solar_date, solar_year, solar_month, solar_day, day_of_week, day_of_year, week_of_year, is_weekend, is_workday) WITH RECURSIVE date_series AS ( SELECT 1900-01-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2100-12-31 ) SELECT d, YEAR(d), MONTH(d), DAY(d), (DAYOFWEEK(d) 5) % 7 1 AS day_of_week, DAYOFYEAR(d), WEEKOFYEAR(d), IF(DAYOFWEEK(d) IN (1, 7), 1, 0), IF(DAYOFWEEK(d) IN (1, 7), 0, 1) FROM date_series;这里我用(DAYOFWEEK(d) 5) % 7 1把 1周日转成了 1周一的方式如果你不需要直接用原始DAYOFWEEK也行。还有个细节递归 CTE 默认递归次数上限是 1000必须用SET cte_max_recursion_depth 80000;或者写进 session 配置否则生成到 1902 年左右就会报错。这个坑我第 6 节详细讲。3.3 数据量和性能7 万行真的不多先算笔账1900 到 2100 是 201 年每年 365 或 366 天总行数约 73400 行。这张表即使把period_tag等扩展字段全加满单表空间也不会超过 10MB。InnoDB 下按主键DATE顺序插入页分裂几乎不发生查询走主键索引都是微秒级。所以不要害怕“200 年的数据量”真正该怕的是没索引、没主键的乱表。4. 农历数据最麻烦的“天文历法”数据该怎么入4.1 农历数据的来源别“发明轮子”农历不是简单的算法能算出来的它依赖天文观测和官方发布的农历历法表。所以我的原则是不要试图在 SQL 里推算农历而是直接使用公开、可信的数据源。网上有很多 Python 库如lunar_python、sxtwl内置了 1900-2100 年的农历数据这些库的本质是把权威对照表打包成函数。用它们生成一次性数据再批量灌进 MySQL是最稳、最准、最省力的方案。4.2 用 Python 脚本生成农历表并导入我推荐三步走先用 Python 遍历所有公历日期拿到每一天对应的农历信息再逐行写入 MySQL。核心脚本大致长这样import pymysql from datetime import date, timedelta from lunar_python import Solar conn pymysql.connect(host127.0.0.1, userroot, passwordyourpass, databasetest, charsetutf8mb4) cursor conn.cursor() start date(1900, 1, 1) end date(2100, 12, 31) current start while current end: lunar Solar.fromYmd(current.year, current.month, current.day).getLunar() lunar_date_key f{lunar.getYear():04d}-{lunar.getMonth():02d}-{lunar.getDay():02d} if lunar.getIsLeap(): lunar_date_key f{lunar.getYear():04d}-{lunar.getMonth():02d}L-{lunar.getDay():02d} sql INSERT INTO t_calendar_lunar (lunar_date, solar_date, lunar_year, lunar_month, lunar_day, is_leap_month, lunar_month_name, lunar_day_name, jieqi, lunar_year_name, zodiac) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE solar_date VALUES(solar_date) cursor.execute(sql, ( lunar_date_key, current.strftime(%Y-%m-%d), lunar.getYear(), lunar.getMonth(), lunar.getDay(), 1 if lunar.getIsLeap() else 0, lunar.getMonthInChinese(), lunar.getDayInChinese(), lunar.getJieQi(), lunar.getYearInGanZhi(), lunar.getYearShengXiao() )) if current.day 1: conn.commit() # 每个月提交一次减少锁冲突 current timedelta(days1) conn.commit() cursor.close() conn.close() print(done)lunar_python的getJieQi()在非节气日返回空字符串所以jieqi字段不需要额外处理。这里最实用的一个技巧是ON DUPLICATE KEY UPDATE断点续跑或者二次执行时都不会产生重复数据幂等性拉满。4.3 程序生成和 SQL 生成的取舍农历表也可以用存储过程做但我不推荐。因为存储过程里写农历换算逻辑会非常痛苦你要么塞入一份巨大的常量表要么依赖 MySQL 内置函数——而它根本没有农历函数。程序生成最大的优势是数据验证方便Python 侧可以直接断言每年春节的公历日期和网上公开数据比对。我在生成时专门抽查了 2024 年春节2 月 10 日、2025 年春节1 月 29 日、2023 年闰二月3 月 22 日开始全部对上了才灌库。5. 实战应用农历生日提醒、日元晓历视图 节假日覆盖5.1 农历生日转公历查询有了农历表按农历查公历变得非常简单。比如查“每年农历正月初一对应哪些公历日期”SELECT l.lunar_year, l.solar_date AS chunjie_solar_date, DAYOFWEEK(l.solar_date) AS chunjie_day_of_week FROM t_calendar_lunar l WHERE l.lunar_month 1 AND l.lunar_day 1 AND l.is_leap_month 0 AND l.lunar_year BETWEEN 2020 AND 2030 ORDER BY l.lunar_year;如果要查“农历五月二十”在 2024 年对应的公历直接SELECT solar_date FROM t_calendar_lunar WHERE lunar_year 2024 AND lunar_month 5 AND lunar_day 20 AND is_leap_month 0;这套查询在生日提醒系统里非常实用。用户填的生日往往是农历系统只要在每年年初跑一次这个 SQL生成一张“今年所有用户农历生日的公历日期映射表”后面定时任务就简单了。5.2 按月生成日历视图日历组件需要的月视图用公历表一步搞定SELECT solar_date, solar_day, day_of_week, is_weekend, COALESCE(l.lunar_month_name, ), COALESCE(l.lunar_day_name, ), COALESCE(l.jieqi, ) FROM t_calendar_solar s LEFT JOIN t_calendar_lunar l ON s.solar_date l.solar_date WHERE s.solar_year 2024 AND s.solar_month 2 ORDER BY s.solar_date;这里用了LEFT JOIN因为公历表是主表农历表是补充信息。一个容易忽略的点2024 年 2 月既包含春节又包含雨水节气如果你在前端只展示“公历数字 农历日 节气”三列这张表返回的数据直接能渲染出完整日历卡片。5.3 节假日和调休怎么扩展我在第三层加了一个t_holiday_config表专门存当年法定节假日和调休安排CREATE TABLE t_holiday_config ( solar_date DATE NOT NULL, holiday_name VARCHAR(50) NOT NULL, is_off_day TINYINT NOT NULL COMMENT 1放假0调休上班, PRIMARY KEY (solar_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT法定节假日配置表;这种设计的好处是日历表保持“通用日期属性”节假日是“年度可变的政策数据”两者分表存每年更新二十几条配置即可不用重新生成 7 万行数据。这也是我从多个实际项目里总结出来的教训别把会变的东西和不变的东西混在一张表里。6. 常见问题与排查技巧实录6.1 递归 CTE 报错cte_max_recursion_depth限制我第一次跑递归 CTE 生成全年数据时直接报错提示Recursive query aborted after 1001 iterations。原因是 MySQL 默认递归上限是 1000 次。解决办法是在会话里调大上限SET SESSION cte_max_recursion_depth 80000;然后在同一次会话里执行上面的INSERT ... WITH RECURSIVE。放在存储过程里也可以只要SET先执行。6.2 农历闰月导致查询结果多一行处理闰月时最常见的坑是“为什么我查农历五月二十会出现两行”。因为 2023 年有闰二月2025 年有闰六月如果你查询时不加is_leap_month 0全表扫描会把闰月也带出来。我建议所有业务查询默认都过滤is_leap_month 0除非你的需求明确要处理闰月。6.3 1900 到 2100 之外的数据怎么办这张表只覆盖 1900-2100 年。如果业务临时需要 1899 年或 2101 年我的处理方式是先看需求是“防未来数据”还是“历史数据回填”。如果只是防未来2100 年之后的数据完全可以等临近时再生成因为农历数据源本身就依赖官方发布。如果是历史数据补录直接用程序脚本再跑一遍对应年份区间即可两张表的主键设计都支持增量插入。6.4 时区和DATE_ADD的边界问题日历表里所有日期都用DATE类型不存时间戳所以基本不受时区影响。但要注意如果你用NOW()和CURDATE()做“今天”的判断MySQL 的时区设置不同会导致结果偏差。我习惯在连接初始化时统一写SET time_zone 08:00保证应用层和数据库层的“今天”完全一致。6.5 索引设计联合索引和唯一索引怎么选公历表主键是solar_date但因为经常按solar_year, solar_month查询我加了idx_solar_ym联合索引。农历表除了主键lunar_date还加了solar_date唯一索引方便从公历日期直接反查农历。这里不需要给lunar_month或lunar_day单独建索引因为单独查月份的过滤度太低联合索引足够了。7. 数据校验哪些日期最容易被算错7.1 春节、除夕、闰月是三大校验点农历数据最容易错的地方就是春节、除夕和闰月。我生成完数据后会专门查这几个日期和公开日历核对。-- 查 2024 年春节和除夕的公历日期 SELECT MAX(CASE WHEN lunar_month 1 AND lunar_day 1 THEN solar_date END) AS chunjie, MAX(CASE WHEN lunar_month 12 AND lunar_day 30 THEN solar_date END) AS chuxi FROM t_calendar_lunar WHERE lunar_year 2024 AND is_leap_month 0;2024 年除夕是 2 月 9 日春节是 2 月 10 日这是公开到每本日历上的信息。如果查询结果和这个对不上优先怀疑数据源或者导入脚本而不是去改 SQL。7.2 用公历表自检天数总和校验还有一个笨办法但很有效统计每一年的天数和当年的实际天数对比。SELECT solar_year, COUNT(*) AS days_in_year FROM t_calendar_solar WHERE solar_year 2024 GROUP BY solar_year;2024 年是闰年应为 366 天1900 年是平年应为 365 天。如果结果不符合说明生成段位有问题。这种校验比肉眼抽查快得多也更容易自动化。7.3 补充一个“节气是否齐全”的抽查节气在农历表里是jieqi字段一个公历年会有 24 个节气。你可以用一条聚合 SQL 验证某年的节气数量排除数据漏导SELECT solar_year, COUNT(*) AS jieqi_count FROM t_calendar_lunar WHERE jieqi ! AND solar_year 2024 GROUP BY solar_year;正常情况下应该能得到 24 个左右按公历年份统计时头尾会出现跨年的节气偏差所以看到 23 或 25 也算正常具体要看节气的归属规则。8. 这套日历表的后续扩展方向日历表做完以后其实不只是“查个农历”这么简单。我在这套基础之上扩展过几个方向一是“财务工作日/结算日”表把调休后的实际上班日标记进is_workday字段做薪资计算直接SUM工作日天数二是“排课/排班”场景在period_tag里存学期周次、单双周标记教务系统排课就能按日历表自动生成三是“营销日历”把每季度的促销节点如双 11 准备期、年货节周期作为标记字段放进日历表BI 报表聚合时就不用到处CASE WHEN了。字段还可以再延伸比如农历表中增加“佛历/道历节日”字段公历表中增加“ISO 周”“所在季度”“月初/月末标记”这些都可以按业务需要新增。表结构我特意设计成宽表优先就是为了后续加字段的时候不用改动主键和已有索引。如果你要直接抄作业我的建议是公历表用第 3 节的递归 CTE 生成农历表用第 4 节的 Python 脚本导入再顺手做一遍第 7 节的校验。四步流程下来一张真正能用的 200 年日历双表就落地了。后面不管是做生日提醒还是做日历组件都能像查普通业务表一样轻松处理农历和节气这些“难啃的骨头”。本文还有配套的精品资源点击获取