简介：一份覆盖1970年1月1日至2100年12月31日共131年的完整万年历MySQL数据库SQL资源，适合日历应用、时间计算、节假日管理及历史日期检索等多类开发场景。资源内附单个.sql文件，包含建表DDL语句与全量数据插入DML语句，表结构覆盖公历日期、年份、月份、星期、闰年标识、农历日期及节假日信息等字段，可直接作为日期维度表导入使用，无需额外编写农历转换逻辑。压缩包仅2.25MB，文件总数1个，sql类型，轻量易用，便于快速部署到现有数据库环境。目前已有633人学习下载，适合需要快速搭建日期基础数据的后端开发、数据分析及MySQL学习者参考。通过该资源可省去自行推算农历与节假日的复杂算法，直接获得结构清晰、覆盖131年的标准SQL脚本，便于二次开发、字段扩展或参考设计。
1. 为什么你需要一张万年历表，而不是每次现算日期
做业务系统做得久了，你会发现一个特别普遍的需求：用户下单要选日期、报表要按月份汇总、排班系统要算星期、考勤系统要区分工作日和节假日。很多开发者的第一反应是“用代码现算呗”，Java里有LocalDate，MySQL里有DATE_ADD、DAYOFWEEK这些函数，感觉没有必要专门建一张日历表。但实际上，当你真的开始写这些逻辑时，会遇到几个很现实的问题。
第一个是代码里到处散落着日期计算的逻辑。今天要判断是不是周末，明天要判断这是第几季度，后天要算两个日期之间隔了多少个工作日。每个地方都写一遍DateUtil，维护起来相当痛苦。第二个是节假日完全没法算。春节每年日期都不一样，国庆节有调休，这些都不是简单的函数能解决的，需要一张表把“哪天放假、哪天补班”这种业务数据存下来。第三个是报表查询的性能问题。当你需要按自然日做统计，而订单表里又没有日期维表时，GROUP BY DATE(order_time)勉强能用，但一旦要统计“每个工作日 vs 周末的订单量”，没有日历表帮忙关联，SQL会写得非常绕。
这张万年历表就像一个“日期字典”，把所有日期相关的维度和标记都提前算好、存好，业务查询直接JOIN这张表就能拿到结果。我之前在做一个考勤系统时就深有体会：没有日历表之前，排班和节假日判断的代码改了又改；有了日历表之后，所有跟日期有关的查询都变得极其清爽。这次我整理了一套完整的MySQL建表语句和插入语句，数据范围从1970年1月1日到2100年12月31日，一共47838天，一次性把全年、月、日、星期、季度、工作日、节假日这些信息全量生成好，你拿到就能直接用。
2. 表结构设计：一个能撑起业务查询的日期字典该有哪些字段
万年历表的设计核心，不是“能查出日期就行”，而是“让业务查询不需要再写复杂的日期函数”。所以字段设计要覆盖绝大多数场景：自然日期本身、年、月、日、星期、季度、上半年/下半年、是否工作日、是否节假日。我最终设计的表结构如下：
CREATE TABLE calendar ( date_value DATE NOT NULL COMMENT '日期，主键', year_value SMALLINT NOT NULL COMMENT '年份，如2024', month_value TINYINT NOT NULL COMMENT '月份，1-12', day_value TINYINT NOT NULL COMMENT '日，1-31', week_value TINYINT NOT NULL COMMENT '星期几，1=周一, 7=周日', week_name VARCHAR(10) NOT NULL COMMENT '星期名称，如星期一', quarter_value TINYINT NOT NULL COMMENT '季度，1-4', half_year TINYINT NOT NULL COMMENT '上半年/下半年：1上半年，2下半年', day_of_year SMALLINT NOT NULL COMMENT '当年第几天，1-366', is_weekend TINYINT NOT NULL DEFAULT 0 COMMENT '是否周末：0否，1是', is_workday TINYINT NOT NULL DEFAULT 0 COMMENT '是否工作日：0否，1是（不含法定节假日调整）', is_holiday TINYINT NOT NULL DEFAULT 0 COMMENT '是否法定节假日：0否，1是', workday_mark VARCHAR(50) DEFAULT NULL COMMENT '工作日备注：如调休补班、春节假期等', PRIMARY KEY (date_value), KEY idx_year_month (year_value, month_value), KEY idx_is_workday (is_workday) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='万年历数据表，1970-2100年';每个字段都不是拍脑袋定的，都是踩过坑之后补上的。
date_value 用 DATE 类型做主键，日期本身精度到天就够用了，不需要带时分秒，这样查询效率最高，也天然避免重复插入。year_value 用 SMALLINT 而不是 INT，因为2100年完全够用，SMALLINT 只占2字节，能省一点算一点。is_weekend 和 is_workday 我分了两个字段，因为“周末”和“非工作日”并不是一回事——当法定节假日调休时，周六可能补班变成工作日，周日可能休息。如果只存一个 is_workday 字段，后面处理节假日调休时就要频繁 UPDATE 这个字段，而很多历史报表已经引用这个字段了，改动影响面会很大。所以我把“是否周末”这种固定属性，和“是否工作日”这种业务属性分开存。
workday_mark 这个字段是后来加上的，特别有用。比如“国庆节调休补班”“春节假期”这些文字说明，直接放在表里，做节假日提醒时拿出来就是现成的展示数据。
注意：is_workday 字段我这里标注的是“不含法定节假日调整”，也就是说它只按周一至周五判定。如果你需要真正的“工作日”（排除法定节假日并包含调休补班），建议在应用中额外维护一张节假日配置表，或者在本表基础上做 UPDATE 调整。这个设计取舍后面会详细说。
字符集用了 utf8mb4，因为 workday_mark 字段要存中文备注，utf8mb4 是 MySQL 8.0 的默认字符集，兼容性最好。如果你的库整体用的还是 utf8，这里用 utf8 也没问题，但如果想要绝对稳妥，还是统一 utf8mb4 更好。
3. 全量数据生成：用存储过程一次搞定1970到2100年的4.8万条记录
表结构设计好之后，最核心的问题就是：47838天的数据怎么填进去？理论上可以写一个程序生成INSERT语句，但为了让这套方案不依赖任何外部工具，我选择了 MySQL 存储过程作为生成方案。直接在数据库里循环插入，不涉及任何外部文件，拿过去就能执行。
这里要说明一下，存储过程一次性插入47838条记录，在 MySQL 8.0 上执行时间可能从十几秒到一分钟不等，取决于机器性能，但只要执行完成，后续查询都是毫秒级响应。
DELIMITER $$ CREATE PROCEDURE fill_calendar(IN start_date DATE, IN end_date DATE) BEGIN DECLARE cur_date DATE DEFAULT start_date; DECLARE day_interval INT DEFAULT 0; WHILE cur_date <= end_date DO INSERT INTO calendar ( date_value, year_value, month_value, day_value, week_value, week_name, quarter_value, half_year, day_of_year, is_weekend, is_workday, is_holiday, workday_mark ) VALUES ( cur_date, YEAR(cur_date), MONTH(cur_date), DAY(cur_date), DAYOFWEEK(cur_date), ELT(DAYOFWEEK(cur_date), '星期日', '星期一', '星期二', '星期三', '星期四', '星期五', '星期六'), QUARTER(cur_date), IF(MONTH(cur_date) <= 6, 1, 2), DAYOFYEAR(cur_date), IF(DAYOFWEEK(cur_date) IN (1, 7), 1, 0), IF(DAYOFWEEK(cur_date) IN (1, 7), 0, 1), 0, NULL ); SET cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY); SET day_interval = day_interval + 1; IF day_interval % 1000 = 0 THEN SELECT CONCAT('Inserted ', day_interval, ' rows') AS progress; END IF; END WHILE; SELECT CONCAT('Finished. Total rows: ', day_interval) AS result; END$$ DELIMITER ;然后执行：
CALL fill_calendar('1970-01-01', '2100-12-31');核心逻辑就是用一个 WHILE 循环，从起始日期逐天递增，每循环一次插入一条记录。这里有几个函数值得注意。
DAYOFWEEK 返回的是 1到7，其中 1=星期日，7=星期六，跟国内习惯“周一是一周第一天”正好反着。所以我在存星期几的时候，直接用 DAYOFWEEK 的值，但在 week_name 字段里用 ELT 函数做了映射，把 1 对应成“星期日”。这样查询时，如果你只关心是不是周末，看 week_value 是否等于 1 或 7 就行；如果想展示给用户看，直接用 week_name 字段，干净漂亮，不需要在代码里再转换一遍。
is_workday 我用了一个非常朴素的规则：周一至周五算工作日，周六周日不算。这就是为什么前面说这个字段“不含法定节假日调整”。如果你需要把节假日也考虑进去，可以用下面的方式调整：
-- 将法定节假日标记为非工作日 UPDATE calendar SET is_workday = 0, is_holiday = 1 WHERE date_value IN ('2024-01-01', '2024-02-10', ...); -- 将调休补班的周末标记为工作日 UPDATE calendar SET is_workday = 1, workday_mark = '调休补班' WHERE date_value IN ('2024-02-04', '2024-02-18', ...);把这个维护工作做成一张单独的节假日配置表也好，或者每年年底统一执行一次也好，都是很成熟的做法。
存储过程里那个 day_interval % 1000 = 0 的判断，纯粹是为了在数据量大的时候能看到执行进度。说实话这个不是必需的，但如果你直接在命令行里跑，没有任何输出的话，你会怀疑是不是卡住了。这个进度提示就是个小彩蛋，执行的时候能安心很多。
4. 常见的日期数据坑：为什么1970年这个起点不是随便选的
你可能注意到了，起始日期是1970年1月1日，不是1900年，也不是2000年。这个日期在计算机领域有特殊含义：UNIX 时间戳的起点，同时也是很多编程语言和数据库系统日期类型的最小值附近。在 MySQL 中，DATE 类型支持的范围是 1000-01-01 到 9999-12-31，所以就算选1970年，也是完全在支持范围内的。
那为什么不用更早的年份，比如1900年？主要是考虑到实际业务中，绝大多数系统的数据都是从2000年以后才产生的，往前多40年的数据，除了让表更大一些、插入更慢一些，没有实际意义。1970年这个起点，跟 UNIX 时间戳相对应，也方便和其他系统做日期对齐。如果你确实需要更早的数据，把 CALL fill_calendar 的起始日期改成 '1900-01-01' 就行，存储过程不需要改。
还有一个容易踩的坑是时区问题。MySQL 连接时如果时区设置不对，直接拿 NOW() 或者 CURDATE() 生成日期，可能产生前后一天的偏移。但对于万年历表来说，因为我们生成的是纯日期，不涉及时间，时区的影响基本为零。不过要注意，如果你的业务系统需要“某天的开始时间和结束时间”，比如统计订单是当天0点到24点，那就要注意系统时区设置了。特别是使用 JDBC 连接 MySQL 8.0 时，建议在连接串里显式指定 serverTimezone=Asia/Shanghai，避免因为默认时区不一致导致日期偏移。
另外，Week 的计算在某些数据库中存在模式差异。MySQL 的 WEEKDAY 函数返回 0=周一，6=周日；而 DAYOFWEEK 返回 1=周日，7=周六；ISO 标准中周一是一周的第一天。 我在存储过程中直接用了 DAYOFWEEK，所以 week_value 的值是1到7，1是周日。如果你习惯用0到6表示周一到周日，可以在代码里再做一次转换：
SELECT date_value, WEEKDAY(date_value) + 1 AS mon_first_week FROM calendar LIMIT 7;这样查询结果就是 1=周一，7=周日，更符合国内大多数场景的习惯。
5. 实际使用姿势：从“能用”到“好用”的查询案例
表建好了，数据也灌进去了，接下来就是看它怎么在业务里发挥作用。我挑几个最常遇到的查询场景，给你一份可以直接抄的SQL。
场景一：按月统计各星期几的订单分布
选品和运营经常关注“周几下单的人比较多”。没有日历表时，你需要在订单表里对订单时间做 DAYOFWEEK 转换，然后 GROUP BY；有了日历表，直接 JOIN 就行：
SELECT c.week_name, COUNT(o.order_id) AS order_cnt FROM orders o INNER JOIN calendar c ON DATE(o.order_time) = c.date_value WHERE c.year_value = 2024 AND c.month_value = 1 GROUP BY c.week_name, c.week_value ORDER BY c.week_value;如果有索引的话，关联查询的效率会非常高。orders 表的 order_time 字段建议也建一个索引，否则全表扫描很难避免。
场景二：计算两个日期之间的工作日天数
这算是日历表最典型的应用了。比如计算两个日期之间的工作日，不需要写循环，不需要递归CTE，一个 COUNT 搞定：
SELECT COUNT(*) AS workdays FROM calendar WHERE date_value BETWEEN '2024-01-01' AND '2024-01-31' AND is_workday = 1;如果要进一步排除法定节假日，只需要在 JOIN 节假日配置表后多一个条件。
场景三：生成完整的报表日期序列
做报表时经常遇到一个问题：某天没有数据，报表里就不显示这一天，图表上出现断档。用日历表做左连接就能轻松补全：
SELECT c.date_value, IFNULL(SUM(o.amount), 0) AS daily_amount FROM calendar c LEFT JOIN orders o ON c.date_value = DATE(o.order_time) WHERE c.date_value BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY c.date_value ORDER BY c.date_value;这样即使某天没有订单，也会展示出来，金额为0，报表就完整了。
场景四：季度和半年度维度汇总
calendar 表里已经预存了 quarter_value 和 half_year 字段，做季度汇总可以直接 GROUP BY，不需要在 SQL 里写 QUARTER(date)：
SELECT c.year_value, c.quarter_value, SUM(o.amount) AS quarterly_amount FROM orders o INNER JOIN calendar c ON DATE(o.order_time) = c.date_value GROUP BY c.year_value, c.quarter_value ORDER BY c.year_value, c.quarter_value;很多 BI 工具或者报表框架在设计数据集时，也需要这种预先计算好的维度字段，因为不是所有查询工具都能灵活处理日期函数。提前把字段算好，对分析师和运营来说非常友好。
6. 性能优化与维护建议：一次生成，长期受益
47838条记录说多不多，说少也不少。对 MySQL 来说，这完全是“微不足道”的量级，查询走主键索引都是微秒级。但有些细节还是值得注意。
索引设计。主键是 date_value，所以按日期范围查询天然走索引。我额外加了两个索引：idx_year_month 用于按年月的分组查询，idx_is_workday 用于工作日筛选。如果你的业务经常按星期几（week_value）筛选，可以考虑再加一个组合索引：
ALTER TABLE calendar ADD KEY idx_year_week (year_value, week_value);但索引不是越多越好，特别是这种数据量不大的表，加太多索引反而浪费空间。只给最常用的查询路径建索引就够了。
表分区有没有必要？说句实话，不到5万行的表没必要分区。分区的好处主要体现在数据量千万级、需要滑动删除的场景。对于万年历表这种“只增不改”的数据，一个普通 InnoDB 表完全够用。如果你担心数据一直增长到2100年，也没必要，5万行就是5万行，不会自己变多。
定期维护。万年历表是一次生成、长期使用的数据，基本不需要维护。唯一可能需要更新的是 is_holiday 和 workday_mark 字段——每年年底，更新下一年的法定节假日安排。建议把这些字段的维护做成一个“节假日配置表”，而不是直接改日历表。为什么？因为日历表里的 is_workday 字段如果被报表引用过，改动了可能导致历史报表数据对不上。节假日配置表独立出来，哪个字段好维护，哪个字段影响面小，一目了然。
备份策略。这种基础维度表属于“改了会出大事、丢了一时半会儿补不回来”的数据，建议纳入数据库的常规备份策略。虽然用存储过程重新生成也就几分钟的事，但如果你在上面做了节假日 UPDATE，那些改动可不会自己回来。所以还是要做好备份。
-- 导出为SQL文件备份 mysqldump -u root -p your_database calendar > calendar_backup.sql;关于 MySQL 8.0 的兼容性。存储过程中用到的 YEAR、MONTH、DAY、DAYOFWEEK、QUARTER、DAYOFYEAR 这些函数，在 MySQL 5.7 和 8.0 中都是完全兼容的。唯一的区别是 MySQL 8.0 默认字符集是 utf8mb4，表结构里也用了 utf8mb4，所以整体不会有什么兼容性问题。如果你还在用 MySQL 5.6 或更老的版本，建议先确认一下 utf8mb4 的排序规则是否选对，一般用 utf8mb4_general_ci 或者 utf8mb4_unicode_ci 都可以。
7. 关于“数据从哪来”的追问：这套方案的边界与局限
最后必须把丑话说在前面。我提供的存储过程生成的数据，解决的是“日期本身”的维度，比如年月日、星期、季度、是否周末。这些是数学上可以确定计算的，不需要任何外部数据源，所以100%准确，永远准确。
但“节假日”不一样。法定节假日的安排是每年由官方发布的，有调休、有补班，不是靠公式能推算出来的。我表里 is_holiday 字段默认全部为0，workday_mark 字段默认为 NULL，就是留给你自己填的。我见到的多数团队会把节假日配置单独做一张表，每年更新一次，维护成本很低。
还有农历相关的需求，比如“春节”“中秋”对应的公历日期每年都不一样，这也需要一张农历转换表或者专门的算法库。我这套表结构里没有包含农历信息，因为农历转换涉及的数据和算法非常复杂，一个简单的万年历表很难覆盖。如果你有这个需求，建议调研专门的农历算法库，不要试图在一个SQL存储过程里搞定所有事情。
我的经验是：日历这种基础数据，一次性投入把表建好、数据灌好，后面所有跟日期相关的需求都能在这个地基上快速实现。不要每次用到日期逻辑就在代码里临时算，那样既慢又乱。直接基于这张万年历表做开发，你会省掉大量时间去处理各种日期边界问题，把精力集中在真正的业务逻辑上。
本文还有配套的精品资源，点击获取
