企业官网建设流程全解析

简介：这份资源是一套覆盖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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日历维度表 1970-2100';

date_key用DATE而不是INT存20240101，是因为业务表的时间列通常是DATETIME，DATE类型能直接等值 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是 1000，4 万多行会直接报错。执行前先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_time：

SELECT 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-set=utf8mb4，建库时也显式DEFAULT CHARSET=utf8mb4。注释乱码不影响查询，但二次开发时看不懂字段含义，维护成本很高，建议一次配对。

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。

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