企业官网建设流程全解析

简介：这份资源提供了一套覆盖1970年1月1日至2100年12月31日的万年历MySQL数据库脚本，面向需要处理跨世纪日期查询的开发者、日历应用与时间计算类项目。包内共1个SQL文件，压缩包约2.25MB，内容包含完整的建表DDL与批量插入DML语句，可直接导入MySQL使用。表结构围绕日期、年、月、日、星期、闰年标识、农历日期及节假日信息等字段设计，并给出主键与索引优化思路，便于快速检索特定日期或节日。对于日历工具、节假日管理、历史事件检索等场景，读者可借此省去自行推算与录入海量日期的工作，直接获得可运行的数据基础，同时参考其字段设计与农历转换处理方式，理解大规模日期数据在关系型数据库中的组织方法。目前已有636人学习下载，适合具备基础SQL能力、希望快速搭建日期数据支撑的开发者参考使用。

1. 万年历数据库：从1970到2100，一张表扛住两万天查询

很多做排班、考勤、金融计息、节假日判断的系统，最后都会撞上同一堵墙：业务代码里到处散落着if 周末、if 法定节假日、if 调休上班，改一次放假安排就要发一次版。更麻烦的是跨年查询，比如算一笔从 2023 年 1 月 15 日到 2027 年 9 月 30 日的账期，中间夹着多少个工作日、多少个自然日、有没有跨闰年，全靠临时拼 SQL，性能差还容易算错。

万年历数据库就是把这堵墙一次性拆掉：把 1970-01-01 到 2100-12-31 这 47847 天的日期属性，全部预先算好落进一张 MySQL 表。之后所有跟日期相关的判断，都退化成一次WHERE date BETWEEN ...的范围查询。它适合做考勤系统、排班引擎、计息模块、报表平台的人，也适合任何被日期逻辑反复折磨的后端。下面这套建表和插入方案，是我在几个模拟项目里反复用过的版本，字段不多，但够用。

2. 表结构怎么设计：字段取舍与索引策略

2.1 为什么主键用 date 而不是自增 id

日期本身就是天然唯一键，用DATE类型做主键有三个好处：一是范围查询直接走聚簇索引，BETWEEN扫描是顺序 IO；二是避免自增 id 和业务日期之间再做一次映射；三是数据量固定，47847 行不会增长，主键页分裂几乎不存在。

常见做法是主键用date，再给year、month建普通索引，方便按月、按年聚合。不要给每个字段都建索引，这张表读多写零，索引多了反而占空间。

CREATE TABLE `calendar` ( `date` DATE NOT NULL COMMENT '公历日期，主键', `year` SMALLINT UNSIGNED NOT NULL COMMENT '年份', `month` TINYINT UNSIGNED NOT NULL COMMENT '月份 1-12', `day` TINYINT UNSIGNED NOT NULL COMMENT '日 1-31', `weekday` TINYINT UNSIGNED NOT NULL COMMENT '星期，1=周一 ... 7=周日', `week_of_year` TINYINT UNSIGNED NOT NULL COMMENT '年内第几周 1-53', `day_of_year` SMALLINT UNSIGNED NOT NULL COMMENT '年内第几天 1-366', `is_weekend` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否周末 1是0否', `is_holiday` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否法定节假日 1是0否', `is_workday` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否工作日（含调休）1是0否', `holiday_name` VARCHAR(32) DEFAULT NULL COMMENT '节假日名称', `quarter` TINYINT UNSIGNED NOT NULL COMMENT '季度 1-4', `lunar_year` SMALLINT UNSIGNED DEFAULT NULL COMMENT '农历年', `lunar_month` TINYINT UNSIGNED DEFAULT NULL COMMENT '农历月', `lunar_day` TINYINT UNSIGNED DEFAULT NULL COMMENT '农历日', `lunar_text` VARCHAR(32) DEFAULT NULL COMMENT '农历中文描述', `solar_term` VARCHAR(16) DEFAULT NULL COMMENT '节气名称', PRIMARY KEY (`date`), KEY `idx_year_month` (`year`,`month`), KEY `idx_workday` (`is_workday`), KEY `idx_holiday` (`is_holiday`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='万年历基础表 1970-2100';

字段说明：weekday用 1 到 7 表示周一到周日，比 MySQL 原生DAYOFWEEK（1=周日）更符合国内排班习惯，避免每次查询都要做偏移换算。is_workday和is_weekend分开存，是因为调休会把某个周六变成工作日，两个字段不能互相推导。lunar_*和solar_term允许为空，如果业务不需要农历，可以整列删掉，插入脚本里对应部分跳过即可。

2.2 生成 47847 行日期的三种思路

第一种是纯 SQL 递归，MySQL 8.0 支持WITH RECURSIVE，一条语句就能生成日期序列。第二种是写脚本（Python/Java）算好再批量插入。第三种是借助已有的数字辅助表做笛卡尔积。

我一般用递归 CTE 生成日期骨架，再用存储过程或外部脚本补农历和节假日。原因是日期骨架纯 SQL 最快，农历计算涉及复杂天文算法，放脚本里更好维护。

-- MySQL 8.0 递归生成日期骨架并插入 INSERT INTO calendar (date, year, month, day, weekday, week_of_year, day_of_year, is_weekend, quarter) WITH RECURSIVE seq AS ( SELECT DATE('1970-01-01') AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM seq WHERE d < '2100-12-31' ) SELECT d, YEAR(d), MONTH(d), DAY(d), WEEKDAY(d) + 1, -- WEEKDAY: 0=周一，+1 后 1=周一 WEEK(d, 3), -- 模式3：周一为一周起点，范围1-53 DAYOFYEAR(d), IF(WEEKDAY(d) >= 5, 1, 0), -- 5=周六 6=周日 QUARTER(d) FROM seq;

逻辑说明：递归 CTE 从 1970-01-01 开始，每次加一天，直到 2100-12-31。WEEKDAY()返回 0 到 6 表示周一到周日，加 1 后变成 1 到 7。WEEK(d,3)的第二个参数 3 表示周一为每周第一天、范围 1 到 53，和国内习惯一致。IF(WEEKDAY(d) >= 5, 1, 0)判断周末。

参数注意：递归 CTE 默认cte_max_recursion_depth是 1000，47847 行会直接报错。执行前必须先调大：

SET SESSION cte_max_recursion_depth = 100000;

这个坑我第一次就踩了，报错信息是Recursive query aborted after 1001 iterations，找半天才发现是会话变量限制。

2.3 批量插入的性能与事务控制

47847 行一次性插入，如果 autocommit 开着，会触发 47847 次事务提交，慢到怀疑人生。正确做法是显式开事务，或者用INSERT ... SELECT一条语句搞定（上面就是）。如果非要分批，每批 5000 行左右，批间不要提交，最后统一 commit。

START TRANSACTION; -- 分批插入示例：假设已生成临时表 tmp_calendar INSERT INTO calendar (date, year, month, day, weekday, week_of_year, day_of_year, is_weekend, quarter) SELECT date, year, month, day, weekday, week_of_year, day_of_year, is_weekend, quarter FROM tmp_calendar WHERE date BETWEEN '1970-01-01' AND '2100-12-31'; COMMIT;

插入完成后用SELECT COUNT(*) FROM calendar;验证，结果应该是 47847。如果少了，大概率是递归深度不够或者日期边界写错。可以用SELECT MIN(date), MAX(date) FROM calendar;确认首尾日期。

3. 农历、节气与节假日：最难啃的三块骨头

3.1 农历转换为什么不能纯 SQL 搞定

农历是阴阳合历，月份长度根据月相定，闰月位置由二十四节气决定，本质是一套天文历算。纯 SQL 实现农历转换，代码量巨大且容易出错。常见做法是准备一张农历数据压缩表，每年用一个整数编码 12 或 13 个月的大小月和闰月信息，再用脚本展开。

我一般用 Python 的lunardate或zhdate这类库算好，再回写数据库。下面是一个回写农历字段的示例结构：

# 伪代码示意：遍历日期，计算农历并批量更新 import datetime from lunardate import LunarDate start = datetime.date(1970, 1, 1) end = datetime.date(2100, 12, 31) batch = [] d = start while d <= end: lunar = LunarDate.fromSolarDate(d.year, d.month, d.day) batch.append((lunar.year, lunar.month, lunar.day, str(lunar), d)) if len(batch) >= 2000: cursor.executemany( "UPDATE calendar SET lunar_year=%s, lunar_month=%s, lunar_day=%s, lunar_text=%s WHERE date=%s", batch ) conn.commit() batch = [] d += datetime.timedelta(days=1)

逻辑说明：逐日计算农历，攒够 2000 条批量更新一次。lunar_text存中文描述如「正月初一」，方便前端直接展示。参数上，批量大小 2000 是折中值，太大占内存，太小提交频繁。

注意：农历库对 1970 年之前的支持可能不完整，但我们的范围从 1970 开始，正好在安全区间。2100 年附近的农历数据也要验证，部分库的压缩表只到 2099 年，需要确认边界。

3.2 节气字段的填充与验证

二十四节气本质是太阳黄经每 15 度一个节点，可以用天文算法算，也可以查表。工程上更稳的是用现成的节气表，按年导入。节气对农业、养生类应用有意义，普通排班系统可以留空。

-- 节气更新示例：假设已有节气临时表 tmp_solar_term(date, term_name) UPDATE calendar c JOIN tmp_solar_term t ON c.date = t.date SET c.solar_term = t.term_name;

验证方法：抽查几个已知节气日期，比如 2024 年立春是 2 月 4 日，清明是 4 月 4 日，用SELECT date, solar_term FROM calendar WHERE solar_term IS NOT NULL AND year = 2024;看结果是否吻合。

3.3 节假日和调休的维护策略

法定节假日每年由相关部门发布，调休安排也年年不同。这张表里is_holiday和is_workday不能靠算法推导，只能人工维护或导入官方安排。常见做法是单独建一张holiday_config表，每年更新一次，再用 JOIN 回写calendar。

-- 节假日配置表 CREATE TABLE holiday_config ( date DATE NOT NULL PRIMARY KEY, is_holiday TINYINT(1) NOT NULL DEFAULT 0, is_workday TINYINT(1) NOT NULL DEFAULT 1, holiday_name VARCHAR(32) DEFAULT NULL ); -- 回写主表 UPDATE calendar c JOIN holiday_config h ON c.date = h.date SET c.is_holiday = h.is_holiday, c.is_workday = h.is_workday, c.holiday_name = h.holiday_name;

这样设计的好处是：主表只读，节假日配置独立维护，每年只需更新配置表再跑一次回写。调休导致的周末上班，通过is_workday=1覆盖默认的周末判断。

4. 避坑与排查：五个让我返工的血泪教训

4.1 递归深度不够导致插入中断

现象：执行递归 CTE 插入时，报错Recursive query aborted after 1001 iterations，表里只有 1000 行。 原因：MySQL 默认cte_max_recursion_depth是 1000，而我们需要 47847 次迭代。 解决：执行前SET SESSION cte_max_recursion_depth = 100000;，或者改用存储过程循环插入。

4.2 时区设置导致日期偏移

现象：插入的日期比预期少一天或多一天，尤其是跨月边界。 原因：MySQL 的DATE类型本身不带时区，但如果用NOW()或CURDATE()参与计算，会受time_zone影响。 解决：生成日期时全部用字面量DATE('1970-01-01')，不要用CURDATE()。连接串里显式设置serverTimezone=Asia/Shanghai。

4.3 批量插入未提交事务导致锁等待

现象：插入过程中其他查询被阻塞，或者插入本身很慢。 原因：autocommit 关闭但忘记 commit，或者批量太大导致 undo log 膨胀。 解决：显式START TRANSACTION和COMMIT，每批 2000 到 5000 行。插入期间避免其他写操作。

4.4 农历字段更新时主键冲突

现象：更新农历字段时报Duplicate entry或更新行数为 0。 原因：批量更新时日期格式不匹配，比如 Python 的datetime.date和 MySQL 的DATE比较时隐式转换失败。 解决：统一用'YYYY-MM-DD'字符串格式传参，或者在 SQL 里用DATE(%s)显式转换。

4.5 索引过多导致插入变慢

现象：插入 47847 行花了十几分钟。 原因：除了主键，还建了五六个二级索引，每插一行都要更新所有索引。 解决：先建表只留主键，插入完成后再ALTER TABLE ADD INDEX补索引。索引重建比逐行维护快得多。

5. 查询实战：用这张表把日期逻辑从代码里赶出去

5.1 算两个日期之间的工作日天数

这是考勤和计息系统最高频的查询。有了is_workday字段，一条 SQL 搞定：

SELECT COUNT(*) AS workday_count FROM calendar WHERE date BETWEEN '2024-01-01' AND '2024-12-31' AND is_workday = 1;

对比传统做法：要么在代码里循环遍历日期逐个判断，要么写复杂的 SQL 排除周末再减去节假日再加上调休。前者慢，后者容易漏掉调休。这张表把判断成本前置到建表阶段，查询时只做一次范围扫描。

5.2 按月统计节假日分布

SELECT month, COUNT(*) AS holiday_days FROM calendar WHERE year = 2024 AND is_holiday = 1 GROUP BY month ORDER BY month;

这个查询走idx_year_month索引，47847 行里过滤出 2024 年再聚合，毫秒级返回。如果业务需要按季度、按周统计，quarter和week_of_year字段直接可用，不用再写QUARTER()函数。

5.3 找下一个工作日

排班系统经常需要「从某天开始往后找最近的工作日」：

SELECT date FROM calendar WHERE date > '2024-10-01' AND is_workday = 1 ORDER BY date LIMIT 1;

走主键索引，ORDER BY date天然有序，LIMIT 1找到即停，性能极好。如果反过来找上一个工作日，把>改成<，ORDER BY date DESC即可。

5.4 跨年账期的自然日与工作日对比

SELECT COUNT(*) AS total_days, SUM(is_workday) AS workdays, SUM(is_weekend) AS weekends, SUM(is_holiday) AS holidays FROM calendar WHERE date BETWEEN '2023-01-15' AND '2027-09-30';

一条查询同时拿到四个指标，不用在代码里做多次遍历。SUM(is_workday)利用了 0/1 字段的特性，比COUNT(CASE WHEN ...)更简洁。

5.5 验证数据完整性的三个检查

建完表、插完数据，别急着上线，先跑这三个检查：

-- 检查1：总行数是否为47847 SELECT COUNT(*) FROM calendar; -- 检查2：首尾日期是否正确 SELECT MIN(date), MAX(date) FROM calendar; -- 检查3：是否有重复日期（主键保证不会，但可验证） SELECT date, COUNT(*) FROM calendar GROUP BY date HAVING COUNT(*) > 1;

如果检查1结果不是 47847，用SELECT YEAR(date), COUNT(*) FROM calendar GROUP BY YEAR(date) HAVING COUNT(*) != 365 AND COUNT(*) != 366;找出异常年份。闰年 366 天，平年 365 天，1970 到 2100 之间闰年数量是固定的，可以提前算好对照。

最后说个习惯：我每次建完这类基础表，都会把生成脚本和校验 SQL 一起存进版本库，下次换项目直接改年份范围就能复用。日期逻辑这种东西，写一次对一次，比每次临时拼 SQL 靠谱得多。希望帮到你。

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