简介这份MySQL日历数据表资源面向数据库开发者、后端工程师及需要处理日期业务逻辑的技术人员提供1900至2100年共200年的公历与农历双表设计方案可用于日期计算、事件规划、节假日判断及与日期相关的数据分析场景。压缩包内共2个SQL文件均为可直接导入的建表与数据脚本整体约1.07MB分别承载公历日历表与农历日历表的结构定义及预填充数据。公历表包含星期、是否周末、是否节假日等字段农历表则记录农历年月日、月份与日期的中文名称及农历节日标识两表可通过公历日期字段关联一次查询即可同时获取公历与农历信息。目前已有299人学习下载。借助预填充数据读者可省去每次查询时的复杂日期转换直接用于工作日统计、节假日筛选、日期区间计算等业务提升查询效率并简化日期相关功能的开发与维护。1. 一张表管两百年的日历公历农历双表在 MySQL 里到底怎么落地做排班、做考勤、做电商大促倒计时、做金融计息只要业务里出现“日期”两个字早晚会撞上农历。我最近接手一个门店经营分析项目需要按农历初一、十五做客流对比还要在春节、中秋这些节点自动打标。一开始想用 Python 的 lunardate 现算结果每次查询都要跑一遍转换逻辑几百万行数据直接卡死。后来换成把 1900 到 2100 年的公历农历对照关系一次性灌进 MySQL用两张表把“算”变成“查”查询从秒级掉到毫秒级。这份资源就是干这个的一张公历表、一张农历表覆盖 1900-2100 共 201 年导入后直接 JOIN 就能用。适合做数据仓库、报表系统、需要批量处理农历日期的后端同学也适合刚学 MySQL 想找个真实数据练手的。2. 两张表的结构设计字段怎么定、索引怎么加、字符集怎么选2.1 公历表与农历表的字段拆解拿到这份数据表第一件事不是急着导入而是看清楚它为什么拆成两张。公历表存的是标准日期维度农历表存的是农历年月日加节气、生肖、干支这些衍生信息。两张表通过公历日期字段关联这样设计的好处是公历表可以独立服务于普通日期查询农历表只在需要农历信息时才 JOIN避免单表字段过多导致行宽膨胀。常见做法是公历表主键用date类型农历表用自增id加唯一索引solar_date。下面是我整理后的建表语句字段名和类型按实际业务习惯做了调整你可以直接抄-- 公历表以日期为主键存基础维度 CREATE TABLE calendar_solar ( solar_date date NOT NULL COMMENT 公历日期, year smallint NOT NULL COMMENT 公历年, month tinyint NOT NULL COMMENT 公历月, day tinyint NOT NULL COMMENT 公历日, weekday tinyint NOT NULL COMMENT 星期几1周一, week_of_year tinyint NOT NULL COMMENT 年内第几周, is_weekend tinyint NOT NULL DEFAULT 0 COMMENT 是否周末, quarter tinyint NOT NULL COMMENT 季度, PRIMARY KEY (solar_date), KEY idx_year_month (year,month) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;-- 农历表以公历日期为唯一键存农历与节气 CREATE TABLE calendar_lunar ( id int NOT NULL AUTO_INCREMENT, solar_date date NOT NULL COMMENT 对应的公历日期, lunar_year smallint NOT NULL COMMENT 农历年, lunar_month tinyint NOT NULL COMMENT 农历月闰月用负数表示, lunar_day tinyint NOT NULL COMMENT 农历日, is_leap_month tinyint NOT NULL DEFAULT 0 COMMENT 是否闰月, lunar_year_name varchar(16) NOT NULL COMMENT 干支纪年如甲辰, zodiac varchar(8) NOT NULL COMMENT 生肖, solar_term varchar(16) DEFAULT NULL COMMENT 节气无则为NULL, lunar_month_name varchar(16) NOT NULL COMMENT 农历月名如正月, lunar_day_name varchar(16) NOT NULL COMMENT 农历日名如初一, PRIMARY KEY (id), UNIQUE KEY uk_solar_date (solar_date), KEY idx_lunar_ymd (lunar_year,lunar_month,lunar_day) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;逻辑说明公历表用date做主键天然去重且范围查询快农历表用自增 id 做主键、solar_date做唯一索引是因为农历日期本身有闰月重复问题不适合直接做主键。参数上lunar_month用负数表示闰月是常见约定比如 -4 代表闰四月这样查询时不用额外判断is_leap_month就能区分。字符集统一utf8mb4因为干支和生肖字段可能存生僻字utf8会翻车。2.2 索引策略与查询场景匹配索引不是越多越好。这两张表的查询模式无非三种按公历日期查农历、按农历月日查公历、按年份范围拉全年数据。公历表的主键已经覆盖第一种和第三种农历表的uk_solar_date覆盖第一种idx_lunar_ymd覆盖第二种。如果你还要按节气筛选比如“找出所有清明节的日期”那solar_term字段值得再加一个普通索引但注意节气为 NULL 的行很多索引选择性一般加之前先用EXPLAIN看看实际执行计划。我一般会先跑一遍数据分布再决定-- 查看节气字段的填充率决定是否加索引 SELECT COUNT(*) AS total, COUNT(solar_term) AS term_filled, ROUND(COUNT(solar_term) / COUNT(*), 4) AS fill_rate FROM calendar_lunar;如果fill_rate低于 0.05加索引的收益就很有限不如让查询走全表扫描加临时表。这个判断习惯帮我省过好几次不必要的索引维护成本。2.3 数据导入的两种方式与字符集校验数据文件常见格式是 CSV 或 SQL 导出文件。如果是 CSV用LOAD DATA INFILE最快但要注意local_infile参数和字段分隔符。如果是 SQL 文件直接source导入即可。导入前务必确认文件编码是 UTF-8否则干支字段会变乱码。# 先检查文件编码确认是 UTF-8 file -i calendar_lunar.csv # 登录 MySQL 后开启本地导入 SET GLOBAL local_infile 1; # 导入 CSV注意字段顺序要和表结构一致 LOAD DATA LOCAL INFILE /path/calendar_lunar.csv INTO TABLE calendar_lunar FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (solar_date, lunar_year, lunar_month, lunar_day, is_leap_month, lunar_year_name, zodiac, solar_term, lunar_month_name, lunar_day_name);参数说明FIELDS TERMINATED BY ,对应 CSV 逗号分隔ENCLOSED BY 处理字段里含逗号的情况IGNORE 1 ROWS跳过表头。导入后跑一遍SELECT COUNT(*)和SELECT MAX(solar_date), MIN(solar_date)验证行数和范围1900-2100 共 73414 天左右行数对不上就说明有截断或格式错误。3. 农历转换的查询实战JOIN 写法、闰月处理和节气筛选3.1 公历转农历的标准 JOIN 查询最常见的需求是给一个公历日期查出对应的农历。有了这两张表一条 JOIN 就够-- 查询 2025-01-29 对应的农历信息 SELECT s.solar_date, s.weekday, l.lunar_year_name, l.zodiac, l.lunar_month_name, l.lunar_day_name, l.is_leap_month, l.solar_term FROM calendar_solar s JOIN calendar_lunar l ON s.solar_date l.solar_date WHERE s.solar_date 2025-01-29;逻辑说明solar_date是两表的关联键公历表提供星期、季度等维度农历表提供农历和节气。这条查询走的是主键和唯一索引执行计划是eq_ref性能最好。参数上日期用字符串传入 MySQL 会自动转换但建议在应用层就格式化成YYYY-MM-DD避免隐式转换导致索引失效。3.2 闰月查询的坑与正确写法农历最让人头疼的是闰月。比如 2023 年有闰二月那一年会出现两个“二月”。如果直接按lunar_month 2查会把闰二月和正常二月都查出来。正确做法是用is_leap_month区分或者利用lunar_month负数约定。-- 查 2023 年所有二月含闰二月并标注哪个是闰月 SELECT solar_date, lunar_month, lunar_month_name, is_leap_month, lunar_day_name FROM calendar_lunar WHERE lunar_year 2023 AND ABS(lunar_month) 2 ORDER BY solar_date;这里用ABS(lunar_month) 2把正常二月和闰二月都捞出来再用is_leap_month字段区分。如果你只想查正常二月加AND is_leap_month 0只想查闰二月加AND is_leap_month 1。这个写法比用lunar_month 2 OR lunar_month -2更简洁也不容易漏条件。3.3 按节气筛选与批量打标做营销活动时经常要按节气打标比如“清明前后三天”。节气在农历表里是solar_term字段直接筛选即可-- 找出 2020-2025 年所有清明节的公历日期 SELECT solar_date, solar_term FROM calendar_lunar WHERE solar_term 清明 AND solar_date BETWEEN 2020-01-01 AND 2025-12-31 ORDER BY solar_date;如果要批量给订单表打上“是否节气日”的标签用 UPDATE JOIN 比逐行处理快得多-- 给订单表打节气标签假设 orders 表有 order_date 和 is_term_day 字段 UPDATE orders o JOIN calendar_lunar l ON o.order_date l.solar_date SET o.is_term_day IF(l.solar_term IS NOT NULL, 1, 0) WHERE o.order_date BETWEEN 2024-01-01 AND 2024-12-31;注意UPDATE JOIN 在大表上会锁行建议分批执行比如按月切分 WHERE 条件避免长事务把连接池占满。我一般会先SELECT COUNT(*)估算影响行数超过十万就分批。4. 避坑与排查导入、时区、闰月、性能的五个血泪教训4.1 导入后行数对不上多半是换行符或编码问题现象LOAD DATA执行成功但COUNT(*)比预期少几百行。原因CSV 文件在 Windows 下是\r\n换行而LINES TERMINATED BY \n只认\n导致最后一行或跨行字段被截断。解决导入前用dos2unix转一遍或者把LINES TERMINATED BY改成\r\n。编码问题同理file -i确认是utf-8再导。4.2 日期范围查询走不了索引隐式转换在作祟现象WHERE solar_date 2025-1-29这种不补零的写法执行计划变成全表扫描。原因MySQL 把字符串转日期时格式不标准会导致索引失效。解决应用层统一用DATE_FORMAT或编程语言的日期格式化输出YYYY-MM-DD别在 SQL 里写2025-1-29。4.3 闰月判断漏条件统计结果翻倍现象统计“农历二月”的订单量发现比预期多了一倍。原因当年有闰二月查询没加is_leap_month条件两个二月都被算进去。解决所有涉及农历月的查询先确认当年是否有闰月再决定是否加is_leap_month过滤。我现在的习惯是只要 SQL 里出现lunar_month就强制检查is_leap_month。4.4 时区设置不一致跨天查询结果偏移现象应用服务器是 UTCMySQL 是 CST查solar_date CURDATE()时结果对不上。原因CURDATE()取的是 MySQL 服务器时区和应用层时区不一致。解决在连接串里显式设置serverTimezone或者所有日期比较都用应用层传入的字符串不用CURDATE()、NOW()这类函数。4.5 大表 UPDATE JOIN 锁等待超时现象给百万级订单表打节气标签执行到一半报Lock wait timeout exceeded。原因UPDATE JOIN 一次性锁住太多行其他事务排队等锁。解决按主键范围分批比如每次处理一万行循环执行。下面是我常用的分批模板-- 分批打标每次处理 10000 行 UPDATE orders o JOIN calendar_lunar l ON o.order_date l.solar_date SET o.is_term_day IF(l.solar_term IS NOT NULL, 1, 0) WHERE o.id 0 AND o.id 10000 AND o.order_date BETWEEN 2024-01-01 AND 2024-12-31; -- 下一批把 id 范围改成 10001 到 20000依此类推5. 进阶技巧用存储过程生成日期维度表并验证数据完整性5.1 用存储过程批量补全公历表这份资源给的是 1900-2100 的对照数据但公历表里的星期、季度、周数这些字段有时候需要按业务规则重新生成。与其手动算不如写个存储过程一次性刷出来。下面这个存储过程从 1900-01-01 循环到 2100-12-31把公历维度补全DELIMITER $$ CREATE PROCEDURE fill_solar_dimension() BEGIN DECLARE d DATE DEFAULT 1900-01-01; WHILE d 2100-12-31 DO INSERT INTO calendar_solar (solar_date, year, month, day, weekday, week_of_year, is_weekend, quarter) VALUES ( d, YEAR(d), MONTH(d), DAY(d), WEEKDAY(d) 1, WEEK(d, 3), IF(WEEKDAY(d) 5, 1, 0), QUARTER(d) ) ON DUPLICATE KEY UPDATE year VALUES(year), month VALUES(month), day VALUES(day), weekday VALUES(weekday), week_of_year VALUES(week_of_year), is_weekend VALUES(is_weekend), quarter VALUES(quarter); SET d DATE_ADD(d, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL fill_solar_dimension();逻辑说明WEEKDAY(d) 1把 MySQL 的 0周一 转成 1周一WEEK(d, 3)用 ISO 标准算周数模式 3 表示周一为一周起点ON DUPLICATE KEY UPDATE保证重复执行不会报错只会刷新字段。这个存储过程跑完 201 年大概需要几十秒建议在业务低峰期执行。参数上如果你只需要 2000 年之后的数据把起始日期改成2000-01-01即可。5.2 数据完整性验证的三个查询导入完数据别急着上线先跑三个验证查询。第一检查公历表和农历表的日期是否一一对应-- 找出公历表有但农历表没有的日期 SELECT s.solar_date FROM calendar_solar s LEFT JOIN calendar_lunar l ON s.solar_date l.solar_date WHERE l.solar_date IS NULL LIMIT 10;第二检查农历日期范围是否合理-- 农历月应该在 1-12 或 -1 到 -12 之间农历日应该在 1-30 之间 SELECT COUNT(*) AS abnormal FROM calendar_lunar WHERE ABS(lunar_month) NOT BETWEEN 1 AND 12 OR lunar_day NOT BETWEEN 1 AND 30;第三抽查几个已知日期做人工比对。比如 2025 年春节是 1 月 29 日农历正月初一2024 年春节是 2 月 10 日。跑一条查询确认SELECT solar_date, lunar_month_name, lunar_day_name, zodiac FROM calendar_lunar WHERE solar_date IN (2025-01-29, 2024-02-10);如果结果对不上说明数据源有问题别硬上。我一般还会随机抽 10 个日期用在线农历工具交叉验证确认无误才敢让业务方用。5.3 一个让我长记性的习惯这个项目上线前我偷懒没跑完整性验证结果业务方反馈“中秋节的日期标错了”。排查半天发现是导入时 CSV 有一行字段错位导致后面所有日期整体偏移一天。从那以后我每次导入这类对照表都强制走一遍“行数校验 范围校验 抽样比对”三步哪怕数据源看起来再可靠也不跳过。希望这份日历表能帮你省掉我踩过的那些坑直接拿去用。本文还有配套的精品资源点击获取