企业官网建设流程全解析

简介：这份MySQL日历数据表面向需要处理日期转换与农历功能的开发者，尤其适合日历应用、事件管理、传统节日提醒等场景。资源以SQL脚本形式提供1900年至2100年的公历与农历对照数据，可支撑公历农历互查、节气节日标记及日历视图切换等需求。压缩包内共1个文件，为sql脚本，整体约1.94MB，导入后即可创建并填充公历表与农历表，字段涵盖年月日、星期、节假日标记及对应转换日期，并涉及闰月处理等农历计算逻辑。目前已有1888人学习下载，说明其在日期处理类项目中具有一定参考价值。读者可借助这份数据快速搭建农历查询与转换基础，减少自行整理历法数据的工作量，也能为日程、活动等业务提供更完整的日历服务支撑。

1. 一张 1900–2100 的 MySQL 日历数据表，到底能省掉多少事

做业务系统的人迟早会撞上同一个需求：按日期维度做统计、排班、算账期、算农历节日、算工作日。第一反应往往是「写个函数现算」，结果就是每次查询都在跑循环，索引失效，慢查询日志天天报警。我见过一个排班系统，光是把「未来 30 天里所有工作日」算出来，就写了两百多行存储过程，改一次需求崩一次。后来换了个思路：把 1900 到 2100 这两百年的日期，提前算好、落成一张 MySQL 日历数据表，公历一张、农历一张，查询时直接 JOIN，问题当场消失。

这个标题里的「mysql日历数据表1900-2100（公历表和农历表)」说的就是这件事：用一张预生成的日期维度表，把公历的星期、周数、是否周末、是否节假日，以及农历的干支、生肖、月日、是否闰月，全部固化成行。它解决的不是「怎么算日期」，而是「怎么让日期计算不再成为查询瓶颈」。适合谁？做考勤、排班、财务账期、会员生日营销、节日活动配置的后端和数据分析同学，尤其是那种日期逻辑反复被产品改、每次改都要动 SQL 的场景。

2. 为什么日期维度表比现算函数更靠谱

2.1 现算日期的三个隐性成本

很多人觉得日期计算是纯函数，算一下不花钱。真实情况是，MySQL 里每一次DAYOFWEEK()、WEEK()、DATE_FORMAT()调用，都发生在结果集逐行处理的阶段。当你在 WHERE 或 JOIN 条件里用它，索引直接失效，全表扫描跑不掉。这是第一层成本：性能。

第二层是正确性成本。公历的「第几周」在不同标准下定义不同，ISO 8601 把周一当一周开始，且第一周必须包含至少四天；而很多业务口径是「1 月 1 日所在周就是第一周」。这两种算法写进 SQL 里，隔三个月没人记得当初用的是哪种。农历更麻烦，闰月、大小月、节气边界，靠函数现算几乎必然出错。

第三层是维护成本。日期规则会变——调休安排每年不同，节假日不是固定规律。如果规则散落在几十个 SQL 和存储过程里，改一次要全局搜索。而维度表把规则收敛到一张表，改数据不改代码。

2.2 日历表的字段设计：公历表该有哪些列

公历表的核心是「一行一天，字段自解释」。下面是我常用的建表语句，字段命名尽量直白，方便业务同学自己看：

CREATE TABLE dim_calendar ( dt DATE NOT NULL COMMENT '公历日期，主键', y SMALLINT NOT NULL COMMENT '年，如 2024', m TINYINT NOT NULL COMMENT '月，1-12', d TINYINT NOT NULL COMMENT '日，1-31', ymd INT NOT NULL COMMENT '年月日整数，如 20240115，便于范围比较', week_day TINYINT NOT NULL COMMENT '星期，1=周一 ... 7=周日（ISO 口径）', week_of_year TINYINT NOT NULL COMMENT 'ISO 周数，1-53', is_weekend TINYINT NOT NULL DEFAULT 0 COMMENT '是否周末，1 是 0 否', is_holiday TINYINT NOT NULL DEFAULT 0 COMMENT '是否法定节假日，1 是 0 否', holiday_name VARCHAR(32) DEFAULT NULL COMMENT '节假日名称，如春节、国庆', workday_shift VARCHAR(16) DEFAULT NULL COMMENT '调休标记，如 work(补班)/rest(放假)', quarter TINYINT NOT NULL COMMENT '季度，1-4', days_in_month TINYINT NOT NULL COMMENT '当月天数，28-31', PRIMARY KEY (dt), KEY idx_y_m (y, m), KEY idx_ymd (ymd), KEY idx_week (y, week_of_year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公历日期维度表 1900-2100';

这里有几个设计取舍值得说清楚。ymd用整数存，是为了让「取某月数据」这种查询能走ymd BETWEEN 20240101 AND 20240131，比DATE_FORMAT快一个量级。week_day用 ISO 口径（周一为 1），是因为跨周统计时 ISO 口径最不容易出歧义；如果你的业务习惯周日为 1，改生成脚本即可，但全表口径必须统一。workday_shift单独留一列，是因为「周末」和「是否上班」是两回事——调休补班的周六是要上班的，这一列让考勤逻辑不用再写 if-else。

2.3 农历表怎么建：闰月和干支的存储方式

农历表比公历表难，难点在闰月。农历一年可能有 13 个月，闰月跟在某个月后面，比如「闰四月」。存储时不能只存「月」和「日」，必须有一个字段标识这个月是不是闰月。

CREATE TABLE dim_lunar_calendar ( dt DATE NOT NULL COMMENT '对应的公历日期，主键', lunar_y SMALLINT NOT NULL COMMENT '农历年', lunar_m TINYINT NOT NULL COMMENT '农历月，1-12', lunar_d TINYINT NOT NULL COMMENT '农历日，1-30', is_leap_month TINYINT NOT NULL DEFAULT 0 COMMENT '该月是否闰月，1 是 0 否', lunar_m_name VARCHAR(8) NOT NULL COMMENT '农历月名，如 正月、闰四月', lunar_d_name VARCHAR(8) NOT NULL COMMENT '农历日名，如 初一、十五', gan_zhi_year VARCHAR(8) NOT NULL COMMENT '干支纪年，如 甲辰', zodiac VARCHAR(4) NOT NULL COMMENT '生肖，如 龙', solar_term VARCHAR(8) DEFAULT NULL COMMENT '节气名，如 立春，非节气日为空', PRIMARY KEY (dt), KEY idx_lunar (lunar_y, lunar_m, lunar_d), KEY idx_leap (lunar_y, is_leap_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='农历日期维度表 1900-2100';

is_leap_month是关键。查询「农历四月初八」时，必须明确是普通四月还是闰四月，否则生日提醒会错。solar_term单独存一列，是因为节气在营销场景里用得多（比如「立秋推秋装」），现算节气需要天文算法，落表后一句WHERE solar_term = '立秋'就够。干支和生肖直接存字符串，避免每次查询再拼接。

提示：农历表的主键也用公历日期，这样两张表可以按dt直接 JOIN，业务查询时不用做任何转换。

3. 把 1900–2100 的数据灌进去：生成脚本与批量插入

3.1 用 Python 生成公历数据并写库

数据量算一下：1900 到 2100 是 201 年，约 73400 天。这个量级对 MySQL 来说很小，一次性灌入毫无压力。我一般用 Python 生成，因为日期库成熟，逻辑清晰。

import datetime import pymysql # 连接配置按实际环境改 conn = pymysql.connect(host='127.0.0.1', user='root', password='your_pwd', database='dim_db', charset='utf8mb4') cur = conn.cursor() start = datetime.date(1900, 1, 1) end = datetime.date(2100, 12, 31) delta = datetime.timedelta(days=1) rows = [] d = start while d <= end: iso = d.isocalendar() # (ISO年, ISO周, ISO星期) y, m, day = d.year, d.month, d.day # 当月天数：下月一号减一天 if m == 12: next_month = datetime.date(y + 1, 1, 1) else: next_month = datetime.date(y, m + 1, 1) days_in_month = (next_month - datetime.date(y, m, 1)).days rows.append(( d.isoformat(), y, m, day, y * 10000 + m * 100 + day, iso[2], iso[1], 1 if iso[2] >= 6 else 0, # 周六周日算周末 0, None, None, (m - 1) // 3 + 1, days_in_month )) d += delta sql = """INSERT INTO dim_calendar (dt,y,m,d,ymd,week_day,week_of_year,is_weekend,is_holiday,holiday_name,workday_shift,quarter,days_in_month) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" cur.executemany(sql, rows) conn.commit() print('inserted', cur.rowcount)

逻辑说明：isocalendar()返回 ISO 口径的年、周、星期，直接对应表里的week_of_year和week_day。is_weekend用iso[2] >= 6判断，因为 ISO 里 6 是周六、7 是周日。days_in_month用「下月一号减当月一号」算，比查表可靠。参数上，executemany批量提交比逐条 INSERT 快几十倍，7 万行几秒钟跑完。

节假日和调休这两列，生成脚本里先留空，因为每年安排不同，需要单独维护。常见做法是建一张dim_holiday_rule表，按年份录入，再用 UPDATE JOIN 回填dim_calendar。这样每年只需更新一次规则表。

3.2 农历数据的来源与灌入策略

农历数据没法用标准库直接算，需要一份农历对照数据。常见做法是找一份公开的农历数据文件（通常是按年压缩的十六进制串，每年一个整数，编码了该年每个月的天数、闰月位置和春节日期），解析后逐日展开。这里不贴具体数据文件，只讲解析思路，因为数据本身需要你自行准备并核对。

# 假设 lunar_data 是 {年份: 编码整数} 的字典，编码规则为公开农历算法 # 每年编码的低 4 位表示闰月月份，0 表示无闰月 # 其余位表示 13 个月的大小月（1 为大月 30 天，0 为小月 29 天） def parse_year(year, code): leap_month = code & 0xF # 闰月月份，0 为无闰 months = [] for i in range(12, -1, -1): # 从高位到低位取 13 个月 months.append(30 if (code >> i) & 1 else 29) return leap_month, months # 展开时：先定位该年春节对应的公历日期，再逐月逐日推进 # 遇到闰月时，在对应月份后插入一个 is_leap_month=1 的月

逻辑说明：农历编码的核心是「闰月位置 + 每月大小」。展开时从春节公历日期起算，按每月 29 或 30 天往后推，遇到闰月就多推一个月。参数上，leap_month为 0 表示该年无闰月，非 0 时表示闰几月。灌入时同样用executemany，并且要和公历表按dt对齐——每生成一个农历日，就对应一个公历日期，直接写入dim_lunar_calendar的dt。

注意：农历数据必须做交叉校验。我一般会抽查几个已知日期，比如某年春节、中秋对应的公历日期，确认解析无误再全量灌入。农历算错是「静默错误」，不校验根本发现不了。

3.3 灌数据时的性能参数

7 万行数据，默认配置下executemany可能因为每次提交都刷盘而变慢。几个可调参数：把autocommit关掉，手动commit；批量大小控制在 5000 到 10000 行一次；如果表上有多个二级索引，灌数据前可以先ALTER TABLE ... DISABLE KEYS，灌完再ENABLE KEYS。另外innodb_flush_log_at_trx_commit在灌数据期间临时设为 2，能明显提速，但生产环境别长期这么设。

4. 查询怎么写：把日历表用出价值

4.1 工作日、节假日统计的标准写法

最常见的需求是「统计某月的工作日天数」。有了日历表，这就是一句 GROUP BY：

-- 统计 2024 年每个月的工作日天数（排除周末和法定节假日，补班日算工作日） SELECT m, SUM(CASE WHEN is_holiday = 1 THEN 0 WHEN workday_shift = 'work' THEN 1 WHEN is_weekend = 1 THEN 0 ELSE 1 END) AS workday_cnt FROM dim_calendar WHERE y = 2024 GROUP BY m ORDER BY m;

逻辑说明：判断顺序很重要——先看是否法定节假日（放假），再看是否补班（上班），最后才看是否周末。这个顺序对应了真实的考勤规则：法定节假日即使落在工作日也放假，补班的周末要上班。参数上，workday_shift的取值约定为work和rest，全表统一。

4.2 农历生日提醒的 JOIN 写法

会员生日营销常要按农历提醒。因为农历生日每年对应的公历日期不同，必须用农历表反查：

-- 查今天农历生日对应的会员（假设会员表存了农历月日） SELECT m.member_id, m.name, l.lunar_m_name, l.lunar_d_name FROM dim_lunar_calendar l JOIN member m ON m.lunar_m = l.lunar_m AND m.lunar_d = l.lunar_d AND m.is_leap = l.is_leap_month WHERE l.dt = CURDATE();

逻辑说明：JOIN 条件里必须带上is_leap_month，否则闰月生日会和普通月生日混淆。l.dt = CURDATE()让查询只扫一行公历日期，走主键，极快。参数上，会员表要存lunar_m、lunar_d、is_leap三个字段，缺一不可。

4.3 跨年周统计的坑：ISO 周和业务周的区别

用week_of_year做周统计时，跨年那几周最容易翻车。ISO 口径下，2024 年 12 月 30 日和 31 日属于 2025 年的第 1 周。如果你的业务口径是「自然周」，这两天的归属就不同。解决办法是在日历表里同时保留两套周字段，或者明确全公司统一用 ISO 口径。我一般会在表注释里写清楚口径，并在生成脚本里加一行校验：每年第 1 周和第 53 周的天数是否符合预期。

5. 避坑与排查：日历表落地时最容易翻车的五件事

5.1 农历闰月判断漏了 is_leap_month

现象：农历生日提醒在闰月那年多发或漏发。原因：查询时只匹配了月和日，没匹配闰月标记。解决：JOIN 条件必须带is_leap_month，并且会员表要存这个字段。如果历史数据没存，需要按出生年份回查农历表补全。

5.2 周末和调休混为一谈

现象：考勤统计里补班的周六没算成工作日。原因：只用了is_weekend判断，忽略了workday_shift。解决：判断逻辑按「法定节假日 → 补班 → 周末」的顺序写，补班优先级高于周末。这个顺序写反了，结果会差好几天。

5.3 用 DATE_FORMAT 关联日历表导致索引失效

现象：查询慢，EXPLAIN 显示全表扫描。原因：JOIN 条件写成DATE_FORMAT(t.biz_date,'%Y-%m-%d') = c.dt。解决：业务表的日期字段直接用 DATE 类型，JOIN 时t.biz_date = c.dt，让两边都走索引。如果业务表存的是字符串，先改类型或加函数索引。

5.4 灌数据时字符集不一致导致农历月名乱码

现象：lunar_m_name显示成问号。原因：连接字符集是 latin1，表是 utf8mb4。解决：连接串里显式指定charset='utf8mb4'，建表也统一 utf8mb4。农历月名里有「闰」这类字，字符集不对必然乱码。

5.5 年份边界没覆盖导致查询返回空

现象：查 2100 年 12 月的日期，结果为空。原因：生成脚本的结束日期写成了2100-12-30或循环条件用了<。解决：结束日期明确写2100-12-31，循环用<=，并在灌完后SELECT MAX(dt)确认边界。边界差一天，跨年报表就少一行。

6. 让日历表长期可用的两个进阶习惯

第一个习惯是「规则与数据分离」。日历表本身是数据，但节假日和调休是规则。我一般会额外建一张dim_holiday_rule表，字段是年份、日期、类型、名称，每年更新一次，然后用一条 UPDATE JOIN 回填dim_calendar的is_holiday和workday_shift。这样做的好处是：日历表可以一次性生成好几年不用动，规则表每年维护，职责清晰。回填语句大概长这样：

UPDATE dim_calendar c JOIN dim_holiday_rule r ON c.dt = r.dt SET c.is_holiday = r.is_holiday, c.holiday_name = r.holiday_name, c.workday_shift = r.workday_shift WHERE c.y = 2024;

逻辑说明：dim_holiday_rule只存有特殊安排的日期，普通日期不在表里，JOIN 不上就保持默认值 0。参数上，is_holiday和workday_shift的取值要在规则表里约束好，避免出现is_holiday=1同时workday_shift='work'这种矛盾数据。

第二个习惯是「加校验查询」。日历表最怕静默错误，我一般会写几条固定校验 SQL，每次更新数据后跑一遍。比如校验每年天数：平年 365、闰年 366；校验每月天数与days_in_month一致；校验农历每年月份数要么 12 要么 13。下面这条查公历天数异常：

-- 找出天数不是 365 或 366 的年份 SELECT y, COUNT(*) AS day_cnt FROM dim_calendar GROUP BY y HAVING day_cnt NOT IN (365, 366);

正常应该返回空。如果返回了行，说明生成脚本有 bug，赶紧查。农历那边同理，校验每年月份数：

SELECT lunar_y, COUNT(DISTINCT CONCAT(lunar_m, '-', is_leap_month)) AS month_cnt FROM dim_lunar_calendar GROUP BY lunar_y HAVING month_cnt NOT IN (12, 13);

这两条校验我每次灌完数据必跑，跑通才敢上线。血泪经验是：日历表的错误不会报错，只会让业务数据悄悄偏一天，等发现时已经过去几个月，后悔药都没得吃。

最后说个我自己的习惯：日历表生成脚本一定进版本库，并且把「数据年份范围」写成配置项。哪天业务要扩到 2200 年，改一个配置重跑就行，不用翻代码找硬编码。这套东西搭一次，后面几年都不用再碰日期逻辑，省下来的时间够你处理更值钱的问题。希望帮到你。

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