简介：完整覆盖1970年1月1日至2100年12月31日的万年历MySQL数据库脚本，面向需要日期查询、节假日管理或农历转换功能的应用开发者与数据库学习者。脚本内含建表语句与批量插入语句，表结构包含日期、年、月、日、星期、闰年标识、农历日期及节假日信息等字段，可直接导入MySQL使用，免去手动逐条录入的繁琐工作。资源包共1个文件，为SQL脚本格式，压缩包大小2.25MB，体积精简，便于快速部署到测试或生产环境。目前已有633人学习下载，用户反馈与实际使用场景均可作为参考。对日历类应用、日程管理、历史日期检索等开发需求而言，这份脚本能提供开箱即用的数据基础，同时也有助于初学者理解日期表设计、索引优化及批量数据插入等SQL实践技巧。 提到万年历，多数人想到的是手机上的日历工具，但我今天说的是一个真实跑在 MySQL 里的万年历数据库。简单说，就是把从 1970 年 1 月 1 日到 2100 年 12 月 31 日之间每一天的信息提前生成，存进一张表里。这张表在数据仓库圈子里通常叫日期维度表，什么都用得上：BI 报表的按日汇总、排班系统的班次循环、办公系统的假期判断、电商平台的价格日历，只要业务里出现“按天”统计，这张表就能让 SQL 写得又快又简单。适合正在设计数据层、或者被各种日期函数绕晕的开发同学参考。
我自己的习惯是，遇到要跟“日期”反复打交道的项目，第一件事不是写业务接口，而是先把这张日期表建起来。下面把完整思路、建表语句、插入语句和踩过的坑一次说清楚。
1. 需求拆解：为什么数据库要有一张万年历表
1.1 一张日期表能帮你省掉多少重复代码
先举一个最常见的例子：统计每天订单量。订单表里只有“产生过订单”的日子才有记录，某天没有订单，这条记录就不存在。但你给老板交日报时，他希望看到的是完整的 30 天列表，没数据的那天也要显示 0。如果不用日期表，你就得在代码里造一个日期数组，再左连接订单表，各种循环补齐，麻烦不说，还容易漏掉周末、月末这些边界。有了日期表，一条LEFT JOIN就解决了。
另外像“计算两个日期之间有多少个工作日”“这个月第几个周五是哪天”“上季度一共多少天”这类问题，不用日期表时要用一堆DATE_ADD、DAYOFWEEK、CONCAT去拼，逻辑稍微一复杂就看不懂。把日期提前算好、字段拆好之后，业务 SQL 全都变成简单的WHERE和COUNT，可读性不是提升一点半点。
1.2 数据规模预估：从 1970 到 2100 到底要存多少天
开始建表前，先算清楚数据量，这样才知道索引怎么设计、插入策略怎么选。1970 年到 2100 年共 131 年，其中包含了平年和闰年。不需要手动一年一年数，MySQL 直接算：
SELECT DATEDIFF('2100-12-31', '1970-01-01') + 1 AS total_days;DATEDIFF返回的是两个日期之间的天数差，不包含起始日，所以插入的总行数要加 1。跑出来大概是四万七千多行，具体数字是四万七千八百多。这个量级对 MySQL 来说非常小，一行按三五十字节算，整张表也就两三个 MB，性能和存储完全不用担心。
这也决定了设计思路：不需要分库分表，不需要分区，普通 InnoDB 表加几个索引就够了。
2. 建表语句：这张表的字段并不是越多越好
2.1 完整建表 SQL（可直接使用）
我见过有人把日期表做成几十个字段，农历、天干地支、节气全部塞进去，结果维护成本非常高。我的建议是，核心字段先做精，农历和节假日这类后续要人工维护的数据，用额外字段或辅助表扩展。以下是我在项目里实际用过的结构：
DROP TABLE IF EXISTS date_dim; CREATE TABLE date_dim ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', date_value DATE NOT NULL COMMENT '日期，格式 1970-01-01', year_value SMALLINT UNSIGNED NOT NULL COMMENT '年份，如 2024', month_value TINYINT UNSIGNED NOT NULL COMMENT '月份，1-12', day_value TINYINT UNSIGNED NOT NULL COMMENT '日，1-31', week_value TINYINT UNSIGNED NOT NULL COMMENT '星期几，1=周一，2=周二 ... 7=周日', week_of_year_value TINYINT UNSIGNED NOT NULL COMMENT '当年第几周，ISO 周数', day_of_year_value SMALLINT UNSIGNED NOT NULL COMMENT '当年第几天，1-366', quarter_value TINYINT UNSIGNED NOT NULL COMMENT '季度，1-4', is_weekend TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否周末，0=否 1=是', is_workday TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否工作日，0=否 1=是，默认按周判断', holiday_name VARCHAR(30) DEFAULT NULL COMMENT '节假日名称，如春节、国庆，需手动维护', PRIMARY KEY (id), UNIQUE KEY uk_date_value (date_value), KEY idx_year_value (year_value), KEY idx_month_value (month_value), KEY idx_is_workday (is_workday), KEY idx_week_value (week_value) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '万年历日期维度表，1970-01-01 至 2100-12-31';2.2 字段类型和索引，我是这么选的
日期本身用DATE类型，不要用VARCHAR存。DATE只占 3 个字节，还能直接用 MySQL 的日期函数，查询范围时走索引也更高效。年份用SMALLINT UNSIGNED，2100 年远没超过 65535，够用；月份、日、星期、季度这些用TINYINT UNSIGNED就够了，省空间。
索引方面，date_value必须加唯一索引，这既是业务约束，也是查询效率的保证。year_value和month_value加普通索引，是因为报表里经常按年和月做分组过滤。is_workday索引在排班系统里很好用，如果你暂时用不到，可以删掉，索引不是越多越好。
关于week_value的取值，这里有个容易踩坑的地方：MySQL 的WEEKDAY()函数返回 0 表示周一、6 表示周日，所以存的时候用WEEKDAY(date_value) + 1就能得到我喜欢的前端展示习惯：1=周一，7=周日。如果你在北方公司做国际化项目，团队习惯周日是一周的起点，那就统一用DAYOFWEEK()，但一定要在建表注释里写清楚，避免别人接手时产生误解。
3. 插入语句：三种方案按环境任选
建表只是第一步，真正让这张表有价值的，是把四万八千多天完整插进去。这里提供三种方案：MySQL 8.0 用递归 CTE 最简洁，老版本用存储过程最兼容，用外部程序生成 SQL 文件则最适合需要同时计算农历等复杂字段的场景。
3.1 方案一：MySQL 8.0 递归 CTE 一条 SQL 搞定
如果你的 MySQL 是 8.0 及以上版本，强烈推荐用递归 CTE。代码非常直观，就是从 1970-01-01 开始，一天一天往后生成，直到 2100-12-31 结束：
SET SESSION cte_max_recursion_depth = 50000; INSERT INTO date_dim ( date_value, year_value, month_value, day_value, week_value, week_of_year_value, day_of_year_value, quarter_value, is_weekend, is_workday ) WITH RECURSIVE date_series AS ( SELECT DATE('1970-01-01') AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d < DATE('2100-12-31') ) SELECT d, YEAR(d), MONTH(d), DAY(d), WEEKDAY(d) + 1, WEEKOFYEAR(d), DAYOFYEAR(d), QUARTER(d), IF(WEEKDAY(d) IN (5, 6), 1, 0), IF(WEEKDAY(d) IN (5, 6), 0, 1) FROM date_series;执行时注意最上面的SET SESSION cte_max_recursion_depth = 50000;。MySQL 8.0 对递归 CTE 默认限制是 1000 层，而我们要递归四万多次，不调这个值会直接报错。这一行必须和 INSERT 在同一个会话里执行。
3.2 方案二：存储过程循环插入，兼容老版本
如果是 MySQL 5.7 或更早版本，不支持递归 CTE，那就用存储过程。核心逻辑是一个WHILE循环，和递归 CTE 本质一样，只是写法老派一些：
DROP PROCEDURE IF EXISTS sp_generate_date_dim; DELIMITER $$ CREATE PROCEDURE sp_generate_date_dim() BEGIN DECLARE d DATE DEFAULT '1970-01-01'; DECLARE end_d DATE DEFAULT '2100-12-31'; DECLARE batch_size INT DEFAULT 0; TRUNCATE TABLE date_dim; WHILE d <= end_d DO INSERT INTO date_dim ( date_value, year_value, month_value, day_value, week_value, week_of_year_value, day_of_year_value, quarter_value, is_weekend, is_workday ) VALUES ( d, YEAR(d), MONTH(d), DAY(d), WEEKDAY(d) + 1, WEEKOFYEAR(d), DAYOFYEAR(d), QUARTER(d), IF(WEEKDAY(d) IN (5, 6), 1, 0), IF(WEEKDAY(d) IN (5, 6), 0, 1) ); SET batch_size = batch_size + 1; IF batch_size % 5000 = 0 THEN COMMIT; END IF; SET d = DATE_ADD(d, INTERVAL 1 DAY); END WHILE; COMMIT; END$$ DELIMITER ; CALL sp_generate_date_dim();用存储过程时，建议确认表引擎是 InnoDB，因为 MyISAM 不支持事务，里面的COMMIT不会生效。每插入 5000 条手动提交一次，是为了避免一次性事务太大、undo 日志膨胀。四万八千条数据在普通机器上跑，通常在几秒到十几秒之间，属于正常范围。
3.3 方案三：用外部程序生成批量 INSERT 文件
如果你不只是想生成基础日期字段，还想顺带计算农历、节假日、调休这些，那直接在 SQL 里搞会非常痛苦。我的做法是用 Python 生成一个.sql文件，然后再导入 MySQL：
from datetime import date, timedelta start = date(1970, 1, 1) end = date(2100, 12, 31) current = start rows = [] while current <= end: rows.append( f"('{current.isoformat()}', {current.year}, {current.month}, " f"{current.day}, {(current.weekday() + 1)}, " f"0, {current.timetuple().tm_yday}, 0, 0, 1)" ) current += timedelta(days=1) # 每 500 行拼成一条多值 INSERT，减少 SQL 语句数量 for i in range(0, len(rows), 500): batch = rows[i:i + 500] sql = "INSERT INTO date_dim (...) VALUES " + ",".join(batch) + ";" print(sql)这段只是示例，真实使用的时候把week_of_year_value、quarter_value、is_weekend也算好再拼接。Python 里date.isocalendar()可以拿到 ISO 周数，current.weekday()返回 0 代表周一，跟 MySQL 的WEEKDAY完全对应，生成结果和方案一、二是一致的。
3.4 插入完成后先做这四项校验
不要急着往业务里接，先跑一遍校验 SQL，确认数据没毛病：
-- 1. 总行数 SELECT COUNT(*) AS total_days FROM date_dim; -- 2. 首尾边界 SELECT MIN(date_value) AS min_date, MAX(date_value) AS max_date FROM date_dim; -- 3. 每年天数必须在 365 或 366 之间 SELECT year_value, COUNT(*) AS days FROM date_dim GROUP BY year_value HAVING days NOT IN (365, 366); -- 4. 每月天数抽查 SELECT year_value, month_value, COUNT(*) AS days FROM date_dim WHERE year_value IN (1970, 2000, 2024, 2100) GROUP BY year_value, month_value ORDER BY year_value, month_value;第 4 条是重点。2024 年是闰年，2 月应该有 29 天；2100 年按格里高利历的规则是平年，2 月只能有 28 天。如果插入逻辑里把年份整除 4 就当闰年处理，2100 年就会算错。
4. 性能与踩坑记录：生成 4.8 万行没那么简单
4.1 递归深度限制：默认只能递归 1000 层
很多第一次用递归 CTE 的人，把语句写好一执行，直接报错：
Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.原因就是前面说的，MySQL 8.0 默认递归上限是 1000。四万八千天必须超过这个数，所以执行插入前先设置：
SET SESSION cte_max_recursion_depth = 50000;这里我建议用SESSION而不是GLOBAL，因为GLOBAL会让整个实例所有会话都放开限制，如果哪个递归查询写错了，可能造成不必要的资源消耗。只在当前会话放开就够了，用完退出即恢复默认。
如果你用的连接池是长连接，插入完成之后最好重新连接或把参数调回去，避免这个“宽松配置”一直挂在这条连接上。
4.2 2100 年是不是闰年：日期边界最容易错
这是我在实际项目里犯过的错。生成到 2100 年时，脑子里只记得“四年一闰”，以为 2100 是闰年，结果当年 2 月多算了一天。严格来说，格里高利历的闰年规则是：能被 4 整除但不能被 100 整除，或者能被 400 整除。所以 2000 年是闰年，2100 年反而是平年。
这个坑在手工写YEAR(d) % 4 = 0这类逻辑时尤其容易犯。用 MySQL 自带的DAYOFYEAR(d)和WEEKOFYEAR(d)可以避免自己判断，因为数据库已经按标准规则算好了。
另外，边界条件里如果用WHERE d < '2100-12-31'，会少插一天；如果用WHERE d <= '2100-12-31'，就刚好到 12 月 31 日为止。存储过程的WHILE d <= end_d同理，别把条件写成了<。
4.3 周数边界与节假日字段的坑
WEEKOFYEAR()返回的 ISO 周数有一点要注意：它只返回周数，不代表年份。比如 2021 年 1 月 1 日属于 2020 年的第 53 周，WEEKOFYEAR('2021-01-01')返回 53，但YEAR('2021-01-01')返回 2021。如果你把年份和周数拼成202153这种字符串做周统计，会跟直觉不一致。
要解决这个问题，可以用YEARWEEK(date_value, 3)或者自己存一个year_week_value字段。建表时我没把年周字段放进去，是担心表字段太多反而不清晰；如果业务有“按周报表”的需求，你可以额外加一列，并在插入语句里用YEARWEEK(d, 3)填充，注意YEARWEEK的 mode 参数不同，周起始日也不同，测试时记得先验证。
节假日字段holiday_name我是故意留成手动维护的。因为农历和法定假日的计算非常复杂，不同年份政策还可能变化，指望一条 INSERT 语句自动算全是不现实的。正确做法是：这张表先把日期骨架和基础工作日存好，再用定时任务或人工脚本，把“春节”“国庆”这些特殊日子UPDATE成节假日，并把is_workday改成 0 或 1。这样既保证了基础数据的稳定，也留出了业务灵活性。
5. 万年历表的实际应用玩法
5.1 用一张表搞定工作日和出勤天数统计
日期表建好之后，最舒服的就是写各种统计 SQL。比如计算 2025 年有多少个工作日，原来要写循环，现在一条搞定：
SELECT COUNT(*) FROM date_dim WHERE date_value BETWEEN '2025-01-01' AND '2025-12-31' AND is_workday = 1;再比如做考勤时，想知道 2025 年每月实际出勤天数，按月份分组就行：
SELECT month_value, COUNT(*) AS workday_count FROM date_dim WHERE year_value = 2025 AND is_workday = 1 GROUP BY month_value ORDER BY month_value;如果你已经在系统里维护了法定节假日和调休，还可以把is_workday字段及时更新成真实状态，之后所有涉及工作日的统计都会自动变得准确，业务代码一行都不用改。
5.2 把节假日表挂进来，实现真正的业务日历
单靠一张表打天下也不是不行，但如果团队里有多个业务系统，我更建议在date_dim之外再建一张节假日表：
CREATE TABLE holiday_dim ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, date_value DATE NOT NULL, holiday_name VARCHAR(50) NOT NULL COMMENT '节日名称', is_off_day TINYINT NOT NULL DEFAULT 1 COMMENT '是否放假', PRIMARY KEY (id), UNIQUE KEY uk_holiday_date (date_value) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '法定节假日与调休表';这样date_dim始终保存的是纯日期维度的数据，不会因为每年政策调整而被反复修改。业务上计算工作日时，先把节假日表关联进来，再根据is_off_day把date_dim.is_workday覆盖掉。
这个方案的优点是数据职责分离。日期基础数据是稳定的，节假日数据是易变的，两者分开后，节假日更新不会影响其他系统的日期统计逻辑。我在一个多系统项目里就是这么做的，排班、计费、报表各用各的视图，互不干扰。
最后再分享一个小技巧。生成完四万八千行数据之后，我习惯把date_dim表加一个 mysql dump 备份出来，放到项目的 SQL 初始化目录里。这样新环境部署时，直接执行备份文件就能恢复日期数据，不用每台机器都重新跑一遍插入脚本。如果你团队里有多个开发环境，这个方法能省不少事。日期表这种基础数据，一次性投入，长期受益，值得认真对待。
本文还有配套的精品资源，点击获取
