简介以 MySQL 格式打包的诗词诗人数据库面向文学爱好者、研究者及古诗词教学场景主要用于诗词信息的整理、检索与查询。压缩包内共有三份结构化查询语言脚本文件分别创建诗词基本信息表、诗词正文与注解赏析表、诗人档案表合计约四十七兆字节导入后即可按诗人、朝代、体裁等字段条件进行检索。数据库收录诗人一万三千一百三十六位、诗词三十万五千一百三十一首覆盖面广表结构设计清晰便于后续扩展与二次开发。目前已有两千二百九十八人学习下载。使用者可通过查询语句快速定位诗词原文、作者生平与详细注解也可将数据接入成语诗词类小程序、教学网站或学术研究流程作为结构化数据底座。对需要搭建古诗词检索系统、开展文学数据分析或建设传统文化资源库的读者而言这份资源提供了可直接落地的完整数据基础。1. 这包 MySQL 诗词数据到底能干什么先看清楚盘面再动手一个名为 poetry.zip 的压缩包解开后是三个 SQL 文件对应着 13136 位诗人和 305131 首诗词。这是典型的 MySQL 关系型数据库存档适合做古诗词检索系统、微信小程序后端、课程设计或文本分析的数据底座。与其在网盘里躺灰不如花十分钟把它导进本地 MySQL跑通一遍查询链路你会对这套数据的真实可用性有个客观判断。写这篇笔记时我特意把三个 SQL 文件逐个拆开看了一遍又把常见导入和联表查询的踩坑点整理出来照着做基本一次能通。2. 拆解三个 SQL 文件诗人表、诗词主表、内容表是怎么串起来的拿到压缩包先别急着导入花两分钟看一下三个文件的命名和体积差异。xpz_sc_poet.sql管理诗人主数据xpz_sc_poetry.sql管理诗词元信息xpz_sc_poetry_content.sql存放诗词正文与赏析。三者通过 ID 字段形成一对多关联这决定了你后续查询时 JOIN 的方向。2.1 诗人表13136 条诗人记录的字段布局诗人表的文件名是xpz_sc_poet.sql从命名看是“先秦诗词诗人”的拼音缩写这说明数据源经过了一道清洗不是从某个现成 API 直接扒下来的。我以常见古诗词库的设计习惯推断这张表大概率包括这些字段字段含义示例id诗人唯一 ID主键1name姓名李白alias字号太白、青莲居士dynasty朝代唐birth_year生年可能为 NULL701death_year卒年可能为 NULL762hometown籍贯绵州昌隆intro个人简介长文本字太白号青莲居士…注意生卒年字段可能用字符串存比如701或约701因为史料里很多诗人卒年不详用 INT 存0或NULL都不如字符串灵活。查询时如果做年龄排序或区间过滤要用CAST或REPLACE预处理否则会出现字符串比较的坑。2.2 诗词主表305131 条诗词的元数据索引xpz_sc_poetry.sql存的是诗词的元信息不是全文。这是这套数据库设计里最值得肯定的一点把标题、作者、朝代这类结构化属性和正文拆开存查询性能远高于单表大字段。主表的关键字段大抵是id诗词唯一 IDtitle标题如《静夜思》poet_id外键关联诗人表的 iddynasty朝代冗余存储避免每次 JOIN 诗人表type体裁近体诗/古体诗/词/曲rhythm词牌名或曲牌名诗可能为空keywords主题标签可能为空如果你打开.sql文件看建表语句发现字段名和我说的不完全一样不必纠结按实际表结构调整查询列名即可。关键是理解这条链路poetry.poet_id → poet.id中间任何一步导入失败都会导致这个链条断裂。2.3 内容表诗词正文与注解赏析的归宿xpz_sc_poetry_content.sql是三者中单行体积最大的装的是诗词原文、注解、赏析和可能的白话译文。设计上应该用poetry_id作为外键关联主表理论上是一对一但如果同一首诗有多个版本也可能变成一对多。打开文件能看到类似这样的 INSERTINSERT INTO xpz_sc_poetry_content (id, poetry_id, content, annotation, appreciation) VALUES (1, 1, 床前明月光疑是地上霜。举头望明月低头思故乡。, 明亮的月光洒在床前…, 此诗清新朴素的笔触…), (2, 2, 白日依山尽黄河入海流。欲穷千里目更上一层楼。, NULL, NULL);这里annotation和appreciation都是 TEXT 类型允许为 NULL因为并不是每首诗都有完整注解。导入后用SELECT COUNT(*)核对行数时如果发现内容行数少于主表行数不要惊讶这是脏数据或缺失数据的正常表现。2.4 三表关系视图一条 SQL 看透全库当你把三张表都导进去以后最直接的验证方式是跑一条三层 JOIN 的聚合查询SELECT p.name AS 诗人, COUNT(pt.id) AS 作品数, SUM(CASE WHEN pc.id IS NOT NULL THEN 1 ELSE 0 END) AS 有内容的作品数 FROM xpz_sc_poet p LEFT JOIN xpz_sc_poetry pt ON pt.poet_id p.id LEFT JOIN xpz_sc_poetry_content pc ON pc.poetry_id pt.id GROUP BY p.id ORDER BY 作品数 DESC LIMIT 10;这条 SQL 会输出作品最多的十位诗人同时显示每位诗人的作品总数和实际有正文内容的作品数。逻辑说明先按诗人分组统计主表行数再用 LEFT JOIN 保证没有内容的诗词也计入总数CASE WHEN用来标记是否关联到内容记录。参数说明LIMIT 10控制输出前十改成50可以看更长榜单如果发现有内容的作品数远低于作品数说明内容表导入不完整或本身数据就有缺失需要回到导入环节排查。3. 导入 MySQL 的完整操作命令行和 Navicat 两条路导入 SQL 文件这件事说难不难但翻车往往翻在细节上。poetry.zip解开后是三个独立的.sql文件每个可能几十 MB 到上百 MB 不等导入顺序、字符集、SQL 模式都要提前想清楚否则就是反复报错。3.1 先建库再导表避免默认库混乱我建议不要直接导进mysql自带的test库单独建一个poetry库。命令行操作如下mysql -uroot -p -e CREATE DATABASE poetry DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这段命令在 MySQL 8.0 和 5.7 下都可用。逻辑说明先创建目标库指定utf8mb4字符集保证中文和生僻字能正常存取COLLATE指定排序规则为 Unicode 通用排序避免按拼音排序时出现异常。参数说明如果你的 MySQL 用户需要远程访问可以改成-h 127.0.0.1指定主机本地默认 socket 连接不用加。3.2 命令行导入三个文件按顺序执行命令行是导入大文件最稳的方式没有图形界面假死的问题。打开终端按“诗人表 → 诗词主表 → 内容表”的顺序导入mysql -uroot -p --default-character-setutf8mb4 poetry /home/youruser/poetry/xpz_sc_poet.sql mysql -uroot -p --default-character-setutf8mb4 poetry /home/youruser/poetry/xpz_sc_poetry.sql mysql -uroot -p --default-character-setutf8mb4 poetry /home/youruser/poetry/xpz_sc_poetry_content.sql逻辑说明每条命令都显式指定了字符集防止文件本身是 UTF-8 而客户端用默认 latin1 解析导致的中文乱码三个文件按依赖顺序导入因为诗词表和内容表都依赖诗人表的主键约束。参数说明--default-character-setutf8mb4是必须项漏掉后导入成功但中文全是问号路径换成你解压后的实际路径。Windows 下把改成 PowerShell 的Get-Content管道方式或者直接用mysql客户端的source命令use poetry; source C:/path/to/xpz_sc_poet.sql;这是我在 Windows 上常用的替代方案注意source后面要用正斜杠或双反斜杠否则路径解析失败。3.3 Navicat 图形导入适合先看表结构如果你只是想快速看表结构或只导一张表Navicat 更直观。右键目标库 → 运行 SQL 文件 → 选择.sql文件 → 执行。这里有一个关键设置在“高级”选项卡里把“运行多个查询”勾上否则遇到文件里的多条 INSERT 语句可能只执行第一条。图形工具导大文件容易卡在“正在读取”状态100 MB 的文件在 Navicat 里可能转圈几分钟。我的习惯是先用 Navicat 导小的xpz_sc_poet.sql看表结构再用命令行导两个大文件两边优势都有。3.4 导入后的首次校验数据对不对跑三条 SQL 就知道导入完成不是终点先跑三条统计 SQL 确认有没有丢数据SELECT COUNT(*) FROM xpz_sc_poet; SELECT COUNT(*) FROM xpz_sc_poetry; SELECT COUNT(*) FROM xpz_sc_poetry_content;正常结果应分别约等于 13136、305131、305131 或略少。如果某条明显少了一截大概率是导入进程被中断或某条 SQL 语法报错被忽略。这时候最有效的排查办法是查看 MySQL 错误日志SHOW VARIABLES LIKE log_error;能看到日志路径打开搜一下ERROR关键字。4. 把数据查起来三表联查与搜索的实用 SQL数据导进去只是第一步能灵活查出来才是这套库的价值所在。这一章写的 SQL 是我实际跑过的覆盖了按诗人查作品、按朝代聚合、按标题模糊搜索、查诗词详情四个高频场景每一段都可以直接复制使用。4.1 按诗人查作品李白名下有多少首诗SELECT p.name AS 诗人, p.dynasty AS 朝代, pt.title AS 诗名, pc.content AS 正文 FROM xpz_sc_poet p JOIN xpz_sc_poetry pt ON pt.poet_id p.id LEFT JOIN xpz_sc_poetry_content pc ON pc.poetry_id pt.id WHERE p.name 李白 LIMIT 20;逻辑说明JOIN把诗人表和诗词主表按外键串起来LEFT JOIN保证即使某首诗没有内容记录标题仍然能显示正文为 NULL。参数说明LIMIT 20限制返回条数李白存诗近千首全查出来反而不好看把20改成5每次只看五首更清爽。如果WHERE p.name 李白查不到结果先确认诗人表里有没有这个人可能是全角空格等脏数据。4.2 按朝代聚合唐朝诗人作品量排名SELECT p.dynasty AS 朝代, COUNT(DISTINCT p.id) AS 诗人数量, COUNT(pt.id) AS 诗词数量 FROM xpz_sc_poet p LEFT JOIN xpz_sc_poetry pt ON pt.poet_id p.id GROUP BY p.dynasty ORDER BY 诗词数量 DESC;逻辑说明COUNT(DISTINCT p.id)统计每个朝代有多少位诗人COUNT(pt.id)统计这个朝代有多少首诗。用LEFT JOIN是为了把没有作品的诗人也计入诗人数量如果用JOIN那些零作品的诗人会直接被过滤掉诗人数量和诗词数量都会失真。参数说明ORDER BY 诗词数量 DESC按作品数降序排列如果你想看诗人多的朝代改成ORDER BY 诗人数量 DESC。4.3 按标题模糊搜索找包含“秋”字的诗词SELECT pt.title AS 标题, p.name AS 作者, pt.dynasty AS 朝代 FROM xpz_sc_poetry pt LEFT JOIN xpz_sc_poet p ON p.id pt.poet_id WHERE pt.title LIKE %秋% LIMIT 50;逻辑说明LIKE %秋%做的是全表扫描的模糊匹配标题里任意位置出现“秋”都会命中。这是理解成本最低的搜索方式但在 30 万行数据上性能一般后面进阶篇我会讲怎么用索引优化。参数说明%是通配符放在前后表示匹配任意字符把%秋%改成%秋词%可以收紧范围。有人在这里会踩一个思维误区想着用REGEXP正则匹配更精确。但实际上 MySQL 8.0 的REGEXP不走索引和LIKE一样全表扫且写法更复杂字段里有多个连续空格或标点时会很难写常规搜索用LIKE足够。4.4 查一首诗的完整详情标题 诗人 全文 赏析SELECT pt.title AS 诗名, p.name AS 作者, p.dynasty AS 朝代, pc.content AS 正文, pc.annotation AS 注解, pc.appreciation AS 赏析 FROM xpz_sc_poetry pt JOIN xpz_sc_poet p ON p.id pt.poet_id JOIN xpz_sc_poetry_content pc ON pc.poetry_id pt.id WHERE pt.title 静夜思;这条查询没有任何模糊匹配全是等值 JOIN响应速度在毫秒级。逻辑说明三张表通过主外键链成一条完整数据链路WHERE pt.title 静夜思精确匹配不会返回多余结果。参数说明如果同一个标题有多首诗不同诗人写过同名诗这里会返回多行可以加AND p.name 李白收紧。4.5 统计验证数据完整性自查模板把这套库交给别人之前我建议你跑一遍这个“体检”SQL确认外键关系没有断裂SELECT (SELECT COUNT(*) FROM xpz_sc_poetry pt LEFT JOIN xpz_sc_poet p ON p.id pt.poet_id WHERE p.id IS NULL) AS 孤儿诗词数, (SELECT COUNT(*) FROM xpz_sc_poetry_content pc LEFT JOIN xpz_sc_poetry pt ON pt.id pc.poetry_id WHERE pt.id IS NULL) AS 孤儿内容数;结果里如果出现非零值说明导入时外键约束被跳过或源数据本身有问题。两数为零是最干净的状态说明三张表的关系完全闭合后续不管做统计还是做接口都不会突然冒出空数据。5. 避坑记录导入、乱码、统计老对不上的五个常见问题这个项目我在本机 MySQL 8.0 和 5.7 上各导了一遍踩过的坑整理成五条每一条都是“现象 → 原因 → 解决”的完整链路如果你卡在某个环节直接对号入座。5.1 导入时报 ERROR 2006/1153max_allowed_packet 太小现象命令行导入大 SQL 文件时报ERROR 2006 (HY000): MySQL server has gone away或ERROR 1153 (08S01): Got a packet bigger than max_allowed_packet bytes。原因MySQL 默认max_allowed_packet只有 4MB 或 64MB而xpz_sc_poetry_content.sql单条 INSERT 可能包含几十上百行 TEXT 数据一个数据包就超过限制服务端直接断开连接。解决临时调大再导入mysql -uroot -p -e SET GLOBAL max_allowed_packet 128M;注意重启 MySQL 后这个全局变量会恢复默认值所以只用于导入阶段就够了。如果用了主从复制还要同步调整从库的该参数否则同步会中断。5.2 导入成功但所有中文都是问号或乱码现象SELECT出来的诗人姓名、诗词标题全是???或者出现æ±æ这种乱码。原因SQL 文件本身是 UTF-8 编码但你在导入时没有指定--default-character-setutf8mb4MySQL 客户端用了系统默认的 latin1 解析 SQL 语句把多字节中文拆成了乱码。解决删除库重建导入命令加参数mysql -uroot -p --default-character-setutf8mb4 poetry /path/xpz_sc_poet.sql之所以强调“删除库重建”是因为乱码写入后再改连接字符集不会修复已损坏的数据。5.3 导入时报字段长度不合法或无法存中文现象报错Data too long for column或者中文被自动截断。原因建表语句里有些字段是 VARCHAR(10) 之类的短长度而数据源里有超出长度的诗词标题或诗人字号。解决先用source命令把建表部分单独执行出来然后查看表结构把可疑字段改用 TEXT 或加大 VARCHAR 长度ALTER TABLE xpz_sc_poetry MODIFY COLUMN title VARCHAR(255);如果文件里是CREATE TABLE IF NOT EXISTS直接改原文件里的字段定义再导入也可以但注意别把主键和索引改没。5.4 外键约束导致后导的表数据不完整现象先导xpz_sc_poetry.sql再导xpz_sc_poet.sql最后SELECT COUNT(*)发现诗词行数和源数据对不上。原因诗词表的poet_id外键在导入时找不到对应的诗人 ID整行被拒绝写入。MySQL 默认FOREIGN_KEY_CHECKS是开启的先导子表必然失败。解决按依赖顺序导入或者临时关闭外键检查SET FOREIGN_KEY_CHECKS 0; -- 执行导入 SET FOREIGN_KEY_CHECKS 1;注意关闭外键检查后导入成功但如果数据本身残缺后面查询会出现孤儿记录所以这种方法只推荐在数据完整的前提下使用。5.5 MySQL 8.0 用 Navicat 连接报 caching_sha2_password 错误现象Navicat 连接 MySQL 8.0 时提示Authentication plugin caching_sha2_password cannot be loaded。原因MySQL 8.0 默认认证插件是caching_sha2_password老版本 Navicat 不自持该插件。解决创建一个用mysql_native_password插件的专用账号CREATE USER poetry_userlocalhost IDENTIFIED WITH mysql_native_password BY 123456; GRANT ALL PRIVILEGES ON poetry.* TO poetry_userlocalhost;这段会创建一个专门用于这个库的账号不影响其他用户。逻辑说明mysql_native_password是老兼容协议新装 Navicat 16 以上一般没有这个问题只有旧版本需要这样处理。6. 进阶技巧用索引和定期校验把数据查询速度稳定在百毫秒内数据导完、查询能跑通这只是起点。在 30 万行和 1 万多行的大表上索引的合理性能差异是“秒回”和“半天转圈”的区别。这一章说两个我常用的优化动作加组合索引、建数据校验模板。6.1 为高频查询建组合索引WHERE pt.poet_id ?是按诗人查作品的高频路径给xpz_sc_poetry表的poet_id加索引是最直接的操作ALTER TABLE xpz_sc_poetry ADD INDEX idx_poet_id (poet_id); ALTER TABLE xpz_sc_poetry ADD INDEX idx_dynasty (dynasty); ALTER TABLE xpz_sc_poetry_content ADD INDEX idx_poetry_id (poetry_id);加上这几条索引后JOIN 不再做全表扫描pt.poet_id p.id变成了索引查找查询速度在百万行以内基本都能压在百毫秒级。参数说明idx_poet_id是索引名可以自定义如果你经常按朝代和体裁组合过滤可以建idx_dynasty_type (dynasty, type)联合索引顺序要按选择性从高到低排。我在本地导完之后还验证了一次最复杂的“按诗人查详情”那条三层 JOIN建索引之前要 1.2 秒建完后稳定在 80 毫秒左右。索引不是越多越好每多一个索引INSERT 和 UPDATE 就要多维护一颗 B 树这套库如果只做查询三个索引足够。6.2 统计数据漂移检查一套模板自查死链数据用久了你可能会发现某个诗人的作品数突然少了或者某首诗正文空了。这通常不是数据库出问题而是你做了部分更新或导入操作时引入了脏数据。我现在每次做完任何写操作都会强制跑一遍这个“体检”-- 检查没有关联诗人的诗词记录 SELECT COUNT(*) AS 孤儿诗词 FROM xpz_sc_poetry pt LEFT JOIN xpz_sc_poet p ON p.id pt.poet_id WHERE p.id IS NULL; -- 检查没有关联诗词的内容记录 SELECT COUNT(*) AS 孤儿内容 FROM xpz_sc_poetry_content pc LEFT JOIN xpz_sc_poetry pt ON pt.id pc.poetry_id WHERE pt.id IS NULL;两条查询的结果都为 0才说明数据链路完整。这个模板我写成了.sql脚本文件放在项目目录里每次改完数据执行一遍比肉眼抽查靠谱得多。从那以后我每次导完这套诗词库都会强制走一遍“建库 → 按顺序导表 → 跑 count 核对 → 建索引 → 跑孤儿检查”这个流程一套下来十分钟出头但从此再没被半路的数据错误坑过。希望帮到你。本文还有配套的精品资源点击获取