简介一整套覆盖1970年1月1日至2100年12月31日的MySQL万年历数据库SQL脚本面向需要处理日期、农历、节假日或时间计算的开发者和数据库使用者。压缩包内共1个文件整体大小2.25MB文件类型为SQL脚本内含完整的建表语句与批量插入语句可一次性构建包含大量日期记录的数据表。表结构以date字段为主键同时包含year、month、day、week_day、is_leap_year等公历基础字段并设有lunar_date和holiday_info扩展字段能查询星期、判断闰年、获取农历日期及相关节日信息导入MySQL后即可直接使用。已有635人学习下载适合日历类App开发、日程管理系统、数据仓库日期维度表建设等场景也可作为学习SQL日期函数、批量插入和海量数据组织方式的参考。对于需要快速搭建日期基础数据的项目这套脚本能显著减少重复造轮子的工作量并便于后续按需扩展或定制。1. 万年历数据库没那么简单47847 行数据背后的三个坑先说一个反直觉的结论一张万年历表表结构定错了比数据插入失败更难受。万年历数据库这个需求听起来简单——从 1970 年 1 月 1 日到 2100 年 12 月 31 日一共也就 47847 行给 MySQL 写张建表语句、再写条插入语句就能交差。但真去网上随便找一个 SQL 文件导入大概率会在星期口径、闰年、递归插入这三个地方翻车。这篇文章我给出一套能直接复现的做法表结构怎么设计、SQL 建表语句怎么写、插入语句怎么生成以及我踩过的坑和自检 SQL。适合做报表时间维、日历组件、排班或者日期计算的开发者照着抄。2. 先把表结构定好日期主键加哪些冗余字段这张表的主键必须是date_key而不是自增 ID。日历表是典型的维度表业务表用日期关联日期自己就是唯一业务键用自增 ID 反而每次 JOIN 都要多带一列日期查询多绕一层。DATE 类型占 3 字节比 DATETIME 少 5 字节对一张几十万行且被频繁 JOIN 的表有帮助。日期维度字段要冗余year、month、day、weekday 这些插入时算一次查询时直接取不要每次查都跑函数。2.1 查询驱动设计为什么拆出年、月、日、星期字段日历表最常见的查询是按月汇总、按周汇总、判断周末、找出某月所有周一。如果表里只有 date_key这些查询也能做但每条都要跑 YEAR()、MONTH()、WEEKDAY()。47847 行本身不算大数据可日历表是被订单表、流水表反复 JOIN 的维度表一次范围查询如果只靠 date_key 主键扫效率高得多把常用粒度拆出来等于把计算压力前移到插入阶段。字段清单如下字段类型说明date_keyDATE公历日期主键yearSMALLINT年monthTINYINT月1-12dayTINYINT日1-31week_of_yearTINYINT周次周一为一周开始1-53weekday_isoTINYINT星期1周一7周日quarterTINYINT季度1-4day_of_yearSMALLINT年内第几天1-366is_weekendTINYINT1周末is_workdayTINYINT1工作日仅按周口径lunar_dateVARCHAR(20)农历日期如 2024-01-01solar_termVARCHAR(20)节气如 立春holiday_nameVARCHAR(50)节假日名称remarkVARCHAR(255)备注year 不用 INTSMALLINT 最大 32767到 2100 年绰绰有余month 和 day 用 TINYINT取值范围刚好。day_of_year 最大 366SMALLINT 正好。这些选择不是为了省几十 KB而是让字段语义和取值域对应上避免将来有人往里塞 month42 这种脏数据。lunar_date、solar_term、holiday_name 这几个字段先建好插入时留空后面用 UPDATE 补不要一开始就把农历算进插入逻辑里否则插入 SQL 会复杂到没人愿意维护。2.2 星期与周次口径DAYOFWEEK 和 WEEKDAY 怎么选MySQL 里查星期的函数有两个翻车点就在这里。DAYOFWEEK 返回 1周日、2周一……7周六WEEKDAY 返回 0周一、1周二……6周日。国内业务习惯把周一作为一周开始所以按 ISO 口径存更顺手。我建议 weekday_iso 用WEEKDAY(date_key) 1计算得到一个 1周一、7周日的值周次用WEEK(date_key, 1)模式 1 表示周一为一周开始返回 1-53。为什么要单独说清楚因为如果用了 DAYOFWEEK2024 年 1 月 1 日明明是周一表里会存成 2周末判断也会跟着错。一个表字段的口径错了排班、考勤、报表全都会偏。DATE_FORMAT 里的 %w 同样返回 0周日和 DAYOFWEEK 一伙的别混用。存 weekday_iso 的另一个好处是查询可以直接写WHERE year 2024 AND weekday_iso 1一年 52 行不用在 WHERE 里套函数。2.3 字符集、引擎与索引小表也有设计取舍字符集用 utf8mb4因为日历表未来要存中文节假日名、农历的“冬月”“腊月”这类汉字甚至可能存特殊字符。排序规则我一般用 utf8mb4_general_ci兼容性最好如果你确定只跑 MySQL 8.0换成 utf8mb4_0900_ai_ci 也没问题。引擎用 InnoDB支持事务和行级锁主键是聚集索引日期范围查询直接走主键。索引方面不需要额外给 month、year 建索引。WHERE month 6会扫出 130 年的所有 6 月比例太高优化器大概率放弃索引如果配合 date_key 范围一起过滤date_key 主键索引已经能处理。有人问要不要按年分区47847 行真没必要分区表的元数据开销比收益大把分区留给上亿行的业务表。DATE 主键配合范围 BETWEEN 查询已经覆盖了日历表 95% 的使用场景。3. 建表与插入语句两条路线生成 47847 行数据表结构定了剩下的就是建表和灌数据。下面这段 DDL 建议直接复制字段注释里把 weekday_iso 的 1周一口径写死防止三个月后自己和同事看着表猜语义。lunar_date、solar_term、holiday_name 在插入阶段留空扩展时再 UPDATE。3.1 建表 SQL字段注释写清口径直接复制执行CREATE TABLE calendar ( date_key DATE NOT NULL COMMENT 公历日期主键, year SMALLINT NOT NULL COMMENT 年, month TINYINT NOT NULL COMMENT 月 1-12, day TINYINT NOT NULL COMMENT 日 1-31, week_of_year TINYINT NOT NULL COMMENT 周次周一为一周开始1-53, weekday_iso TINYINT NOT NULL COMMENT 星期1周一7周日, quarter TINYINT NOT NULL COMMENT 季度 1-4, day_of_year SMALLINT NOT NULL COMMENT 年内第几天 1-366, is_weekend TINYINT NOT NULL DEFAULT 0 COMMENT 1周末, is_workday TINYINT NOT NULL DEFAULT 1 COMMENT 1工作日仅按周口径, lunar_date VARCHAR(20) NULL COMMENT 农历日期如 2024-01-01, solar_term VARCHAR(20) NULL COMMENT 节气如 立春, holiday_name VARCHAR(50) NULL COMMENT 节假日名称, remark VARCHAR(255) NULL COMMENT 备注, PRIMARY KEY (date_key) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT万年历基础表 1970-01-01 至 2100-12-31;字段类型的选择前面已经说过这里重点看 COMMENT。date_key 注释直接写“公历日期主键”weekday_iso 注释写明 1周一、7周日is_workday 特意加了“仅按周口径”避免以后有人把法定节假日也硬塞进这个字段。排序规则选 utf8mb4_general_ci 是为了和 5.7 的旧库兼容如果全链路都是 8.0换 0900_ai_ci 更好。这张表不要加自增 ID日期主键就是自然键业务表直接存 date_key。3.2 MySQL 8.0 插入递归 CTE 生成 1970 到 2100MySQL 8.0 可以直接用递归 CTE 生成连续日期序列一条 SQL 搞定。注意要先调大递归深度限制默认 1000 次不够用。SET SESSION cte_max_recursion_depth 50000; INSERT INTO calendar ( date_key, year, month, day, week_of_year, weekday_iso, quarter, day_of_year, is_weekend, is_workday ) WITH RECURSIVE seq (n) AS ( SELECT 0 UNION ALL SELECT n 1 FROM seq WHERE n 47846 ) SELECT DATE_ADD(1970-01-01, INTERVAL n DAY) AS date_key, YEAR(DATE_ADD(1970-01-01, INTERVAL n DAY)) AS year, MONTH(DATE_ADD(1970-01-01, INTERVAL n DAY)) AS month, DAYOFMONTH(DATE_ADD(1970-01-01, INTERVAL n DAY)) AS day, WEEK(DATE_ADD(1970-01-01, INTERVAL n DAY), 1) AS week_of_year, WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 1 AS weekday_iso, QUARTER(DATE_ADD(1970-01-01, INTERVAL n DAY)) AS quarter, DAYOFYEAR(DATE_ADD(1970-01-01, INTERVAL n DAY)) AS day_of_year, CASE WHEN WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 5 THEN 1 ELSE 0 END AS is_weekend, CASE WHEN WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 5 THEN 0 ELSE 1 END AS is_workday FROM seq;47846 这个数字是这么来的1970-01-01 到 2100-12-31 一共 47847 天递归序列从 0 开始终止条件就是 n 47846生成 0 到 47846 共 47847 个数对应 47847 个日期。cte_max_recursion_depth默认只有 1000不设置的话执行到第 1001 次就直接报错8.0.19 之前这个参数叫 max_recursive_iterations版本不同注意区分。它是 session 级变量不写进配置文件建议在插入前同一个连接里先执行。WEEKDAY(date_key) 1前面解释过011 就是周一617 就是周日。is_weekend 和 is_workday 用 CASE 从 WEEKDAY 推导WEEKDAY 5 是周六日。插入完成后执行SELECT COUNT(*) FROM calendar;看到 47847 就说明日期范围覆盖完整。3.3 MySQL 5.7 兼容方案digits 数字辅助表生产库还在用 MySQL 5.7 的话没有递归 CTE最稳的方案是一张数字辅助表 digits。digits 表就是单列整数表放 0 到 49999以后生成序列、分页补数都能复用。建表并填充的 SQL 如下DROP TABLE IF EXISTS digits; CREATE TABLE digits (n INT NOT NULL PRIMARY KEY); INSERT INTO digits (n) SELECT a.n b.n * 10 c.n * 100 d.n * 1000 e.n * 10000 FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS e WHERE a.n b.n * 10 c.n * 100 d.n * 1000 e.n * 10000 50000;五个派生表交叉连接生成 0 到 99999WHERE 条件截断到 50000。为什么不只建 47847 行多留一点余量以后生成别的序列不用重建。有了 digits 表插入日历的 SQL 和递归 CTE 逻辑几乎一样INSERT INTO calendar ( date_key, year, month, day, week_of_year, weekday_iso, quarter, day_of_year, is_weekend, is_workday ) SELECT DATE_ADD(1970-01-01, INTERVAL n DAY), YEAR(DATE_ADD(1970-01-01, INTERVAL n DAY)), MONTH(DATE_ADD(1970-01-01, INTERVAL n DAY)), DAYOFMONTH(DATE_ADD(1970-01-01, INTERVAL n DAY)), WEEK(DATE_ADD(1970-01-01, INTERVAL n DAY), 1), WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 1, QUARTER(DATE_ADD(1970-01-01, INTERVAL n DAY)), DAYOFYEAR(DATE_ADD(1970-01-01, INTERVAL n DAY)), CASE WHEN WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 5 THEN 1 ELSE 0 END, CASE WHEN WEEKDAY(DATE_ADD(1970-01-01, INTERVAL n DAY)) 5 THEN 0 ELSE 1 END FROM digits WHERE n 47846;两种方案结果完全一致digits 方案不依赖 session 变量在脚本里重复执行更稳。如果你建数字表时只建到 9999那 47846 超出范围插入会缺一截所以 digits 表至少建到 49999。4. 避坑清单递归报错、星期错位与 2100 年闰年这一章是最想让你看到的部分。下面五个坑我自己或 A 同学都实际踩过每条按现象、原因、解决写清楚抄作业的时候提前避开。4.1 递归 CTE 报错Recursive query aborted现象执行递归 CTE 插入时报错Recursive query aborted after 1001 iterations表里只插进去 1001 行。原因MySQL 默认的 cte_max_recursion_depth 是 1000递归到第 1001 次直接中止。47847 行需要递归 47847 次默认值远远不够。解决插入前先执行SET SESSION cte_max_recursion_depth 50000;。注意 8.0.19 之前这个参数叫 max_recursive_iterations老版本把变量名换一下。最关键的一点SET 和 INSERT 必须在同一个连接里执行我见过有人把 SET 写在 SQL 文件开头然后从客户端拆成两次执行结果 INSERT 还是报错。4.2 星期字段和实际口径差一天现象2024-01-01 明明是周一表里 weekday_iso 存成了 2周末判断也跟着反。原因建表时用了 DAYOFWEEK它把周日算作 1周一算作 2和国内习惯的周一1 差了一天。DAYOFWEEK 和 WEEKDAY 的返回值本来就差 1混用必然错。解决统一用WEEKDAY(date_key) 1计算 weekday_iso。如果表已经错了一条 UPDATE 修正UPDATE calendar SET weekday_iso WEEKDAY(date_key) 1;改完之后抽查几个已知星期几的日期再确认一遍口径。4.3 2100 年被当成闰年2 月多一天现象表里出现了 2100-02-29统计每年 2 月天数时发现 2100 年是 29 天。原因2100 能被 100 整除但不能被 400 整除它不是闰年。很多人用“年份能被 4 整除”判断闰年就会把 2100 算错。用 DATE_ADD 从基准日期递增不会碰这个问题但手工拼日期或者导入外部数据时很容易翻车。解决不要手工拼日期统一用 DATE_ADD 从 1970-01-01 递增。判断闰年用完整公式(y % 4 0 AND y % 100 0) OR y % 400 0。后面 5.1 会给一条专门检查 2 月天数的 SQL跑一遍就能发现这种脏数据。4.4 工作日标记被法定节假日推翻现象国庆 7 天 is_workday 都是 1调休补班的周六 is_workday 却是 0排班统计全乱。原因is_workday 在插入时只按星期几判断管不了法定节假日和调休。这种静态字段一旦写入每年都要 UPDATE维护成本很高。解决保留 is_workday 作为“按周口径”另建一张 holiday 表记录每年的法定休息日和调休补班日CREATE TABLE holiday ( date_key DATE PRIMARY KEY, is_offday TINYINT NOT NULL COMMENT 1法定休息日0调休补班, holiday_name VARCHAR(50) NULL );查询时用 LEFT JOIN 修正SELECT c.date_key, c.is_workday, CASE WHEN h.is_offday 1 THEN 0 WHEN h.is_offday 0 THEN 1 ELSE c.is_workday END AS real_workday FROM calendar c LEFT JOIN holiday h ON h.date_key c.date_key;每年只要维护几十行 holiday 数据不需要动 47847 行的日历主表。这样设计是给后来人留后路毕竟调休政策每年都可能变。4.5 单事务写 4.7 万行慢与日志膨胀现象一次插入 47847 行执行几十秒机器 IO 飙高binlog 也明显膨胀如果用客户端逐条 INSERT更是慢到怀疑人生。原因单事务写入大量行时InnoDB 要维护主键索引每条记录都要写 undo 和 redo客户端逐条提交则每条都有一次网络往返和事务提交开销。4.7 万行不算大但方式不对一样卡。解决用 INSERT ... SELECT 一次性写入不要逐条 INSERT。如果还嫌慢可以把数据按年拆分每 10 年一个 INSERT 段分多次提交工作量差不多但更好排查。mysqld 的 max_allowed_packet 如果设置太小大批量语句也可能被中断调到 64M 以上基本够用。5. 三条自检 SQL 和后续扩展把日历表用稳日历表插入完成后先用三条 SQL 自检确认没有断档、没有闰年错误、没有星期错位再交给业务用。SELECT COUNT(*) AS total_days FROM calendar; -- 期望结果47847SELECT YEAR(date_key) AS y, COUNT(*) AS feb_days FROM calendar WHERE MONTH(date_key) 2 GROUP BY YEAR(date_key) HAVING feb_days NOT IN (28, 29) ORDER BY y; -- 期望结果空集尤其注意 2100 年必须只有 28 天SELECT a.date_key AS gap_start FROM calendar a LEFT JOIN calendar b ON a.date_key DATE_ADD(b.date_key, INTERVAL 1 DAY) WHERE b.date_key IS NULL AND a.date_key 1970-01-01; -- 期望结果空集存在断档时会返回断档后的第一天三条 SQL 分别验证了行数、闰年规则和日期连续性。再随便抽查一个已知日期比如 2024-01-01weekday_iso 应该是 1。农历扩展建议单独建一张 lunar_reference 表用现成的农历数据文件导入再通过 UPDATE JOIN 回填到 calendar。不要指望自己手写 SQL 推算农历闰月逻辑复杂到能把人绕晕。节气同理精确日期要用天文算法常见做法是每年由定时任务算好再更新如果只是展示级需求先存近似日期也能跑。调休扩展就是 4.4 里的 holiday 表每年维护几十行比改主表干净得多。我用日历表有个习惯写 JOIN 之前先看字段注释里的 weekday 口径确认 1 是周一还是周日再写条件。曾经有位同事没看口径直接把 weekday_iso 当成周一0 来用排班表全线错位最后靠一条 UPDATE 才救回来。现在我把口径写进 DDL 注释就是不想让后人再猜。希望帮到你。本文还有配套的精品资源点击获取