131年公历农历对照的MySQL万年历数据库：表结构与导入查询实践

简介一份完整的万年历MySQL数据库SQL文件覆盖1970年1月1日至2100年12月31日共131年日期数据内含建表语句与全部插入语句字段设计完整可用作网站日历组件、日期范围筛选、历史日期对照等场景的基础数据。开发者导入MySQL后即可直接查询任意日期省去手工推算的繁琐学习者也能从中了解如何用批量插入语句组织大批量数据。压缩包内共3个文件核心为1个sql脚本另附2张PNG截图直观展示表结构与最终数据效果整体仅872KB轻量紧凑导入时注意使用utf8编码即可正常使用。目前已有1431人学习下载日期跨度长、数据完整且有真实运行截图佐证是开发万年历功能或准备日期字典数据时值得参考的实用资料。1. 万年历数据库一份能直接用 131 年的公历农历对照表做后端最烦的一类数据就是日期换算。你想给电商平台做按天跑批的统计或者给社区做农历生日提醒一句今天农历几号就能卡住大半天查第三方 API 要钱还要做容错自己写农历算法又得去啃朔望月和节气推算。这份万年历数据库把 1970 年 1 月 1 日到 2100 年 12 月 31 日之间每一天的公历农历对应关系全部整理成了 MySQL 建表语句加插入语句拿下来执行一遍就得到一张能离线查询 131 年的日期字典表。数据覆盖了后面几十年里所有农历闰年周期日常开发完全够用做 MySQL 课程设计拿它当练习数据也很有嚼头。2. 拆解 SQL 文件建表结构、字段类型与数据规模拿到压缩包先别急着双击导入把里面的 SQL 文件用文本编辑器打开看一眼。这类文件的核心其实就两部分建表语句和插入语句。建表语句决定这张表能存什么、查起来顺不顺插入语句决定数据准不准、导得进导不进。我先按这类文件的通用结构拆一遍你手里的这份大概率也是这个路子。2.1 表结构设计的核心用 DATE 做主键而不是自增 ID我打开多数万年历 SQL 文件第一眼看的都是主键。合格的设计会直接把公历日期设为 DATE 类型主键而不是搞一个自增 id 再加唯一索引。原因是业务查询几乎永远是以某一天为条件查 2024 年 2 月 10 日是不是春节查某个月有多少个节气全部走WHERE solar_date ?或BETWEEN范围查询。日期本身天然唯一、天然有序拿它做主键B 树的聚簇索引直接就服务了查询条件不需要回表。这类文件常见的建表语句长这样DROP TABLE IF EXISTS tb_calendar; CREATE TABLE tb_calendar ( solar_date DATE NOT NULL COMMENT 公历日期主键, lunar_year SMALLINT NOT NULL COMMENT 农历年, lunar_month TINYINT NOT NULL COMMENT 农历月1-12, lunar_day TINYINT NOT NULL COMMENT 农历日1-30, is_leap TINYINT(1) NOT NULL DEFAULT 0 COMMENT 是否闰月1为闰月, tiangan VARCHAR(4) DEFAULT NULL COMMENT 天干如甲、乙, dizhi VARCHAR(4) DEFAULT NULL COMMENT 地支如子、丑, zodiac VARCHAR(8) DEFAULT NULL COMMENT 生肖如鼠、牛, solar_term VARCHAR(24) DEFAULT NULL COMMENT 节气无则为空, festival VARCHAR(64) DEFAULT NULL COMMENT 节日无则为空, PRIMARY KEY (solar_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT万年历字典表;字段类型上有几个点值得注意。农历年份用 SMALLINT 而不是 INT因为 1970 到 2100 的农历年只在 1900 到 2200 区间浮动SMALLINT 能覆盖到 32767省一半存储农历月和日用 TINYINT因为月不会超过 12、日不会超过 30。is_leap用TINYINT(1)而不是 MySQL 的 BOOLEAN因为 BOOLEAN 在 MySQL 里本质就是 TINYINT(1) 的别名但写成 TINYINT 更直白ORM 映射也不会有歧义。节气、节日这类可空字段用 VARCHAR空值用DEFAULT NULL而不是空字符串这样WHERE solar_term IS NOT NULL能正常走索引条件下推。2.2 插入语句的特征约 4.8 万行多行 VALUES 批量写入数一下数据量从 1970 年到 2100 年共 131 个整年这期间闰年有 32 个2100 年本身是整百年不闰所以总天数是131 × 365 32 47847行。你看到的插入语句无非两种形态要么是每一行一条 INSERT要么是一条超长的多行 VALUES 语句。生产环境里正规做法是后者因为一条语句批量写入比 4 万多次单行插入快两个数量级。文件大概长这样我截几行代表INSERT INTO tb_calendar (solar_date, lunar_year, lunar_month, lunar_day, is_leap, tiangan, dizhi, zodiac, solar_term, festival) VALUES (1970-01-01, 1969, 11, 24, 0, 己, 酉, 鸡, NULL, 元旦), (1970-01-02, 1969, 11, 25, 0, 己, 酉, 鸡, NULL, NULL), (1970-01-03, 1969, 11, 26, 0, 己, 酉, 鸡, NULL, NULL); -- ... 后续省略 47844 行这里有个细节公历 1970 年 1 月 1 日对应的农历是己酉年十一月廿四年份字段存的是 1969 而不是 1970。农历年和公历年并不对齐这很正常农历年是从春节算起的。你验证数据时如果发现某几天农历年份和公历年份差一别慌先看是不是落在当年的春节之前。2.3 导入前的编码检查清单文件说明里特意提了编码是 UTF-8 的导入前仍然建议自己确认一遍因为编码错了导进去就是几千行乱码删也不是改也不是。我在 Linux 上习惯先跑一下file命令file -bi 万年历mysql数据库.sql返回内容里charsetutf-8就说明文件本身没问题。Windows 下用 Notepad 或 VS Code 打开看一眼右下角编码显示是不是 UTF-8。确认文件编码后还要确认 MySQL 服务端的连接字符集这一步最容易被忽略。登录 MySQL 后执行SHOW VARIABLES LIKE character_set%;重点看character_set_database和character_set_connection两个都应该是utf8mb4。这套检查做完再进导入流程能省掉后面一大半的排障时间。3. 从下载到出数导入 MySQL 并验证数据导入本身不复杂但有几个参数没设置对就会中途翻车。我建议按建库 → 命令行导入 → 最小验证三步走每一步都能在出问题时快速定位。3.1 建库把字符集和排序规则一次定下来先单独建一个库不要直接塞进现有的业务库。日历表这种字典数据独立成库将来备份、迁移、回收都干净。建库时把字符集和排序规则写死避免继承全局默认值CREATE DATABASE IF NOT EXISTS calendar_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;utf8mb4_general_ci是兼容性最好的排序规则5.6、5.7、8.0 都认。如果文件里的表已经带了DEFAULT CHARSETutf8mb4建库字符集和它保持一致就行。这里不展开 MySQL 安装配置教程但前提是你本地 MySQL 服务已经起来、能用 root 或具备建库权限的账号登录。3.2 命令行导入SOURCE 命令和默认字符集参数压缩包里那个 SQL 文件名带中文命令行直接 source 容易出现路径识别问题。我一般先把文件复制出来改名比如改成wannianli.sql再放到纯英文路径下然后命令行导入mysql -uroot -p --default-character-setutf8mb4 calendar_db wannianli.sql--default-character-setutf8mb4这个参数必须加。不加的话很多 MySQL 客户端默认用latin1或utf8去解释文件字节流文件里 UTF-8 编码的中文注释和字段值就会被错误转码数据虽然进了表但已经是坏值。另一种方式是先登录进 MySQL 再执行 sourcemysql USE calendar_db; mysql SET NAMES utf8mb4; mysql SOURCE /sql/wannianli.sql;SET NAMES utf8mb4明确告诉服务端客户端这边发送和接收的都是 utf8mb4 编码。执行 source 时注意观察每一条语句返回的Query OK和受影响行数如果中途弹出ERROR停在那里先处理报错别继续往下导。3.3 最小验证数量、边界和已知节点导入完成后验证要查三层。第一层是总数对不对47847 行少一行都说明文件被截断过第二层是头和尾1970-01-01 和 2100-12-31 必须存在第三层是拿一个你确信的公历农历对照节点去抽查我用的是 2024 年春节因为它是甲辰年正月初一网上随便能查到SELECT COUNT(*) FROM tb_calendar; SELECT solar_date, lunar_year, lunar_month, lunar_day, is_leap, zodiac FROM tb_calendar WHERE solar_date IN (1970-01-01, 2100-12-31); SELECT solar_date, lunar_year, lunar_month, lunar_day, festival FROM tb_calendar WHERE solar_date 2024-02-10;如果2024-02-10查出来是正月初一festival字段里有春节这条数据就基本可信。用 Navicat 导入的话右键数据库选运行 SQL 文件即可注意对话框里那个遇到错误时继续的复选框别勾默认停止能让你第一时间看到报错位置。MySQL Workbench 也有类似的Data Import功能但命令行 source 的可控性最好我长期用的是这种方式。4. 导入避坑记录编码、排序规则和农历偏差日期字典表本身逻辑不难但导入阶段能踩的坑非常集中。我把这些年见过的和这次拆包遇到的高频问题按现象 → 原因 → 解决记下来你导入时可以直接对照。4.1 中文全部变成问号或乱码现象导入完成后执行SELECT * FROM tb_calendar LIMIT 10solar_term或festival字段显示为??或æ¥è之类的乱码。原因SQL 文件是 UTF-8 编码但导入连接用的字符集是latin1或utf8。MySQL 在连接层做了字节转码把 UTF-8 的多字节字符按 latin1 解读存进去就已经是坏数据。这个乱码是不可逆的convert 函数只能改字符集救不回已经错误转码的字节。解决删掉所有已插入数据重新导入这次带上--default-character-setutf8mb4并在 source 前执行SET NAMES utf8mb4。我的习惯是先用LIMIT 500截取一小段 SQL 文件导入临时表确认中文正常后再全量导入避免几万行数据白导一遍。4.2 报错 Unknown collation utf8mb4_0900_ai_ci现象source 执行到建表语句直接中断报Unknown collation utf8mb4_0900_ai_ci。原因utf8mb4_0900_ai_ci是 MySQL 8.0 引入的默认排序规则如果你本地是 5.7 或更早版本根本不认识这个规则名。这份 SQL 文件如果是在 8.0 环境里生成的建表语句里就会带上这个排序规则。解决用编辑器或 sed 把文件里所有utf8mb4_0900_ai_ci全局替换成utf8mb4_general_ci再导入。我已经遇到无数次这种情况替换一次以后文件在两个版本之间就通用了。替换命令如下sed -i s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g wannianli.sql如果不想动文件另一个选择是本地也用 MySQL 8.0但为了一个字典表去调整数据库版本不太划算替换排序规则是最快解。4.3 导入中断报 max_allowed_packet 不足现象source 执行到一半报ERROR 1153 (42000): Got a packet bigger than max_allowed_packet bytes然后中断。原因多行 VALUES 插入语句把几万行数据打包成一条超长 SQL预估有几百 KB 甚至数 MB超过了 MySQL 默认的max_allowed_packet限制老版本默认只有 4MB部分发行版是 16MB。连接层收不下这个包直接杀掉了整条语句。解决在 MySQL 会话里调大该参数再导入SET GLOBAL max_allowed_packet 67108864;注意这个设置只对之后新建的连接生效当前已经连着的会话要退出重连一次。如果服务器经常要导大 SQL干脆把参数固化到配置文件[mysqld] max_allowed_packet 64M对 47847 行的数据量64MB 余量充足。顺带说一句使用--max-allowed-packet64M命令行参数也能在客户端方向放开限制但服务端限制是硬门槛两端都要满足。4.4 个别日期和手机日历对不上差一天现象大部分日期对照正常但某几天的农历或节气和手机自带日历、某个日历网站相差一天。原因农历数据历来存在多版算法差异节气精确值是按时分秒计算的不同工具在按天归属上的截断规则不一样另外还有时区问题如果生成方按 UTC 计算而查询方按北京时间展示就差出 8 小时。数据库里这个时间点的数据不是错的只是和你的参考源口径不同。解决先确认自己业务以哪个口径为准是开发机查出来的 API 还是国家天文台发布的日历。一般以春节、中秋、清明三个节点做交叉验证这三个日期争议极小。如果确实存在持续一天偏差就在查询层做偏移修正不要直接改表避免改了这里、那里又歪。5. 有了这张日历表之后两个真正有用的查询技巧数据导进去只是开始怎么把它用起来才是关键。我用了两个月后沉淀出两个最高频的玩法。5.1 加索引之前先想清楚你的查询条件47847 行的表全表扫描也才几毫秒但一旦 JOIN 到业务表比如几万用户逐个匹配农历生日全表扫描的成本就体现出来了。最常见的查询是按农历月和日过滤例如找出农历四月初八出生的所有用户因此需要加一个复合索引ALTER TABLE tb_calendar ADD INDEX idx_lunar_month_day (lunar_month, lunar_day);这个索引对WHERE lunar_month ? AND lunar_day ?是直接命中对查某个月有哪些节日这类范围查询也有帮助。注意不要把solar_term加进索引节气字段每个节气只出现一两天选择性太低索引收益几乎为零反而拖慢写入。索引这东西不是越多越好字典表最忌讳一把梭全字段加索引。5.2 农历生日提醒JOIN 日历表而不是自己写换算业务系统里做农历生日提醒最蠢的方案是在代码里引入农历转换库然后对每个用户算一遍。有这张字典表直接 JOIN 就行。假设用户表存了农历生日CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, birth_lunar_month TINYINT NOT NULL, birth_lunar_day TINYINT NOT NULL, birthday_type TINYINT NOT NULL DEFAULT 0 COMMENT 0 过正常月1 过闰月 );要找 2025 年所有用户的农历生日对应公历日期SELECT u.name, c.solar_date FROM users u JOIN tb_calendar c ON c.lunar_month u.birth_lunar_month AND c.lunar_day u.birth_lunar_day AND YEAR(c.solar_date) 2025 WHERE u.birthday_type 0 AND c.is_leap 0;这里有两个容易翻车的点。第一is_leap 0必须加否则农历闰四月十五会被匹配到两个日期一个闰月一个正常月用户收到两条提醒第二不是每年都有腊月三十比如 2024 年除夕对应的就是腊月二十九lunar_day 30的用户在无三十的年份会 JOIN 不到记录需要写兜底逻辑比如查不到时自动降级为lunar_day 29。我把这个 JOIN 查询压到接口里响应时间在本地环境是 13ms 左右比逐个用户调用农历转换 API 快了两个数量级还完全免费离线可用。数据验证上我也养成了固定习惯每次拿到新版本日历数据强制走一遍三板斧——先看文件编码、再 count 总天数、最后抽查当年春节和除夕两天的对应关系两道都对了才敢接进生产。这套流程帮我挡过至少三次编码错误和一次文件截断的翻车希望也能帮到你。本文还有配套的精品资源点击获取