企业官网建设流程全解析

简介：这份资源提供一套覆盖1900至2100年共200年跨度的MySQL日历数据表，面向需要处理日期计算、事件规划与节假日分析的数据库开发者与后端工程师。包内共2个SQL文件，压缩包约1.07MB，分别对应公历表与农历表：公历表包含日期、星期、是否周末与公历节日等字段，农历表则记录农历年月日、中文月名日名及农历节日，两表可通过公历日期字段关联，一次查询即可同时获取公历与农历信息。借助预填充数据，可避免每次查询时进行复杂的日期转换，提升日期区间统计、工作日与节假日筛选等场景的查询效率。目前已有299人学习下载，适合需要快速搭建日历系统、减少重复造轮子的开发者参考使用。

1. 从一张 1900–2100 的日历表说起：为什么公历和农历要拆成两张 MySQL 表

做排班、考勤、会员生日提醒、节假日营销、财务结账日推算这类业务时，绕不开一个基础问题：日期到底怎么存。很多人第一反应是直接用 MySQL 的DATE类型，需要农历时再临时算。真到线上跑起来才发现，农历不是简单的加减法，它涉及闰月、大小月、节气，靠一个函数现场推，性能和数据一致性都会翻车。我一般会把 1900 到 2100 这 201 年的公历和农历数据一次性落库，拆成两张表：一张公历表负责“每一天是什么日子”，一张农历表负责“农历某年某月某日对应公历哪一天”。这样查询变成索引命中，而不是每次跑算法。

这套方案适合谁？做考勤排班、节假日判断、生日提醒、传统节日营销的开发者，尤其是业务里同时存在公历和农历两套时间口径的团队。它解决的核心痛点是：把“日期换算”从运行时计算变成一次建表、长期查询。下面从表结构设计讲到批量导入，再到查询和踩坑，全部是可复现的操作。

2. 公历表和农历表怎么设计：字段、主键与索引的取舍

2.1 两张表各自存什么

公历表（calendar_solar）的定位是“公历日期字典”，一行代表一天，主键就是公历日期本身。它要能回答：这一天是星期几、是一年中的第几天、是否节假日、对应农历是什么。农历表（calendar_lunar）的定位是“农历日期字典”，一行代表一个农历日，主键是农历年月日加是否闰月，它要能回答：这个农历日期对应公历哪一天。

为什么不合成一张表？因为两张表的查询入口不同。业务按公历查（比如“2025-10-01 是农历几号”）和按农历查（比如“农历八月十五对应哪年哪月哪日”）是两条完全不同的索引路径。合成一张表，另一个方向的查询就得全表扫描。拆开之后，各自的主键就是各自的查询入口，这是最省事的做法。

2.2 建表语句与字段说明

先建公历表。注意solar_date用DATE做主键，lunar_year等字段冗余存一份农历信息，是为了“按公历查农历”时不用回表 join。

CREATE TABLE calendar_solar ( solar_date DATE NOT NULL COMMENT '公历日期，主键', week_day TINYINT NOT NULL COMMENT '星期，1=周一 ... 7=周日', day_of_year SMALLINT NOT NULL COMMENT '一年中的第几天，1-366', is_holiday TINYINT NOT NULL DEFAULT 0 COMMENT '是否法定节假日，0否1是', holiday_name VARCHAR(32) DEFAULT 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 '该农历月是否闰月', PRIMARY KEY (solar_date), KEY idx_lunar (lunar_year, lunar_month, lunar_day) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公历日历表 1900-2100';

再建农历表。主键用(lunar_year, lunar_month, lunar_day, is_leap_month)四列联合，因为农历里同一年可能存在闰月和正常月同月号的情况，不加is_leap_month会主键冲突。

CREATE TABLE calendar_lunar ( 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 DEFAULT 0 COMMENT '是否闰月，0否1是', solar_date DATE NOT NULL COMMENT '对应的公历日期', gan_zhi_year VARCHAR(8) DEFAULT NULL COMMENT '干支纪年，如甲辰', zodiac VARCHAR(4) DEFAULT NULL COMMENT '生肖', PRIMARY KEY (lunar_year, lunar_month, lunar_day, is_leap_month), KEY idx_solar (solar_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='农历日历表 1900-2100';

字段设计上有几个取舍要说清楚。lunar_month在公历表里我用负数表示闰月，比如 -6 代表闰六月，这样单列就能表达闰月，不用再加一列；而农历表里因为主键需要，单独用is_leap_month标记。week_day存 1 到 7 而不是存中文，是为了排序和计算方便，展示时再转。day_of_year冗余存一份，是为了做“年度第 N 天”这类统计时不用DAYOFYEAR()函数，直接走索引。

2.3 为什么不用 MySQL 内置日期函数硬算农历

MySQL 没有内置农历函数。常见做法是用存储过程或应用层算法推算，但农历推算依赖天文数据和历史历法修正，1900 到 2100 这 201 年里有闰月分布不规则、大小月交替的情况，纯算法实现容易在边界年份出错。把结果预先算好落库，查询时只做等值匹配，既避免了算法 bug，也避免了每次查询都跑一遍计算。这是典型的“空间换时间”，201 年也就七万多行，对 MySQL 来说毫无压力。

3. 把 1900–2100 的日历数据灌进 MySQL：生成、导入与校验

3.1 数据从哪来：用 Python 生成 CSV

农历数据不建议手写，也不建议从不明来源直接拷贝。稳妥做法是用成熟的历法库生成，比如 Python 的lunardate或zhdate，逐日遍历 1900-01-31 到 2100-12-31，把公历和农历两边都算出来，写成两个 CSV。下面是一个生成脚本的骨架。

import csv from datetime import date, timedelta from lunardate import LunarDate start = date(1900, 1, 31) # 农历1900年正月初一对应的公历日 end = date(2100, 12, 31) solar_rows = [] lunar_rows = [] d = start while d <= end: ld = LunarDate.fromSolarDate(d.year, d.month, d.day) # 公历表：闰月用负数月份表示 lm = -ld.month if ld.isLeapMonth else ld.month solar_rows.append([ d.isoformat(), d.isoweekday(), # 1=周一 ... 7=周日 d.timetuple().tm_yday, # 一年中第几天 0, None, # 节假日先留空，后续单独维护 ld.year, lm, ld.day, 1 if ld.isLeapMonth else 0 ]) # 农历表 lunar_rows.append([ ld.year, ld.month, ld.day, 1 if ld.isLeapMonth else 0, d.isoformat(), None, None ]) d += timedelta(days=1) with open('calendar_solar.csv', 'w', newline='', encoding='utf-8') as f: csv.writer(f).writerows(solar_rows) with open('calendar_lunar.csv', 'w', newline='', encoding='utf-8') as f: csv.writer(f).writerows(lunar_rows)

逻辑说明：LunarDate.fromSolarDate把每个公历日转成农历日，isLeapMonth标记闰月。公历表里闰月月份存负数，农历表里单独用is_leap_month列。d.isoweekday()返回 1 到 7，正好对应周一到周日。节假日字段先留 0 和 NULL，因为法定节假日每年由安排另行发布，不适合写死在生成脚本里。

参数说明：起始日必须是农历 1900 年正月初一对应的公历日，早于这个日期很多历法库不支持。结束日 2100-12-31 是常见历法库的可靠范围上界。如果你的库只支持到 2099，就把end相应前移，别硬撑。

3.2 用 LOAD DATA 批量导入

CSV 生成后，用LOAD DATA LOCAL INFILE导入，比逐条 INSERT 快几个数量级。注意先确认 MySQL 客户端和服务端都开启了local_infile。

-- 导入公历表 LOAD DATA LOCAL INFILE '/path/calendar_solar.csv' INTO TABLE calendar_solar FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (solar_date, week_day, day_of_year, is_holiday, holiday_name, lunar_year, lunar_month, lunar_day, is_leap_month); -- 导入农历表 LOAD DATA LOCAL INFILE '/path/calendar_lunar.csv' INTO TABLE calendar_lunar FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (lunar_year, lunar_month, lunar_day, is_leap_month, solar_date, gan_zhi_year, zodiac);

逻辑说明：字段顺序必须和 CSV 列顺序严格一致，否则数据会错位。ENCLOSED BY '"'处理可能含逗号的文本字段。LINES TERMINATED BY '\n'在 Linux 下没问题，Windows 生成的 CSV 要改成'\r\n'。

参数说明：如果导入报ERROR 1148或local_infile相关错误，检查服务端local_infile=ON，客户端连接时加--local-infile=1。数据量大时可以先SET autocommit=0再导入，最后统一提交，减少日志刷盘。

3.3 导入后必须做的三项校验

导入完不要直接上业务，先校验。第一，行数校验：公历表应该是 1900-01-31 到 2100-12-31 的天数，农历表行数应与之相等。第二，主键冲突校验：如果导入时没报错，说明主键没冲突，但仍要抽查闰月记录。第三，双向一致性校验：随机抽若干公历日，用公历表里的农历字段去农历表反查，看solar_date是否对得上。

-- 行数校验 SELECT COUNT(*) FROM calendar_solar; SELECT COUNT(*) FROM calendar_lunar; -- 抽查闰月：农历表里闰月记录数 SELECT COUNT(*) FROM calendar_lunar WHERE is_leap_month = 1; -- 双向一致性：公历表某天的农历，去农历表反查公历 SELECT s.solar_date, s.lunar_year, s.lunar_month, s.lunar_day, l.solar_date AS lunar_side_solar FROM calendar_solar s JOIN calendar_lunar l ON l.lunar_year = s.lunar_year AND l.lunar_month = ABS(s.lunar_month) AND l.lunar_day = s.lunar_day AND l.is_leap_month = s.is_leap_month WHERE s.solar_date = '2025-10-06';

逻辑说明：最后一条查询用公历表的农历字段去农历表反查，如果solar_date和lunar_side_solar一致，说明两张表数据自洽。ABS(s.lunar_month)是因为公历表里闰月存负数。

参数说明：抽查日期建议选有闰月的年份，比如 2025 年有闰六月，2023 年有闰二月，这些是容易出错的点。

4. 查询怎么写才快：公历查农历、农历查公历与节假日判断

4.1 公历查农历：走主键，一步到位

最常见的需求是“给我公历日期，返回农历和星期”。因为公历表主键就是solar_date，这条查询是主键等值命中，速度极快。

SELECT solar_date, week_day, lunar_year, lunar_month, lunar_day, is_leap_month FROM calendar_solar WHERE solar_date = '2025-10-06';

逻辑说明：lunar_month返回负数时表示闰月，应用层展示时判断正负即可。week_day是 1 到 7，展示时映射成中文。

参数说明：如果业务要批量查一段日期，用BETWEEN范围扫描主键，不要循环单条查。

SELECT solar_date, lunar_month, lunar_day FROM calendar_solar WHERE solar_date BETWEEN '2025-10-01' AND '2025-10-07' ORDER BY solar_date;

4.2 农历查公历：联合主键的最左前缀

“农历八月十五是哪天”这类查询，走农历表的联合主键。注意联合主键是(lunar_year, lunar_month, lunar_day, is_leap_month)，查询时按最左前缀给条件。

SELECT solar_date FROM calendar_lunar WHERE lunar_year = 2025 AND lunar_month = 8 AND lunar_day = 15 AND is_leap_month = 0;

逻辑说明：四个条件全给，直接命中主键。如果只给lunar_month和lunar_day不给年份，联合主键用不上最左列，会退化成扫描，所以业务上尽量带上年份。

参数说明：is_leap_month一定要显式给 0 或 1，不要省略。省略时如果该年该月既有正常月又有闰月，会返回两行，业务逻辑容易出错。

4.3 节假日判断与排序场景

节假日判断建议单独维护一张holiday表，或者定期更新calendar_solar.is_holiday。不要试图用农历字段推导法定节假日，因为调休安排每年不同。判断某天是否节假日：

SELECT solar_date, is_holiday, holiday_name FROM calendar_solar WHERE solar_date = '2025-10-01';

排序场景，比如按农历生日给会员排序，用农历表的联合主键排序即可：

SELECT lunar_year, lunar_month, lunar_day, solar_date FROM calendar_lunar WHERE is_leap_month = 0 ORDER BY lunar_month, lunar_day LIMIT 100;

逻辑说明：按农历月日排序时，闰月记录通常排除，避免同一个月出现两次。ORDER BY走的是主键前缀，效率可以接受。

参数说明：如果数据量大且排序频繁，可以考虑在(lunar_month, lunar_day)上单独建索引，但 201 年数据量小，一般没必要。

5. 避坑与排查：日历表落地时最容易翻车的五个点

5.1 闰月主键冲突，导入直接失败

现象：导入农历表时报Duplicate entry，主键冲突。原因：农历同一年可能存在闰月和正常月同月号，比如闰六月和六月都有十五，如果主键不含is_leap_month，两行主键相同。解决：主键必须包含is_leap_month，四列联合，导入前确认 CSV 里这一列有值且是 0 或 1。

5.2 公历表闰月存负数，查询时忘了取绝对值

现象：按公历查农历正常，但用公历表的lunar_month去 join 农历表时查不到数据。原因：公历表里闰月存的是负数（如 -6），农历表里存的是正数加is_leap_month=1，直接等值 join 对不上。解决：join 时用ABS(s.lunar_month)并同时匹配is_leap_month，如第 3.3 节的校验 SQL 所示。

5.3 LOAD DATA 报 local_infile 相关错误

现象：执行LOAD DATA LOCAL INFILE报ERROR 1148或提示local_infile未开启。原因：MySQL 8.0 之后local_infile默认关闭，客户端和服务端都要开。解决：服务端在配置文件里设local_infile=ON并重启，客户端连接时加--local-infile=1。用 Docker 部署 MySQL 时，注意把 CSV 文件挂载进容器，路径写容器内路径。

5.4 日期范围边界算错，少一天或多一天

现象：行数校验时发现公历表行数和预期差一天。原因：起始日或结束日的while条件写成了<而不是<=，或者起始日选错。解决：起始日必须是农历 1900 年正月初一对应的公历日，结束日用<=包含当天。生成后先打印首尾两行确认。

5.5 用农历字段推导节假日，调休对不上

现象：系统判断某天是节假日，但实际是调休上班日。原因：法定节假日和调休安排每年由安排发布，不是农历或公历能推导的。解决：节假日单独维护，定期更新is_holiday和holiday_name，不要用农历日期硬编码判断。

6. 进阶：用存储过程做批量换算与一个校验技巧

如果业务里经常需要“给一批公历日期，批量返回农历”，循环单条查虽然能跑，但连接开销大。常见做法是写一个存储过程，接收日期区间，一次性返回结果集。下面这个存储过程接收起止日期，返回区间内每天的农历信息。

DELIMITER $$ CREATE PROCEDURE sp_solar_range_lunar( IN p_start DATE, IN p_end DATE ) BEGIN SELECT solar_date, week_day, lunar_year, CASE WHEN lunar_month < 0 THEN -lunar_month ELSE lunar_month END AS lunar_month, lunar_day, is_leap_month FROM calendar_solar WHERE solar_date BETWEEN p_start AND p_end ORDER BY solar_date; END $$ DELIMITER ;

逻辑说明：CASE WHEN把公历表里的负数闰月转成正数展示，同时保留is_leap_month标记，应用层不用再处理正负。BETWEEN走主键范围扫描。

参数说明：p_start和p_end必须落在 1900-01-31 到 2100-12-31 之间，超出范围查不到数据。调用方式：CALL sp_solar_range_lunar('2025-10-01', '2025-10-07');。注意DELIMITER是客户端指令，在 Navicat、MySQL Workbench 里都能用，但在某些 JDBC 场景下要拆开执行。

再给一个校验技巧：把两张表的行数、闰月数、首尾日期做成一张校验结果表，每次导入后跑一遍，比人工抽查靠谱。

SELECT 'solar_count' AS item, COUNT(*) AS val FROM calendar_solar UNION ALL SELECT 'lunar_count', COUNT(*) FROM calendar_lunar UNION ALL SELECT 'leap_count', COUNT(*) FROM calendar_lunar WHERE is_leap_month = 1 UNION ALL SELECT 'solar_min', CAST(MIN(solar_date) AS CHAR) FROM calendar_solar UNION ALL SELECT 'solar_max', CAST(MAX(solar_date) AS CHAR) FROM calendar_solar;

逻辑说明：UNION ALL把多个校验指标拼成一个结果集，一眼看完。CAST是因为UNION要求各列类型兼容，日期转成字符串。

参数说明：leap_count的预期值可以提前用生成脚本算出来，导入后对比。如果对不上，说明闰月数据有问题，回去查 CSV。

我自己踩过的教训是：一开始图省事，把农历字段全塞进公历表，结果按农历查生日时全表扫描，数据量一大就慢得没法看。后来拆成两张表，各自主键就是各自入口，查询稳定在毫秒级。日历表这种东西，建表时多花半小时想清楚主键和索引，比上线后加索引、改 SQL 省事得多。希望帮到你。

本文还有配套的精品资源，点击获取