简介这是一份覆盖1970年1月1日至2100年12月31日的完整万年历MySQL数据库资源面向需要日期维度数据的开发者、数据分析人员及后端工程师可用于日历查询、节假日统计、报表按日聚合等场景省去自行推算农历与公历对应关系的繁琐工作。压缩包共3个文件以1个sql脚本为主内含完整建表语句与逐日插入语句另附2张png截图用于展示数据表结构与数据效果整体约872KB导入前注意将数据库编码设为utf8创建后直接执行脚本即可使用。目前已有1433人学习下载说明该数据在日期维度建模中具有一定实用价值。数据时间跨度长达131年字段信息齐全可直接作为基础维表接入业务系统减少重复造轮子的时间成本适合对数据完整性要求较高的项目参考使用。1. 万年历数据库到底解决什么问题从 1970 到 2100 的日期查询为什么值得单独建表很多做业务系统的开发者都遇到过这种场景考勤系统要判断某天是不是法定节假日、排班系统要区分工作日和周末、金融系统要按交易日历计算 T1 到账日、IoT 设备要按农历节气触发定时任务。这些需求有一个共同点——它们都需要一个「查一下就知道」的日期字典。你当然可以用代码实时计算但每次调用都跑一遍日期算法在高并发下就是白白烧 CPU。更麻烦的是节假日调休这种数据根本没法用公式算出来必须人工维护。万年历数据库的思路很直接把 1970 年 1 月 1 日到 2100 年 12 月 31 日这 47847 天的所有日期属性提前算好、存好业务侧只做一次SELECT就能拿到全部信息。这个方案适合做考勤、排班、金融交易日历、定时任务调度、会员生日提醒等需要频繁查询日期属性的系统。下面我从表结构设计开始一步步把建表和插入语句讲清楚。2. 表结构怎么设计字段拆解与索引策略2.1 核心字段与类型选择设计这张表之前先想清楚要存哪些维度。一个完整的万年历记录至少包含以下几类信息字段名类型说明idINT UNSIGNED AUTO_INCREMENT主键自增full_dateDATE公历日期唯一yearSMALLINT年份monthTINYINT月份 1-12dayTINYINT日 1-31day_of_weekTINYINT星期几1周一 7周日day_of_yearSMALLINT一年中的第几天week_of_yearTINYINTISO 周数quarterTINYINT季度 1-4is_weekendTINYINT(1)是否周末is_holidayTINYINT(1)是否法定节假日is_workdayTINYINT(1)是否调休上班日holiday_nameVARCHAR(32)节日名称lunar_yearSMALLINT农历年lunar_monthTINYINT农历月lunar_dayTINYINT农历日lunar_textVARCHAR(20)农历文字表示如「正月初一」ganzhi_yearVARCHAR(10)干支纪年如「甲子」shengxiaoVARCHAR(4)生肖solar_termVARCHAR(10)节气名称字段看着多但每个都有明确用途。full_date用DATE而不是VARCHAR是因为日期范围查询和比较用原生日期类型效率高得多。day_of_week存 1-7 而不是 0-6是为了跟 ISO 8601 标准对齐周一作为一周开始做周统计时不用额外偏移。2.2 建表语句与索引设计CREATE TABLE calendar ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, full_date DATE NOT NULL COMMENT 公历日期, year SMALLINT UNSIGNED NOT NULL COMMENT 年份, month TINYINT UNSIGNED NOT NULL COMMENT 月份, day TINYINT UNSIGNED NOT NULL COMMENT 日, day_of_week TINYINT UNSIGNED NOT NULL COMMENT 星期几 1周一 7周日, day_of_year SMALLINT UNSIGNED NOT NULL COMMENT 一年中第几天, week_of_year TINYINT UNSIGNED NOT NULL COMMENT ISO周数, quarter TINYINT UNSIGNED NOT NULL COMMENT 季度, is_weekend TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否周末, is_holiday TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否法定节假日, is_workday TINYINT(1) NOT NULL DEFAULT 1 COMMENT 是否工作日(含调休), holiday_name VARCHAR(32) DEFAULT NULL COMMENT 节日名称, lunar_year SMALLINT UNSIGNED DEFAULT NULL COMMENT 农历年, lunar_month TINYINT UNSIGNED DEFAULT NULL COMMENT 农历月, lunar_day TINYINT UNSIGNED DEFAULT NULL COMMENT 农历日, lunar_text VARCHAR(20) DEFAULT NULL COMMENT 农历文字, ganzhi_year VARCHAR(10) DEFAULT NULL COMMENT 干支纪年, shengxiao VARCHAR(4) DEFAULT NULL COMMENT 生肖, solar_term VARCHAR(10) DEFAULT NULL COMMENT 节气, PRIMARY KEY (id), UNIQUE KEY uk_full_date (full_date), KEY idx_year_month (year, month), KEY idx_year_week (year, week_of_year), KEY idx_workday (is_workday, full_date), KEY idx_holiday (is_holiday, full_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT万年历日期字典表;这里有几个设计决策值得展开说。uk_full_date唯一索引保证日期不重复同时它也是最高频的查询入口——业务侧 90% 的查询都是「给我某一天的全部属性」。idx_year_month覆盖按月统计的场景比如「查 2025 年 3 月所有工作日」。idx_workday和idx_holiday是复合索引把布尔字段放前面、日期放后面是因为这两个字段的区分度低工作日占多数单独建索引没意义但配合日期范围查询就能走索引扫描。提示如果你的 MySQL 版本是 5.7 以下utf8mb4的索引长度限制可能导致建表失败需要把VARCHAR(32)改成VARCHAR(20)或调整innodb_large_prefix参数。2.3 为什么不用分区表有人会问47847 行数据要不要做分区我的经验是不要。这个数据量对 InnoDB 来说非常小全表扫描也就几十毫秒分区带来的维护成本反而更高。真正需要担心的是插入效率——一次性插入 4 万多行如果不做优化可能要跑好几分钟。3. 数据怎么生成从公历计算到农历转换的完整插入方案3.1 用存储过程批量生成公历数据直接手写 47847 条 INSERT 语句不现实正确做法是用存储过程循环生成。下面这段代码生成公历部分的基础数据DELIMITER $$ CREATE PROCEDURE generate_calendar_base() BEGIN DECLARE v_date DATE DEFAULT 1970-01-01; DECLARE v_end DATE DEFAULT 2100-12-31; DECLARE v_dow TINYINT; DECLARE v_doy SMALLINT; DECLARE v_woy TINYINT; DECLARE v_quarter TINYINT; DECLARE v_is_weekend TINYINT(1); WHILE v_date v_end DO -- DAYOFWEEK: 1周日 7周六转换为 1周一 7周日 SET v_dow CASE DAYOFWEEK(v_date) WHEN 1 THEN 7 ELSE DAYOFWEEK(v_date) - 1 END; SET v_doy DAYOFYEAR(v_date); SET v_woy WEEK(v_date, 3); -- ISO 8601 周数 SET v_quarter QUARTER(v_date); SET v_is_weekend IF(v_dow 6, 1, 0); INSERT INTO calendar ( full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday ) VALUES ( v_date, YEAR(v_date), MONTH(v_date), DAY(v_date), v_dow, v_doy, v_woy, v_quarter, v_is_weekend, IF(v_is_weekend 1, 0, 1) ); SET v_date DATE_ADD(v_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL generate_calendar_base();这段存储过程的逻辑很直白从 1970-01-01 开始每次加一天算出星期、年内天数、周数、季度、是否周末然后插入。WEEK(v_date, 3)的第二个参数 3 表示按 ISO 8601 标准计算周数周一为一周开始包含 1 月 4 日的那一周是第 1 周。DAYOFWEEK函数返回 1 表示周日所以要做一个映射把周一变成 1、周日变成 7。参数说明v_date是循环变量v_end是终止日期。如果你只需要到 2050 年把v_end改掉就行。执行时间取决于服务器性能一般 4 万多行插入在 30 秒到 2 分钟之间。3.2 农历转换的三种落地路径公历数据用 MySQL 内置函数就能算但农历不行。农历是阴阳合历月份天数不固定还有闰月没法用简单公式推导。常见的做法有三种第一种是查表法。把 1900 年到 2100 年的农历数据压缩成一个整数数组每个整数用二进制位表示这一年每个月的大小月和闰月信息。这是最可靠的做法精度取决于表数据本身。网上流传的农历数据表通常是一个 200 个元素的数组每个元素是一个十六进制数。第二种是调用外部程序。用 Python 的lunardate或zhdate库算出结果导出成 CSV再用LOAD DATA INFILE导入 MySQL。这种做法适合一次性生成后续不再依赖外部程序。第三种是纯 SQL 实现。把农历算法用存储过程写出来但代码量很大而且容易在边界年份出错。我一般不建议这么做维护成本太高。我自己的习惯是用 Python 脚本生成完整的 CSV 文件包含公历和农历所有字段然后用LOAD DATA LOCAL INFILE一次性导入。这样生成和导入分离出错了也容易排查。# generate_calendar_csv.py import csv from datetime import date, timedelta from lunardate import LunarDate start date(1970, 1, 1) end date(2100, 12, 31) current start # 干支和生肖的基础数据 tiangan 甲乙丙丁戊己庚辛壬癸 dizhi 子丑寅卯辰巳午未申酉戌亥 shengxiao 鼠牛虎兔龙蛇马羊猴鸡狗猪 with open(calendar_data.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday, lunar_year, lunar_month, lunar_day, lunar_text, ganzhi_year, shengxiao]) while current end: dow current.isoweekday() # 1周一 7周日 doy current.timetuple().tm_yday woy current.isocalendar()[1] quarter (current.month - 1) // 3 1 is_weekend 1 if dow 6 else 0 is_workday 0 if is_weekend else 1 # 农历转换 lunar LunarDate.fromSolarDate(current.year, current.month, current.day) lunar_text f{lunar.month}月{lunar.day}日 # 干支纪年以立春为界这里简化用农历年计算 gan_idx (lunar.year - 4) % 10 zhi_idx (lunar.year - 4) % 12 ganzhi tiangan[gan_idx] dizhi[zhi_idx] sx shengxiao[zhi_idx] writer.writerow([current.isoformat(), current.year, current.month, current.day, dow, doy, woy, quarter, is_weekend, is_workday, lunar.year, lunar.month, lunar.day, lunar_text, ganzhi, sx]) current timedelta(days1) print(CSV 生成完毕)这段脚本的核心是LunarDate.fromSolarDate它把公历日期转成农历。干支纪年的计算用(农历年 - 4) % 10和% 12分别取天干和地支的索引因为公元 4 年是甲子年。生肖直接用地支索引去取。注意农历转换的边界在春节前后容易出错因为干支纪年传统上以立春为界但很多库以农历正月初一为界。如果你的业务对干支要求严格需要额外处理立春的分界。3.3 导入 CSV 并补充节假日数据CSV 生成后用 MySQL 的LOAD DATA导入LOAD DATA LOCAL INFILE /path/to/calendar_data.csv INTO TABLE calendar FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday, lunar_year, lunar_month, lunar_day, lunar_text, ganzhi_year, shengxiao);导入完成后节假日和调休数据需要单独维护。这部分没有规律只能按年更新。建一张临时表存节假日配置然后 UPDATE 主表-- 节假日配置表 CREATE TABLE holiday_config ( full_date DATE NOT NULL, holiday_name VARCHAR(32) DEFAULT NULL, is_holiday TINYINT(1) DEFAULT 0, is_workday TINYINT(1) DEFAULT 1, PRIMARY KEY (full_date) ); -- 示例插入元旦和春节调休 INSERT INTO holiday_config VALUES (2025-01-01, 元旦, 1, 0), (2025-01-26, 春节调休, 0, 1), (2025-01-28, 春节, 1, 0), (2025-01-29, 春节, 1, 0), (2025-01-30, 春节, 1, 0), (2025-01-31, 春节, 1, 0), (2025-02-01, 春节, 1, 0), (2025-02-02, 春节, 1, 0), (2025-02-08, 春节调休, 0, 1); -- 更新主表 UPDATE calendar c JOIN holiday_config h ON c.full_date h.full_date SET c.is_holiday h.is_holiday, c.is_workday h.is_workday, c.holiday_name h.holiday_name;节假日配置表的好处是每年只需要维护几十条记录更新逻辑清晰不会污染主表的基础数据。4. 避坑指南日期数据库落地时最容易翻车的五个地方4.1 时区问题导致日期偏移现象插入的日期是 2025-01-01查询出来变成 2024-12-31。原因MySQL 的DATE类型本身不带时区但如果连接层设置了time_zone参数且插入时用了NOW()或CURRENT_DATE就会受服务器时区影响。更隐蔽的是某些 ORM 框架会把DATE当DATETIME处理自动做时区转换。解决插入日期时一律用字符串字面量2025-01-01不要用NOW()。连接参数里显式设置time_zone08:00避免依赖服务器默认值。4.2 农历数据在闰月年份错位现象2023 年有闰二月但生成的农历数据里二月只有 29 天没有闰月。原因部分农历转换库对闰月的处理不完整或者数据表本身有缺失。闰月的判断依赖前一年的天文数据不是所有库都覆盖了 1970-2100 全范围。解决生成完 CSV 后抽查几个已知闰月年份2023 闰二月、2025 闰六月、2028 闰五月确认闰月日期存在。如果库不支持换用数据表方案。4.3 批量插入时事务日志爆满现象LOAD DATA执行到一半报错The table calendar is full或磁盘空间不足。原因InnoDB 的 redo log 和 undo log 在批量插入时会快速增长如果innodb_log_file_size设置太小就会触发频繁 checkpoint甚至写满磁盘。解决导入前临时调大innodb_log_file_size和innodb_buffer_pool_size或者分批导入——每 5000 行提交一次。用存储过程生成时可以在循环里加IF v_doy % 5000 0 THEN COMMIT; END IF;。4.4 索引过多拖慢导入速度现象建了 5 个索引后导入 4 万行花了 10 分钟。原因每插入一行所有索引都要更新。索引越多写入放大越严重。解决先建表但不建索引导入完成后再ALTER TABLE ADD INDEX。这样索引只需要构建一次比逐行维护快得多。我的习惯是导入前只保留主键导入后统一加索引。4.5 节假日更新覆盖了基础字段现象更新 2025 年节假日配置后发现某些日期的is_weekend变成了 0。原因UPDATE ... JOIN语句里如果 SET 了不该更新的字段或者holiday_config表里有多余字段被误关联。解决UPDATE 时只 SET 需要变更的字段不要用c.* h.*这种写法。另外holiday_config表的主键是full_dateJOIN 时确保没有重复日期。5. 查询优化与进阶用法让万年历表真正跑得快5.1 高频查询场景的 SQL 模板这张表建好之后业务侧的查询无非几种模式。我把最常用的几条 SQL 整理出来你可以直接抄。查某一天的完整信息SELECT * FROM calendar WHERE full_date 2025-06-15;这条走uk_full_date唯一索引毫秒级返回。查某个月的所有工作日SELECT full_date, day_of_week, holiday_name FROM calendar WHERE year 2025 AND month 6 AND is_workday 1 ORDER BY full_date;这条走idx_year_month然后回表过滤is_workday。如果这个查询特别频繁可以考虑把is_workday加到idx_year_month后面变成(year, month, is_workday)覆盖索引。查两个日期之间的工作日天数SELECT COUNT(*) FROM calendar WHERE full_date BETWEEN 2025-01-01 AND 2025-12-31 AND is_workday 1;这条走idx_workday的日期范围扫描。注意is_workday在索引里排在full_date前面所以实际执行时可能走全表扫描。更好的索引设计是(is_workday, full_date)让等值条件在前、范围条件在后。查下一个工作日SELECT full_date FROM calendar WHERE full_date 2025-06-15 AND is_workday 1 ORDER BY full_date LIMIT 1;这条查询在idx_workday上做范围扫描效率取决于下一个工作日距离当前日期有多远。最长的情况是春节连休 8 天扫描 8 行就够。5.2 用生成列减少存储冗余MySQL 5.7 以上支持生成列可以把一些计算字段做成虚拟列不占存储空间但能建索引。比如day_of_week完全可以从full_date推导ALTER TABLE calendar ADD COLUMN dow_virtual TINYINT GENERATED ALWAYS AS ( CASE DAYOFWEEK(full_date) WHEN 1 THEN 7 ELSE DAYOFWEEK(full_date) - 1 END ) VIRTUAL;虚拟列的好处是不占磁盘查询时实时计算。但如果你需要频繁按星期几筛选还是建议用物理列因为虚拟列每次查询都要算一遍。5.3 验证数据完整性的三条 SQL数据导入后别急着上线先跑几条校验 SQL。检查日期是否有断档SELECT COUNT(*) AS total_days, DATEDIFF(2100-12-31, 1970-01-01) 1 AS expected_days FROM calendar;两个数字应该相等都是 47847。如果不相等说明有日期缺失。检查星期计算是否正确SELECT full_date, day_of_week FROM calendar WHERE full_date IN (1970-01-01, 2000-01-01, 2025-06-15);1970-01-01 是周四day_of_week应该是 42000-01-01 是周六应该是 62025-06-15 是周日应该是 7。检查节假日和周末的逻辑一致性SELECT COUNT(*) FROM calendar WHERE is_weekend 1 AND is_workday 1 AND is_holiday 0;正常情况下这个数字应该是 0因为周末要么是休息日要么是调休上班日此时is_workday 1 但is_holiday 0 且is_weekend 1 是合理的。如果出现大量异常说明更新逻辑有问题。5.4 我踩过的一个坑最后说一个我自己的血泪经验。最早做这张表的时候我图省事把农历字段全部设成NOT NULL结果导入时发现 2100 年附近的农历数据在某些库里算不出来直接报错中断。后来改成DEFAULT NULL导入顺利完成业务侧查询时用COALESCE(lunar_text, )兜底。另一个教训是索引。我一开始建了 8 个索引觉得查询快结果导入花了 15 分钟而且后续每次更新节假日都要重建索引。后来砍到 4 个导入时间降到 2 分钟查询性能几乎没有下降。索引不是越多越好够用就行。这张表一旦建好可以稳定用很多年。每年只需要更新一次节假日配置其他数据都是静态的。如果你正在做考勤、排班或者交易日历相关的系统花半天时间把这张表建起来后面能省掉很多重复计算的麻烦。希望帮到你。本文还有配套的精品资源点击获取 万年历数据库设计：从1970到2100的日期查询与农历转换实战
简介这是一份覆盖1970年1月1日至2100年12月31日的完整万年历MySQL数据库资源面向需要日期维度数据的开发者、数据分析人员及后端工程师可用于日历查询、节假日统计、报表按日聚合等场景省去自行推算农历与公历对应关系的繁琐工作。压缩包共3个文件以1个sql脚本为主内含完整建表语句与逐日插入语句另附2张png截图用于展示数据表结构与数据效果整体约872KB导入前注意将数据库编码设为utf8创建后直接执行脚本即可使用。目前已有1433人学习下载说明该数据在日期维度建模中具有一定实用价值。数据时间跨度长达131年字段信息齐全可直接作为基础维表接入业务系统减少重复造轮子的时间成本适合对数据完整性要求较高的项目参考使用。1. 万年历数据库到底解决什么问题从 1970 到 2100 的日期查询为什么值得单独建表很多做业务系统的开发者都遇到过这种场景考勤系统要判断某天是不是法定节假日、排班系统要区分工作日和周末、金融系统要按交易日历计算 T1 到账日、IoT 设备要按农历节气触发定时任务。这些需求有一个共同点——它们都需要一个「查一下就知道」的日期字典。你当然可以用代码实时计算但每次调用都跑一遍日期算法在高并发下就是白白烧 CPU。更麻烦的是节假日调休这种数据根本没法用公式算出来必须人工维护。万年历数据库的思路很直接把 1970 年 1 月 1 日到 2100 年 12 月 31 日这 47847 天的所有日期属性提前算好、存好业务侧只做一次SELECT就能拿到全部信息。这个方案适合做考勤、排班、金融交易日历、定时任务调度、会员生日提醒等需要频繁查询日期属性的系统。下面我从表结构设计开始一步步把建表和插入语句讲清楚。2. 表结构怎么设计字段拆解与索引策略2.1 核心字段与类型选择设计这张表之前先想清楚要存哪些维度。一个完整的万年历记录至少包含以下几类信息字段名类型说明idINT UNSIGNED AUTO_INCREMENT主键自增full_dateDATE公历日期唯一yearSMALLINT年份monthTINYINT月份 1-12dayTINYINT日 1-31day_of_weekTINYINT星期几1周一 7周日day_of_yearSMALLINT一年中的第几天week_of_yearTINYINTISO 周数quarterTINYINT季度 1-4is_weekendTINYINT(1)是否周末is_holidayTINYINT(1)是否法定节假日is_workdayTINYINT(1)是否调休上班日holiday_nameVARCHAR(32)节日名称lunar_yearSMALLINT农历年lunar_monthTINYINT农历月lunar_dayTINYINT农历日lunar_textVARCHAR(20)农历文字表示如「正月初一」ganzhi_yearVARCHAR(10)干支纪年如「甲子」shengxiaoVARCHAR(4)生肖solar_termVARCHAR(10)节气名称字段看着多但每个都有明确用途。full_date用DATE而不是VARCHAR是因为日期范围查询和比较用原生日期类型效率高得多。day_of_week存 1-7 而不是 0-6是为了跟 ISO 8601 标准对齐周一作为一周开始做周统计时不用额外偏移。2.2 建表语句与索引设计CREATE TABLE calendar ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, full_date DATE NOT NULL COMMENT 公历日期, year SMALLINT UNSIGNED NOT NULL COMMENT 年份, month TINYINT UNSIGNED NOT NULL COMMENT 月份, day TINYINT UNSIGNED NOT NULL COMMENT 日, day_of_week TINYINT UNSIGNED NOT NULL COMMENT 星期几 1周一 7周日, day_of_year SMALLINT UNSIGNED NOT NULL COMMENT 一年中第几天, week_of_year TINYINT UNSIGNED NOT NULL COMMENT ISO周数, quarter TINYINT UNSIGNED NOT NULL COMMENT 季度, is_weekend TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否周末, is_holiday TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否法定节假日, is_workday TINYINT(1) NOT NULL DEFAULT 1 COMMENT 是否工作日(含调休), holiday_name VARCHAR(32) DEFAULT NULL COMMENT 节日名称, lunar_year SMALLINT UNSIGNED DEFAULT NULL COMMENT 农历年, lunar_month TINYINT UNSIGNED DEFAULT NULL COMMENT 农历月, lunar_day TINYINT UNSIGNED DEFAULT NULL COMMENT 农历日, lunar_text VARCHAR(20) DEFAULT NULL COMMENT 农历文字, ganzhi_year VARCHAR(10) DEFAULT NULL COMMENT 干支纪年, shengxiao VARCHAR(4) DEFAULT NULL COMMENT 生肖, solar_term VARCHAR(10) DEFAULT NULL COMMENT 节气, PRIMARY KEY (id), UNIQUE KEY uk_full_date (full_date), KEY idx_year_month (year, month), KEY idx_year_week (year, week_of_year), KEY idx_workday (is_workday, full_date), KEY idx_holiday (is_holiday, full_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT万年历日期字典表;这里有几个设计决策值得展开说。uk_full_date唯一索引保证日期不重复同时它也是最高频的查询入口——业务侧 90% 的查询都是「给我某一天的全部属性」。idx_year_month覆盖按月统计的场景比如「查 2025 年 3 月所有工作日」。idx_workday和idx_holiday是复合索引把布尔字段放前面、日期放后面是因为这两个字段的区分度低工作日占多数单独建索引没意义但配合日期范围查询就能走索引扫描。提示如果你的 MySQL 版本是 5.7 以下utf8mb4的索引长度限制可能导致建表失败需要把VARCHAR(32)改成VARCHAR(20)或调整innodb_large_prefix参数。2.3 为什么不用分区表有人会问47847 行数据要不要做分区我的经验是不要。这个数据量对 InnoDB 来说非常小全表扫描也就几十毫秒分区带来的维护成本反而更高。真正需要担心的是插入效率——一次性插入 4 万多行如果不做优化可能要跑好几分钟。3. 数据怎么生成从公历计算到农历转换的完整插入方案3.1 用存储过程批量生成公历数据直接手写 47847 条 INSERT 语句不现实正确做法是用存储过程循环生成。下面这段代码生成公历部分的基础数据DELIMITER $$ CREATE PROCEDURE generate_calendar_base() BEGIN DECLARE v_date DATE DEFAULT 1970-01-01; DECLARE v_end DATE DEFAULT 2100-12-31; DECLARE v_dow TINYINT; DECLARE v_doy SMALLINT; DECLARE v_woy TINYINT; DECLARE v_quarter TINYINT; DECLARE v_is_weekend TINYINT(1); WHILE v_date v_end DO -- DAYOFWEEK: 1周日 7周六转换为 1周一 7周日 SET v_dow CASE DAYOFWEEK(v_date) WHEN 1 THEN 7 ELSE DAYOFWEEK(v_date) - 1 END; SET v_doy DAYOFYEAR(v_date); SET v_woy WEEK(v_date, 3); -- ISO 8601 周数 SET v_quarter QUARTER(v_date); SET v_is_weekend IF(v_dow 6, 1, 0); INSERT INTO calendar ( full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday ) VALUES ( v_date, YEAR(v_date), MONTH(v_date), DAY(v_date), v_dow, v_doy, v_woy, v_quarter, v_is_weekend, IF(v_is_weekend 1, 0, 1) ); SET v_date DATE_ADD(v_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL generate_calendar_base();这段存储过程的逻辑很直白从 1970-01-01 开始每次加一天算出星期、年内天数、周数、季度、是否周末然后插入。WEEK(v_date, 3)的第二个参数 3 表示按 ISO 8601 标准计算周数周一为一周开始包含 1 月 4 日的那一周是第 1 周。DAYOFWEEK函数返回 1 表示周日所以要做一个映射把周一变成 1、周日变成 7。参数说明v_date是循环变量v_end是终止日期。如果你只需要到 2050 年把v_end改掉就行。执行时间取决于服务器性能一般 4 万多行插入在 30 秒到 2 分钟之间。3.2 农历转换的三种落地路径公历数据用 MySQL 内置函数就能算但农历不行。农历是阴阳合历月份天数不固定还有闰月没法用简单公式推导。常见的做法有三种第一种是查表法。把 1900 年到 2100 年的农历数据压缩成一个整数数组每个整数用二进制位表示这一年每个月的大小月和闰月信息。这是最可靠的做法精度取决于表数据本身。网上流传的农历数据表通常是一个 200 个元素的数组每个元素是一个十六进制数。第二种是调用外部程序。用 Python 的lunardate或zhdate库算出结果导出成 CSV再用LOAD DATA INFILE导入 MySQL。这种做法适合一次性生成后续不再依赖外部程序。第三种是纯 SQL 实现。把农历算法用存储过程写出来但代码量很大而且容易在边界年份出错。我一般不建议这么做维护成本太高。我自己的习惯是用 Python 脚本生成完整的 CSV 文件包含公历和农历所有字段然后用LOAD DATA LOCAL INFILE一次性导入。这样生成和导入分离出错了也容易排查。# generate_calendar_csv.py import csv from datetime import date, timedelta from lunardate import LunarDate start date(1970, 1, 1) end date(2100, 12, 31) current start # 干支和生肖的基础数据 tiangan 甲乙丙丁戊己庚辛壬癸 dizhi 子丑寅卯辰巳午未申酉戌亥 shengxiao 鼠牛虎兔龙蛇马羊猴鸡狗猪 with open(calendar_data.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday, lunar_year, lunar_month, lunar_day, lunar_text, ganzhi_year, shengxiao]) while current end: dow current.isoweekday() # 1周一 7周日 doy current.timetuple().tm_yday woy current.isocalendar()[1] quarter (current.month - 1) // 3 1 is_weekend 1 if dow 6 else 0 is_workday 0 if is_weekend else 1 # 农历转换 lunar LunarDate.fromSolarDate(current.year, current.month, current.day) lunar_text f{lunar.month}月{lunar.day}日 # 干支纪年以立春为界这里简化用农历年计算 gan_idx (lunar.year - 4) % 10 zhi_idx (lunar.year - 4) % 12 ganzhi tiangan[gan_idx] dizhi[zhi_idx] sx shengxiao[zhi_idx] writer.writerow([current.isoformat(), current.year, current.month, current.day, dow, doy, woy, quarter, is_weekend, is_workday, lunar.year, lunar.month, lunar.day, lunar_text, ganzhi, sx]) current timedelta(days1) print(CSV 生成完毕)这段脚本的核心是LunarDate.fromSolarDate它把公历日期转成农历。干支纪年的计算用(农历年 - 4) % 10和% 12分别取天干和地支的索引因为公元 4 年是甲子年。生肖直接用地支索引去取。注意农历转换的边界在春节前后容易出错因为干支纪年传统上以立春为界但很多库以农历正月初一为界。如果你的业务对干支要求严格需要额外处理立春的分界。3.3 导入 CSV 并补充节假日数据CSV 生成后用 MySQL 的LOAD DATA导入LOAD DATA LOCAL INFILE /path/to/calendar_data.csv INTO TABLE calendar FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (full_date, year, month, day, day_of_week, day_of_year, week_of_year, quarter, is_weekend, is_workday, lunar_year, lunar_month, lunar_day, lunar_text, ganzhi_year, shengxiao);导入完成后节假日和调休数据需要单独维护。这部分没有规律只能按年更新。建一张临时表存节假日配置然后 UPDATE 主表-- 节假日配置表 CREATE TABLE holiday_config ( full_date DATE NOT NULL, holiday_name VARCHAR(32) DEFAULT NULL, is_holiday TINYINT(1) DEFAULT 0, is_workday TINYINT(1) DEFAULT 1, PRIMARY KEY (full_date) ); -- 示例插入元旦和春节调休 INSERT INTO holiday_config VALUES (2025-01-01, 元旦, 1, 0), (2025-01-26, 春节调休, 0, 1), (2025-01-28, 春节, 1, 0), (2025-01-29, 春节, 1, 0), (2025-01-30, 春节, 1, 0), (2025-01-31, 春节, 1, 0), (2025-02-01, 春节, 1, 0), (2025-02-02, 春节, 1, 0), (2025-02-08, 春节调休, 0, 1); -- 更新主表 UPDATE calendar c JOIN holiday_config h ON c.full_date h.full_date SET c.is_holiday h.is_holiday, c.is_workday h.is_workday, c.holiday_name h.holiday_name;节假日配置表的好处是每年只需要维护几十条记录更新逻辑清晰不会污染主表的基础数据。4. 避坑指南日期数据库落地时最容易翻车的五个地方4.1 时区问题导致日期偏移现象插入的日期是 2025-01-01查询出来变成 2024-12-31。原因MySQL 的DATE类型本身不带时区但如果连接层设置了time_zone参数且插入时用了NOW()或CURRENT_DATE就会受服务器时区影响。更隐蔽的是某些 ORM 框架会把DATE当DATETIME处理自动做时区转换。解决插入日期时一律用字符串字面量2025-01-01不要用NOW()。连接参数里显式设置time_zone08:00避免依赖服务器默认值。4.2 农历数据在闰月年份错位现象2023 年有闰二月但生成的农历数据里二月只有 29 天没有闰月。原因部分农历转换库对闰月的处理不完整或者数据表本身有缺失。闰月的判断依赖前一年的天文数据不是所有库都覆盖了 1970-2100 全范围。解决生成完 CSV 后抽查几个已知闰月年份2023 闰二月、2025 闰六月、2028 闰五月确认闰月日期存在。如果库不支持换用数据表方案。4.3 批量插入时事务日志爆满现象LOAD DATA执行到一半报错The table calendar is full或磁盘空间不足。原因InnoDB 的 redo log 和 undo log 在批量插入时会快速增长如果innodb_log_file_size设置太小就会触发频繁 checkpoint甚至写满磁盘。解决导入前临时调大innodb_log_file_size和innodb_buffer_pool_size或者分批导入——每 5000 行提交一次。用存储过程生成时可以在循环里加IF v_doy % 5000 0 THEN COMMIT; END IF;。4.4 索引过多拖慢导入速度现象建了 5 个索引后导入 4 万行花了 10 分钟。原因每插入一行所有索引都要更新。索引越多写入放大越严重。解决先建表但不建索引导入完成后再ALTER TABLE ADD INDEX。这样索引只需要构建一次比逐行维护快得多。我的习惯是导入前只保留主键导入后统一加索引。4.5 节假日更新覆盖了基础字段现象更新 2025 年节假日配置后发现某些日期的is_weekend变成了 0。原因UPDATE ... JOIN语句里如果 SET 了不该更新的字段或者holiday_config表里有多余字段被误关联。解决UPDATE 时只 SET 需要变更的字段不要用c.* h.*这种写法。另外holiday_config表的主键是full_dateJOIN 时确保没有重复日期。5. 查询优化与进阶用法让万年历表真正跑得快5.1 高频查询场景的 SQL 模板这张表建好之后业务侧的查询无非几种模式。我把最常用的几条 SQL 整理出来你可以直接抄。查某一天的完整信息SELECT * FROM calendar WHERE full_date 2025-06-15;这条走uk_full_date唯一索引毫秒级返回。查某个月的所有工作日SELECT full_date, day_of_week, holiday_name FROM calendar WHERE year 2025 AND month 6 AND is_workday 1 ORDER BY full_date;这条走idx_year_month然后回表过滤is_workday。如果这个查询特别频繁可以考虑把is_workday加到idx_year_month后面变成(year, month, is_workday)覆盖索引。查两个日期之间的工作日天数SELECT COUNT(*) FROM calendar WHERE full_date BETWEEN 2025-01-01 AND 2025-12-31 AND is_workday 1;这条走idx_workday的日期范围扫描。注意is_workday在索引里排在full_date前面所以实际执行时可能走全表扫描。更好的索引设计是(is_workday, full_date)让等值条件在前、范围条件在后。查下一个工作日SELECT full_date FROM calendar WHERE full_date 2025-06-15 AND is_workday 1 ORDER BY full_date LIMIT 1;这条查询在idx_workday上做范围扫描效率取决于下一个工作日距离当前日期有多远。最长的情况是春节连休 8 天扫描 8 行就够。5.2 用生成列减少存储冗余MySQL 5.7 以上支持生成列可以把一些计算字段做成虚拟列不占存储空间但能建索引。比如day_of_week完全可以从full_date推导ALTER TABLE calendar ADD COLUMN dow_virtual TINYINT GENERATED ALWAYS AS ( CASE DAYOFWEEK(full_date) WHEN 1 THEN 7 ELSE DAYOFWEEK(full_date) - 1 END ) VIRTUAL;虚拟列的好处是不占磁盘查询时实时计算。但如果你需要频繁按星期几筛选还是建议用物理列因为虚拟列每次查询都要算一遍。5.3 验证数据完整性的三条 SQL数据导入后别急着上线先跑几条校验 SQL。检查日期是否有断档SELECT COUNT(*) AS total_days, DATEDIFF(2100-12-31, 1970-01-01) 1 AS expected_days FROM calendar;两个数字应该相等都是 47847。如果不相等说明有日期缺失。检查星期计算是否正确SELECT full_date, day_of_week FROM calendar WHERE full_date IN (1970-01-01, 2000-01-01, 2025-06-15);1970-01-01 是周四day_of_week应该是 42000-01-01 是周六应该是 62025-06-15 是周日应该是 7。检查节假日和周末的逻辑一致性SELECT COUNT(*) FROM calendar WHERE is_weekend 1 AND is_workday 1 AND is_holiday 0;正常情况下这个数字应该是 0因为周末要么是休息日要么是调休上班日此时is_workday 1 但is_holiday 0 且is_weekend 1 是合理的。如果出现大量异常说明更新逻辑有问题。5.4 我踩过的一个坑最后说一个我自己的血泪经验。最早做这张表的时候我图省事把农历字段全部设成NOT NULL结果导入时发现 2100 年附近的农历数据在某些库里算不出来直接报错中断。后来改成DEFAULT NULL导入顺利完成业务侧查询时用COALESCE(lunar_text, )兜底。另一个教训是索引。我一开始建了 8 个索引觉得查询快结果导入花了 15 分钟而且后续每次更新节假日都要重建索引。后来砍到 4 个导入时间降到 2 分钟查询性能几乎没有下降。索引不是越多越好够用就行。这张表一旦建好可以稳定用很多年。每年只需要更新一次节假日配置其他数据都是静态的。如果你正在做考勤、排班或者交易日历相关的系统花半天时间把这张表建起来后面能省掉很多重复计算的麻烦。希望帮到你。本文还有配套的精品资源点击获取 RELATED READING
延伸阅读
更多一线实战笔记与深度复盘，助您持续精进
