简介这份MySQL日历数据表面向需要处理日期转换与农历功能的开发者尤其适合日历应用、事件管理、传统节日提醒等场景。包内提供1900至2100年的公历与农历对照数据通过SQL脚本建表并填充记录可支撑公历农历互查、节气与传统节日检索、日历视图切换等需求。资源共1个文件为sql脚本压缩包约1.94MB导入后即可获得结构清晰的公历表与农历表字段涵盖年月日、星期、节假日标记及描述信息并涉及闰月处理与转换逻辑。目前已有1888人学习下载。借助这份数据读者能快速搭建日期转换基础减少自行推算农历的繁琐工作也可将其与用户日程、活动等业务数据关联扩展出更完整的日历服务同时需注意数据导入正确性与查询优化以保障系统运行效率。1. 一张日历表能踩多少坑从 1900 到 2100 的日期维度为什么值得单独建表做业务系统的人大多有过这种经历某天运营突然要查“去年中秋节前后三天的订单量”或者财务要按农历结算周期出报表又或者前端要显示“距离春节还有几天”。这时候你打开数据库发现只有一张orders表和一个created_at字段农历、节气、节假日、工作日调休全都没有。临时写代码算Python 的lunardate库能算但每次查询都要在应用层循环数据量一大就崩。更麻烦的是不同语言、不同库对闰月、节气交接时刻的处理还不一致同一个日期算出来两个结果这就是典型的“玄学”问题。mysql日历数据表1900-2100公历表和农历表).rar这个标题指向的就是把这套日期维度提前物化成数据库表。公历表负责阳历日期、星期、周数、是否闰年农历表负责阴历日期、干支、生肖、节气、传统节日。两张表配合就能在 SQL 层面直接完成“按农历月统计”“查某节气对应的公历日期”“判断某天是不是调休工作日”这类操作。适合谁适合做电商、金融、OA、排班、会员生日营销的后端和数据分析人员。你不需要成为历法专家但需要知道这张表怎么建、怎么灌数据、怎么查才不翻车。2. 公历表和农历表到底该存什么字段设计与选型理由2.1 公历表别只存一个 date 字段很多人觉得公历日期用 MySQL 原生DATE类型就够了为什么还要单独建表因为原生类型只存“哪一天”不存“这一天是什么属性”。业务查询里高频出现的需求包括按周聚合、按季度聚合、判断是否周末、判断是否月末、判断是否闰年。这些如果每次都用WEEK()、QUARTER()函数算索引失效不说跨年周数还会出现第 0 周和第 53 周的边界问题。我一般会建一张dim_calendar_gregorian核心字段如下字段名类型说明date_keyINT主键格式 YYYYMMDD如 20240315full_dateDATE标准日期year_numSMALLINT年month_numTINYINT月day_numTINYINT日day_of_weekTINYINT1周一 … 7周日day_of_yearSMALLINT一年中第几天week_of_yearTINYINTISO 周数quarter_numTINYINT季度is_weekendTINYINT0/1is_leap_yearTINYINT0/1days_in_monthTINYINT当月天数用date_key做整型主键而不是DATE是因为在事实表关联时整型 JOIN 比日期类型快而且方便做分区。day_of_week用 1 到 7 而不是 0 到 6是为了跟国内习惯对齐周一是一周第一天。2.2 农历表闰月是最大的变量农历表比公历表复杂得多。核心难点是闰月农历一年通常 12 个月但某些年份有 13 个月多出来的那个月叫闰月。比如闰四月意味着有两个四月。如果你在表里只存lunar_month就没法区分“四月”和“闰四月”统计结果直接翻倍。常见做法是加一个is_leap_month标记并且用lunar_month加is_leap_month组成唯一键。农历表我一般这样设计字段名类型说明date_keyINT关联公历表lunar_yearSMALLINT农历年lunar_monthTINYINT农历月1-12lunar_dayTINYINT农历日1-30is_leap_monthTINYINT0正常月1闰月heavenly_stemCHAR(1)天干earthly_branchCHAR(1)地支zodiacVARCHAR(4)生肖solar_termVARCHAR(8)节气名无则为空lunar_festivalVARCHAR(16)农历节日如春节、中秋注意lunar_day最大是 30因为农历月只有 29 或 30 天。solar_term存节气名称而不是编号方便直接WHERE solar_term 清明。节气一年有 24 个每个对应一个公历日期但交接时刻可能落在一天中的任何时间表里只精确到天做营销活动足够做天文计算不够。2.3 两张表怎么关联date_key 是桥梁公历表和农历表通过date_key一对一关联。查询“2024 年中秋节是公历几月几号”SELECT g.full_date, l.lunar_festival FROM dim_calendar_gregorian g JOIN dim_calendar_lunar l ON g.date_key l.date_key WHERE l.lunar_festival 中秋 AND g.year_num 2024;这条 SQL 能走索引因为lunar_festival和year_num都可以建索引。如果不用表在应用层用库函数算每次都要遍历 365 天数据量上来后 CPU 直接打满。这就是建表的收益把计算成本从查询时转移到建表时一次生成长期使用。3. 从 1900 到 2100 的数据怎么灌进去生成脚本与批量插入3.1 用 Python 生成 CSV 再 LOAD DATA200 年大约是 73000 天公历表加农历表合计约 15 万行。这个量级用INSERT逐条插也能跑但太慢。我一般用 Python 生成 CSV再用LOAD DATA LOCAL INFILE灌进去几秒钟完事。import csv from datetime import date, timedelta # 农历转换用第三方库这里只演示公历表生成逻辑 start date(1900, 1, 1) end date(2100, 12, 31) delta timedelta(days1) with open(gregorian.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([date_key,full_date,year_num,month_num,day_num, day_of_week,day_of_year,week_of_year,quarter_num, is_weekend,is_leap_year,days_in_month]) d start while d end: date_key int(d.strftime(%Y%m%d)) # isoweekday: 1周一 7周日 dow d.isoweekday() doy d.timetuple().tm_yday # ISO周数注意跨年周 iso_week d.isocalendar()[1] quarter (d.month - 1) // 3 1 is_weekend 1 if dow 6 else 0 # 闰年判断能被4整除但不能被100整除或能被400整除 is_leap 1 if (d.year % 4 0 and d.year % 100 ! 0) or (d.year % 400 0) else 0 # 当月天数 if d.month 12: next_month date(d.year 1, 1, 1) else: next_month date(d.year, d.month 1, 1) days_in_month (next_month - date(d.year, d.month, 1)).days writer.writerow([date_key, d, d.year, d.month, d.day, dow, doy, iso_week, quarter, is_weekend, is_leap, days_in_month]) d delta这段脚本的关键点isocalendar()返回的周数是 ISO 标准跨年时可能出现第 1 周属于上一年 12 月的情况这是正常的不要自己写算法去纠正。days_in_month用下个月第一天减当月第一天得到比写 switch-case 可靠。3.2 农历数据生成闰月和节气要单独处理农历转换建议用成熟的库不要自己实现。生成 CSV 时重点检查闰月标记和节气字段。# 伪代码示意实际依赖具体农历库的API from lunar_lib import LunarDate # 假设的库名实际按你选用的库调整 with open(lunar.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([date_key,lunar_year,lunar_month,lunar_day, is_leap_month,heavenly_stem,earthly_branch, zodiac,solar_term,lunar_festival]) d start while d end: ld LunarDate.from_solar(d) writer.writerow([ int(d.strftime(%Y%m%d)), ld.year, ld.month, ld.day, 1 if ld.is_leap else 0, ld.stem, ld.branch, ld.zodiac, ld.solar_term or , ld.festival or ]) d delta参数说明is_leap必须显式写入不能靠月份大于 12 来判断。solar_term和lunar_festival允许为空字符串导入 MySQL 时用NULL还是空串要统一我一般用空串避免NULL比较的坑。3.3 建表与导入的完整命令-- 建公历表 CREATE TABLE dim_calendar_gregorian ( date_key INT NOT NULL PRIMARY KEY, full_date DATE NOT NULL, year_num SMALLINT NOT NULL, month_num TINYINT NOT NULL, day_num TINYINT NOT NULL, day_of_week TINYINT NOT NULL, day_of_year SMALLINT NOT NULL, week_of_year TINYINT NOT NULL, quarter_num TINYINT NOT NULL, is_weekend TINYINT NOT NULL DEFAULT 0, is_leap_year TINYINT NOT NULL DEFAULT 0, days_in_month TINYINT NOT NULL, KEY idx_year_month (year_num, month_num), KEY idx_week (year_num, week_of_year) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 建农历表 CREATE TABLE dim_calendar_lunar ( date_key INT NOT NULL PRIMARY KEY, lunar_year SMALLINT NOT NULL, lunar_month TINYINT NOT NULL, lunar_day TINYINT NOT NULL, is_leap_month TINYINT NOT NULL DEFAULT 0, heavenly_stem CHAR(1) DEFAULT , earthly_branch CHAR(1) DEFAULT , zodiac VARCHAR(4) DEFAULT , solar_term VARCHAR(8) DEFAULT , lunar_festival VARCHAR(16) DEFAULT , KEY idx_lunar (lunar_year, lunar_month, lunar_day), KEY idx_festival (lunar_festival), KEY idx_solar_term (solar_term) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;导入命令mysql -u root -p your_db -e LOAD DATA LOCAL INFILE gregorian.csv INTO TABLE dim_calendar_gregorian FIELDS TERMINATED BY , ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 ROWS; 注意LOCAL关键字需要客户端和服务端都开启local_infile。如果报错The used command is not allowed检查SHOW VARIABLES LIKE local_infile;用SET GLOBAL local_infile 1;打开。导入前先TRUNCATE TABLE避免重复数据。4. 查询实战农历节日、节气和周数统计怎么写才不翻车4.1 查农历节日对应的公历日期-- 查 2024 年到 2026 年所有中秋节 SELECT g.full_date, l.lunar_festival FROM dim_calendar_gregorian g JOIN dim_calendar_lunar l ON g.date_key l.date_key WHERE l.lunar_festival 中秋 AND g.year_num BETWEEN 2024 AND 2026 ORDER BY g.full_date;这条查询走idx_festival索引毫秒级返回。注意lunar_festival存的是“中秋”而不是“中秋节”建表时要统一命名否则查不到。4.2 按节气做营销活动窗口-- 查立春后 15 天内的所有日期 SELECT g.full_date, l.solar_term FROM dim_calendar_gregorian g JOIN dim_calendar_lunar l ON g.date_key l.date_key WHERE l.solar_term 立春 AND g.full_date BETWEEN 2024-01-01 AND 2024-12-31;如果要算“立春后 15 天”需要先用子查询找到立春日期再关联公历表SELECT g.full_date FROM dim_calendar_gregorian g WHERE g.full_date BETWEEN (SELECT g2.full_date FROM dim_calendar_gregorian g2 JOIN dim_calendar_lunar l2 ON g2.date_key l2.date_key WHERE l2.solar_term 立春 AND g2.year_num 2024) AND DATE_ADD( (SELECT g3.full_date FROM dim_calendar_gregorian g3 JOIN dim_calendar_lunar l3 ON g3.date_key l3.date_key WHERE l3.solar_term 立春 AND g3.year_num 2024), INTERVAL 15 DAY);这种写法虽然长但逻辑清晰。更好的做法是把节气日期单独缓存到一张小表避免重复子查询。4.3 周数统计的边界处理按周统计订单量时直接用week_of_year分组会出现跨年周被拆成两周的问题。比如 2024 年 12 月 30 日是周一ISO 周数是第 1 周属于 2025 年。如果按year_num week_of_year分组2024 年的最后两天会被分到 2025 年第 1 周。解决方法是加一个iso_year字段存 ISO 标准下的年份。生成数据时用d.isocalendar()[0]获取。查询时按iso_year, week_of_year分组就不会拆散跨年周。SELECT iso_year, week_of_year, COUNT(*) AS order_cnt FROM orders o JOIN dim_calendar_gregorian g ON DATE_FORMAT(o.created_at, %Y%m%d) g.date_key GROUP BY iso_year, week_of_year ORDER BY iso_year, week_of_year;注意DATE_FORMAT会导致索引失效更好的做法是在订单表里冗余一个date_key字段写入时直接算好。5. 避坑与排查农历表最容易出错的 5 个地方5.1 闰月导致统计翻倍现象统计“农历四月”的订单量结果比预期多了一倍。原因当年有闰四月lunar_month 4的记录出现了两次一次is_leap_month 0一次is_leap_month 1。解决查询时明确加AND is_leap_month 0或AND is_leap_month 1看业务是否需要包含闰月。如果业务说“四月”通常指正常四月闰月要单独说明。5.2 节气交接时刻导致日期偏差现象某年清明是 4 月 4 日但表里写的是 4 月 5 日。原因节气交接时刻在 4 月 4 日 23:59 或 4 月 5 日 00:01不同库对时刻的舍入方式不同。解决建表时只精确到天就要接受这个误差。如果业务对节气时刻敏感需要额外存solar_term_time字段精确到分钟。我一般会在生成数据时打印出所有节气日期人工抽查几个跟权威日历对比。5.3 1900 年和 2100 年的边界数据缺失现象查 1900 年 1 月 1 日的农历返回空。原因农历转换库的起始日期可能不是 1900 年 1 月 1 日或者结束日期不到 2100 年 12 月 31 日。解决生成数据前先确认库的支持范围。如果库只支持 1900 年到 2099 年就把表范围改成 1900 到 2099不要硬撑到 2100。边界年份的数据宁可少不要错。5.4 字符集导致节日名称乱码现象查出来的lunar_festival是问号或乱码。原因CSV 文件编码是 GBKMySQL 表是 utf8mb4导入时没指定编码。解决生成 CSV 时统一用 UTF-8导入命令加CHARACTER SET utf8mb4。建表时DEFAULT CHARSETutf8mb4不能省。5.5 索引建了但查询不走现象WHERE lunar_festival 春节查询很慢。原因lunar_festival字段默认值用了NULL而查询条件是字符串MySQL 无法用索引。解决把默认值改成空字符串并且查询时用 春节而不是IS NULL。如果确实需要查空值用 。6. 进阶用法把日历表变成排班和营销的底座日历表建好之后真正的价值在于跟业务表关联。我做过一个排班系统核心逻辑就是靠dim_calendar_gregorian的is_weekend和一张单独的dim_holiday表存法定节假日和调休工作日来判断某天是否上班。查询“某员工本月工作日天数”SELECT COUNT(*) AS work_days FROM dim_calendar_gregorian g LEFT JOIN dim_holiday h ON g.date_key h.date_key WHERE g.year_num 2024 AND g.month_num 3 AND ( (g.is_weekend 0 AND h.is_holiday 0) OR (g.is_weekend 1 AND h.is_workday 1) );这条 SQL 的逻辑是正常工作日是“非周末且非节假日”调休工作日是“周末但被标记为上班”。dim_holiday表每年更新一次只存几十行维护成本极低。另一个场景是会员生日营销。农历生日每年对应的公历日期都不同用日历表可以提前算出未来 30 天内的农历生日会员SELECT m.member_id, g.full_date, l.lunar_month, l.lunar_day FROM members m JOIN dim_calendar_lunar l ON m.lunar_month l.lunar_month AND m.lunar_day l.lunar_day AND m.is_leap_month l.is_leap_month JOIN dim_calendar_gregorian g ON l.date_key g.date_key WHERE g.full_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);这里的关键是is_leap_month也要匹配否则闰月出生的会员会在正常月被误触发。验证数据是否正确我习惯用几个已知日期做回归测试2024 年春节是 2 月 10 日2025 年春节是 1 月 29 日2024 年中秋是 9 月 17 日。写一个简单的 SQL 脚本每次重新生成数据后跑一遍全部命中才上线。-- 回归测试检查已知节日日期 SELECT 2024春节 AS item, full_date FROM dim_calendar_gregorian g JOIN dim_calendar_lunar l ON g.date_key l.date_key WHERE l.lunar_festival 春节 AND g.year_num 2024 UNION ALL SELECT 2025春节, full_date FROM dim_calendar_gregorian g JOIN dim_calendar_lunar l ON g.date_key l.date_key WHERE l.lunar_festival 春节 AND g.year_num 2025;如果结果不是 2024-02-10 和 2025-01-29说明数据生成有问题不要抱侥幸心理上线。我在这上面翻过车当时觉得差一天无所谓结果营销短信提前一天发出去被业务方追着问。后来养成的习惯是日历数据每次重新生成必须跑回归测试通过才允许导入生产库。希望帮到你。本文还有配套的精品资源点击获取