简介这份资源是一套覆盖1970年1月1日至2100年12月31日的万年历MySQL数据库脚本面向需要处理跨世纪日期查询的开发者、日历应用与时间计算类项目。包内共1个sql文件压缩包约2.25MB包含建表DDL与批量插入DML语句可直接导入MySQL使用。表结构围绕日期、年、月、日、星期、闰年标识、农历日期及节假日信息等字段设计并给出主键与索引优化思路便于快速检索特定日期或节日。已有636人学习下载适合需要现成日期基础数据、减少手工录入成本的中级数据库使用者参考复用。1. 从一张日期维度表说起为什么我要把 1970 到 2100 的日历灌进 MySQL做报表的人大概都遇到过这种场面业务表里只有created_at老板要按“工作日/周末/节假日”分组看趋势SQL 里一堆DAYOFWEEK()和CASE WHEN嵌套写完自己都不敢改。更麻烦的是跨年查询、闰年二月、农历节气这些边界每次都要重新算一遍。这个项目的思路很直接——把 1970-01-01 到 2100-12-31 每一天的日历属性提前算好落成一张 MySQL 表查询时直接 JOIN把“算日期”变成“查日期”。它适合做数据仓库维度建模、排班系统、财务周期统计的开发者也适合刚学 SQL 想找一个能跑起来的完整建表插数据脚本练手的人。整份资源就是建表语句加批量插入语句拿到就能导入不用装任何额外依赖。2. 表结构设计与字段语义一张日历维度表该有哪些列2.1 先想清楚查询会怎么用它日历维度表date dimension在数据仓库里是个标准套路核心思想是把时间的所有派生属性物化。设计字段前我一般先列查询场景按年/季度/月/周聚合、判断是否周末、判断是否月末、算两个日期相差多少天、按周几排班。这些场景决定了表里要有“原始日期 各种粒度标识 布尔标记”三类列。原始日期用DATE类型做主键保证唯一且能直接和业务表的日期列比较粒度标识用整数存年、月、日、季度、周序号方便GROUP BY和排序布尔标记用TINYINT(1)存是否周末、是否月末查询时WHERE is_weekend 1比函数计算快得多。一个容易忽略的点是“周”的定义。MySQL 的WEEK()函数受default_week_format影响不同服务器配置下同一日期可能算出不同的周序号这是血泪经验。所以表里存周序号时要么固定用WEEK(date, 3)ISO 标准周一为一周起点要么干脆存“年内第几周”和“周起始日期”两列把歧义消灭在数据里。我倾向于后者查询端不用再关心服务器配置。2.2 建表语句逐段拆解下面是这张表的核心建表语句字段按“日期本体 → 粒度 → 标记 → 展示”排列CREATE TABLE dim_date ( date_key DATE NOT NULL COMMENT 日期主键格式 YYYY-MM-DD, year_num SMALLINT NOT NULL COMMENT 年份如 2024, quarter_num TINYINT NOT NULL COMMENT 季度 1-4, month_num TINYINT NOT NULL COMMENT 月份 1-12, day_num TINYINT NOT NULL COMMENT 当月第几天 1-31, day_of_year SMALLINT NOT NULL COMMENT 当年第几天 1-366, week_of_year TINYINT NOT NULL COMMENT ISO 周序号 1-53, week_start_date DATE NOT NULL COMMENT 所在周的周一日期, weekday_num TINYINT NOT NULL COMMENT 周几1周一 7周日, is_weekend TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否周末 1是 0否, is_month_end TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否当月最后一天, is_year_end TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否当年最后一天, date_label VARCHAR(20) NOT NULL COMMENT 展示用如 2024-01-01, PRIMARY KEY (date_key), KEY idx_year_month (year_num, month_num), KEY idx_week (year_num, week_of_year) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT日历维度表 1970-2100;date_key用DATE而不是INT存20240101是因为业务表的时间列通常是DATETIMEDATE类型能直接等值 JOIN省掉转换。year_num用SMALLINT是因为 2100 超出TINYINT上限 127这里踩过坑——早期版本用TINYINT存年份插到 128 年就报Out of range。week_start_date这列是给“按周汇总”用的GROUP BY week_start_date比GROUP BY WEEK()稳定。两个索引分别服务“按月查”和“按周查”两类高频查询date_key主键本身覆盖了按天查。2.3 插入语句的两种写法与取舍131 年约 47847 天逐条INSERT写 4 万多行不现实常见做法有两种一是用存储过程循环生成二是用递归 CTE 或数字表批量生成。MySQL 8.0 支持递归 CTE写法最干净INSERT INTO dim_date ( date_key, year_num, quarter_num, month_num, day_num, day_of_year, week_of_year, week_start_date, weekday_num, is_weekend, is_month_end, is_year_end, date_label ) WITH RECURSIVE seq AS ( SELECT 0 AS n UNION ALL SELECT n 1 FROM seq WHERE n 47846 ) SELECT d.dt, YEAR(d.dt), QUARTER(d.dt), MONTH(d.dt), DAY(d.dt), DAYOFYEAR(d.dt), WEEK(d.dt, 3), DATE_SUB(d.dt, INTERVAL WEEKDAY(d.dt) DAY), WEEKDAY(d.dt) 1, IF(WEEKDAY(d.dt) 5, 1, 0), IF(LAST_DAY(d.dt) d.dt, 1, 0), IF(MONTH(d.dt) 12 AND DAY(d.dt) 31, 1, 0), DATE_FORMAT(d.dt, %Y-%m-%d) FROM ( SELECT DATE_ADD(1970-01-01, INTERVAL n DAY) AS dt FROM seq ) d;seq生成 0 到 47846 的连续整数DATE_ADD把基准日逐天推进得到完整日期序列。WEEK(d.dt, 3)的第二个参数 3 表示 ISO 标准周周一开头、第一周含 4 天以上和week_start_date的WEEKDAY计算口径一致。WEEKDAY()返回 0 表示周一加 1 后weekday_num就是 1-7 的直观编号。is_weekend用WEEKDAY 5判断即周六5和周日6。LAST_DAY(d.dt) d.dt是判断月末最省事的写法比DAY(d.dt) DAY(LAST_DAY(d.dt))少一次函数调用。提示递归 CTE 默认cte_max_recursion_depth是 10004 万多行会直接报错。执行前先SET SESSION cte_max_recursion_depth 100000;或者改用存储过程循环。这是导入时最常见的翻车点。如果目标环境是 MySQL 5.7 或更早递归 CTE 不可用就得用存储过程DELIMITER $$ CREATE PROCEDURE fill_dim_date() BEGIN DECLARE v_date DATE DEFAULT 1970-01-01; WHILE v_date 2100-12-31 DO INSERT INTO dim_date VALUES ( v_date, YEAR(v_date), QUARTER(v_date), MONTH(v_date), DAY(v_date), DAYOFYEAR(v_date), WEEK(v_date, 3), DATE_SUB(v_date, INTERVAL WEEKDAY(v_date) DAY), WEEKDAY(v_date) 1, IF(WEEKDAY(v_date) 5, 1, 0), IF(LAST_DAY(v_date) v_date, 1, 0), IF(MONTH(v_date) 12 AND DAY(v_date) 31, 1, 0), DATE_FORMAT(v_date, %Y-%m-%d) ); SET v_date DATE_ADD(v_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL fill_dim_date();存储过程逐行插入 4 万多条会慢实测在普通开发机上要几十秒到一两分钟。优化办法是关掉自动提交、批量提交在循环里每 1000 条COMMIT一次或者把INSERT改成先拼VALUES多值再执行。不过这是一次性初始化操作慢一点可以接受别在业务高峰期跑就行。3. 把脚本跑起来导入、验证与查询实战3.1 导入前的环境检查拿到 SQL 文件后别急着source先确认三件事MySQL 版本决定用 CTE 还是存储过程、字符集utf8mb4避免中文注释乱码、时区DATE类型不受时区影响但如果脚本里有NOW()相关逻辑要注意。检查命令mysql --version mysql -u root -p -e SHOW VARIABLES LIKE character_set_server; mysql -u root -p -e SELECT sql_mode;sql_mode里如果有STRICT_TRANS_TABLES插入时字段超范围会直接报错而不是截断这其实是好事能提前暴露TINYINT存年份这类问题。导入时建议新建独立库别和业务库混在一起mysql -u root -p -e CREATE DATABASE IF NOT EXISTS dw_dim DEFAULT CHARSET utf8mb4; mysql -u root -p dw_dim dim_date.sql如果脚本里包含CREATE TABLE和INSERT一条命令就完成。导入后先看行数对不对SELECT COUNT(*) FROM dim_date; -- 期望 47847 SELECT MIN(date_key), MAX(date_key) FROM dim_date; -- 期望 1970-01-01 到 2100-12-31行数对不上通常是递归深度限制或循环边界写错比如WHILE v_date 2100-12-31会少最后一天。这种 off-by-one 在日期生成里特别常见验证时一定要查MAX。3.2 三个典型查询验证表的价值表建好后用几个真实场景验证它确实能简化 SQL。第一个是按季度统计订单量业务表只存order_timeSELECT d.year_num, d.quarter_num, COUNT(*) AS order_cnt FROM orders o JOIN dim_date d ON DATE(o.order_time) d.date_key GROUP BY d.year_num, d.quarter_num ORDER BY d.year_num, d.quarter_num;对比传统写法GROUP BY YEAR(order_time), QUARTER(order_time)JOIN 版本的好处是能顺带取季度起止日期、工作日天数等维度属性而且dim_date上的索引能加速过滤。第二个场景是算工作日天数SELECT COUNT(*) AS work_days FROM dim_date WHERE date_key BETWEEN 2024-01-01 AND 2024-12-31 AND is_weekend 0;不用写任何DAYOFWEEK逻辑一个WHERE搞定。第三个场景是找某月最后一天SELECT date_key FROM dim_date WHERE year_num 2024 AND month_num 2 AND is_month_end 1;闰年二月自动返回 29 号不用特殊处理。这三个查询覆盖了聚合、过滤、边界判断三类需求也是我验证任何日历表是否可用的最小测试集。3.3 和业务表关联时的性能注意dim_date只有 4 万多行全表扫描也就毫秒级但 JOIN 时如果写成DATE(o.order_time) d.date_key函数作用在业务表列上会导致索引失效。更好的写法是让业务表时间列直接和date_key比较范围或者业务表冗余一个date_key列在写入时算好。数据量大的事实表我一般会在 ETL 阶段就把DATE(order_time)落成独立列并建索引查询时o.order_date d.date_key走等值 JOIN执行计划干净很多。这是维度建模里“代理键”思路的简化版日历表本身就是一张预计算的代理键表。4. 避坑与排查导入日历表时最容易翻车的五件事4.1 递归 CTE 报错 “Recursive query aborted”现象是执行插入语句时直接报ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。原因是 MySQL 对递归 CTE 有默认迭代上限 1000而生成 4 万多天远超这个数。解决办法是在同一会话里先调大限制SET SESSION cte_max_recursion_depth 100000;再执行插入。注意这个设置是会话级的换一个连接就失效所以要和插入语句放在同一个脚本或同一个客户端会话里执行。4.2 年份字段插入报 “Out of range value”现象是插到 128 年附近报Out of range value for column year_num。原因是year_num用了TINYINT有符号范围只到 127。日历表要覆盖到 2100 年年份字段必须用SMALLINT上限 32767。这个坑的隐蔽之处在于 1970 到 2127 年之间不会报错只有超过 127 才暴露如果测试数据只造了近几年上线跑全量才翻车。建表时所有可能超过 127 的数值列都要检查一遍day_of_year最大 366 同样不能用TINYINT。4.3 周序号和业务方对不上现象是报表里“2024 年第 1 周”的起止日期和业务方认知不一致。原因是WEEK()函数的行为受default_week_format系统变量影响不同环境可能一个按周日开头、一个按周一开头。解决办法是统一用WEEK(date, 3)显式指定 ISO 模式并且额外存week_start_date列让业务方按“周起始日期”分组而不是按周序号数字分组。周序号本身在不同标准下含义不同存数字容易引发争议存日期没有歧义。4.4 导入后中文注释变问号现象是SHOW CREATE TABLE看到的COMMENT全是???。原因是客户端连接字符集不是utf8mb4导入时注释被转码丢失。解决办法是在导入命令或客户端配置里指定--default-character-setutf8mb4建库时也显式DEFAULT CHARSETutf8mb4。注释乱码不影响查询但二次开发时看不懂字段含义维护成本很高建议一次配对。4.5 存储过程循环插入太慢甚至锁表现象是CALL fill_dim_date()跑了很久没结束或者期间业务查询变慢。原因是逐行INSERT在默认自动提交模式下每行一个事务4 万多次提交开销巨大还会持有锁。解决办法是在过程开头START TRANSACTION循环内每 1000 行COMMIT并重新开启事务或者干脆改用递归 CTE 一条语句批量插入。如果表上已有数据先TRUNCATE再灌避免主键冲突导致中途失败留下半张表。5. 二次开发把日历表接进自己的项目并扩展字段5.1 扩展字段的加列思路基础表跑通后多数项目会想加节假日、农历、调休标记。加列的原则是“能预计算就预计算”别在查询时算。比如加一个is_workday列把周末和法定节假日都排除ALTER TABLE dim_date ADD COLUMN is_workday TINYINT(1) NOT NULL DEFAULT 1 COMMENT 是否工作日周末和节假日为0 AFTER is_weekend; -- 先按周末初始化 UPDATE dim_date SET is_workday IF(is_weekend 1, 0, 1); -- 再按节假日表覆盖holiday 表需自行维护 UPDATE dim_date d JOIN dim_holiday h ON d.date_key h.holiday_date SET d.is_workday 0;dim_holiday是一张需要人工维护的小表存法定节假日和调休上班日。调休上班日周末但上班要单独处理把is_workday置回 1。这种“基础规则 人工覆盖”的两层结构比在 SQL 里写死节假日列表好维护得多每年更新一次节假日表即可。农历字段同理可以用现成的农历转换逻辑在生成阶段算好存进来查询端只读。5.2 用生成列减少维护成本MySQL 5.7 起支持生成列generated column像date_label这种纯展示字段可以交给数据库自动算插入时不用管ALTER TABLE dim_date MODIFY COLUMN date_label VARCHAR(20) GENERATED ALWAYS AS (DATE_FORMAT(date_key, %Y-%m-%d)) STORED;STORED表示实际存储、可建索引VIRTUAL则不占空间但每次读时计算。展示字段用STORED更稳避免每次查询都调DATE_FORMAT。同理year_num、month_num这些也能做成生成列插入语句就只需要提供date_key其余全自动。代价是生成列表达式不能引用其他表的列且修改表达式要重建表适合字段规则稳定的场景。5.3 验证改造是否正确的习惯每次改完表结构或生成逻辑我都会跑一遍固定校验集而不是凭感觉。校验 SQL 可以存成一个文件反复用-- 1. 总行数 SELECT COUNT(*) 47847 AS ok_total FROM dim_date; -- 2. 无重复、无空洞相邻日期差恒为1 SELECT COUNT(*) 0 AS ok_gap FROM ( SELECT date_key, DATEDIFF(date_key, LAG(date_key) OVER (ORDER BY date_key)) AS diff FROM dim_date ) t WHERE diff 1 AND diff IS NOT NULL; -- 3. 闰年 2 月 29 存在且标记正确 SELECT COUNT(*) 1 AS ok_leap FROM dim_date WHERE date_key 2024-02-29 AND is_month_end 1;第二条用窗口函数LAG检查日期连续性能一次性发现漏生成或多生成。第三条锁定闰年边界。这套校验跑通基本可以放心把表交给下游用。从那以后我每次重建日历表不管改没改逻辑都强制走一遍这三条校验再发布因为日期生成的 bug 往往藏在边界上肉眼扫不出来。希望这套建表加校验的思路能帮你在下一个报表需求里少写几层CASE WHEN。本文还有配套的精品资源点击获取