简介这份诗词诗人数据库以MySQL为载体面向文学研究者、古诗词爱好者及需要诗词数据做检索或二次开发的开发者解决诗词与诗人信息分散、难以结构化查询的问题。资源包共3个文件均为sql脚本压缩包约47.46MB分别用于构建诗词基本信息表、诗词正文与注解赏析内容表以及诗人姓名、字号、生卒年、籍贯、代表作等资料表便于按诗人、朝代、体裁等维度分类检索。数据库收录13136位诗人、305131首诗词覆盖范围广可直接导入MySQL使用。已有2299人学习下载适合用于教学演示、学术研究、诗词类小程序或网站的数据支撑也能帮助读者快速搭建可查询、可扩展的诗词数据环境。1. 三张表撑起十三万诗人与三十万首诗词这套 MySQL 库到底能干什么拿到一个只有三个.sql文件的压缩包第一反应往往是「这么小能装多少东西」。poetry.zip解压后就是xpz_sc_poetry.sql、xpz_sc_poetry_content.sql、xpz_sc_poet.sql三个脚本没有文档、没有 ER 图、没有安装说明。但把这三个文件导入 MySQL 之后你会得到 13136 位诗人、305131 首诗词的完整结构化数据。这个量级放在任何诗词类应用里都算得上扎实——它不是 demo 数据是能直接撑起检索、分类、关联查询的底库。这套库解决的核心问题是「把非结构化的诗词文本变成可查询的关系数据」。诗人表管人物属性诗词表管作品元信息内容表管正文与注解三张表通过 ID 关联。适合谁用做诗词类小程序、做古文教学工具、做文学数据分析、或者单纯想练手 MySQL 关联查询的人。如果你正在找一份能直接导入、字段清晰、数据量够大的中文文本数据集这三个文件值得花十分钟跑一遍。下面从表结构推断、导入实操、查询验证到避坑一步步拆开讲。2. 从三个 SQL 文件反推表结构字段怎么设、关系怎么连2.1 三张表的分工与关联逻辑文件名本身就是线索。xpz_sc_poet.sql对应诗人表xpz_sc_poetry.sql对应诗词主表xpz_sc_poetry_content.sql对应诗词内容表。这种拆法在文本类数据库里很常见把「谁写的」「写了什么作品」「作品正文是什么」分成三层避免单表字段膨胀。诗人表通常包含诗人 ID、姓名、朝代、字号、生卒年、籍贯、简介等字段。诗词主表一般有诗词 ID、标题、作者 ID、朝代、体裁诗/词/曲、韵律信息等。内容表则挂诗词 ID、正文、注解、赏析。三张表通过诗人 ID 和诗词 ID 做外键关联查「杜甫在长安时期写的五言律诗」这种需求就是三表 JOIN。常见做法是诗词主表里冗余一个作者姓名字段这样列表页不用每次都 JOIN 诗人表。但冗余字段在导入时要注意和诗人表保持一致否则会出现「作者名对不上」的玄学问题。2.2 导入前先确认字符集与排序规则诗词数据里全是生僻字和多音字字符集选错直接乱码。MySQL 5.7 默认latin18.0 默认utf8mb4但utf8mb4的排序规则有utf8mb4_general_ci和utf8mb4_unicode_ci之分。诗词检索建议用utf8mb4_unicode_ci它对多语言字符的排序更准确尤其是按拼音或笔画排序时差异明显。建库时显式指定CREATE DATABASE poetry_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;导入前还要确认max_allowed_packet够大。三十万首诗词的内容表可能单条 INSERT 语句很长默认 4MB 在某些版本上会截断。临时调大SET GLOBAL max_allowed_packet 268435456;这个值是会话级的重启后失效导入完可以改回去。如果用的是云数据库控制台里一般有参数组可以调。2.3 导入顺序不能乱先诗人、再诗词、后内容三个文件有外键依赖导入顺序错了会报Cannot add or update a child row。正确顺序是# 1. 先导入诗人表建立主键 mysql -u root -p poetry_db xpz_sc_poet.sql # 2. 再导入诗词主表此时作者 ID 已存在 mysql -u root -p poetry_db xpz_sc_poetry.sql # 3. 最后导入内容表挂载正文和注解 mysql -u root -p poetry_db xpz_sc_poetry_content.sql如果脚本里没有USE poetry_db;语句命令行里必须指定库名。导入过程中如果报Duplicate entry说明之前跑过一半要么清库重来要么在脚本里找INSERT IGNORE或REPLACE INTO的写法。我一般会先DROP DATABASE再重建避免残留数据干扰。导入完成后用SHOW TABLES;确认三张表都在再用SELECT COUNT(*)核对行数是否和简介里的 13136、305131 对得上。对不上就说明有截断或跳过得回去看导入日志。3. 导入后的验证与查询把数据跑通才算数3.1 用 COUNT 和 JOIN 验证数据完整性导入完第一件事不是写业务查询是验证数据有没有缺胳膊少腿。三条 COUNT 语句跑一遍-- 诗人数量预期 13136 SELECT COUNT(*) AS poet_count FROM xpz_sc_poet; -- 诗词数量预期 305131 SELECT COUNT(*) AS poetry_count FROM xpz_sc_poetry; -- 内容表数量应该和诗词表接近或相等 SELECT COUNT(*) AS content_count FROM xpz_sc_poetry_content;如果诗词表数量对但内容表少很多可能是内容表有外键约束导致部分插入失败。用 LEFT JOIN 找孤儿记录-- 找出有诗词记录但没有内容记录的诗词 ID SELECT p.poetry_id, p.title FROM xpz_sc_poetry p LEFT JOIN xpz_sc_poetry_content c ON p.poetry_id c.poetry_id WHERE c.poetry_id IS NULL LIMIT 20;这个查询能帮你判断是数据本身缺失还是导入时被截断。如果孤儿记录集中在某几个诗人身上可能是那个诗人的作品内容特别长触发了max_allowed_packet限制。3.2 按诗人查作品最常用的关联查询诗词库最高频的查询就是「给我某个诗人的所有作品」。假设诗人表的主键是poet_id诗词表的外键也是poet_id-- 查李白的所有诗词标题和体裁 SELECT p.title, p.genre, p.dynasty FROM xpz_sc_poetry p JOIN xpz_sc_poet po ON p.poet_id po.poet_id WHERE po.name 李白 ORDER BY p.poetry_id LIMIT 50;这里用JOIN而不是LEFT JOIN因为我们要的是「有诗人记录的作品」孤儿作品不显示。ORDER BY p.poetry_id保证结果稳定分页时不会因为排序抖动导致重复或遗漏。如果诗人表里姓名有重名比如不同朝代的同名诗人需要加朝代条件SELECT p.title, p.genre FROM xpz_sc_poetry p JOIN xpz_sc_poet po ON p.poet_id po.poet_id WHERE po.name 王维 AND po.dynasty 唐;3.3 全文检索的替代方案LIKE 与全文索引的取舍MySQL 5.7 之前没有内置中文分词全文索引对中文支持很差。这套库如果没建全文索引搜诗句只能用LIKE %关键词%-- 在内容表里搜包含「明月」的诗句 SELECT c.poetry_id, c.content FROM xpz_sc_poetry_content c WHERE c.content LIKE %明月% LIMIT 100;LIKE %...%走不了索引三十万行全表扫描在本地 SSD 上大概几百毫秒到一秒勉强能用。但如果要做线上服务这个延迟不可接受。常见做法是导入 Elasticsearch 或 Meilisearch 做全文检索MySQL 只存结构化字段。如果坚持用 MySQL8.0 可以用ngram分词器建全文索引-- MySQL 8.0 建 ngram 全文索引 ALTER TABLE xpz_sc_poetry_content ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; -- 查询时用 MATCH AGAINST SELECT poetry_id, content FROM xpz_sc_poetry_content WHERE MATCH(content) AGAINST(明月 IN BOOLEAN MODE) LIMIT 100;ngram默认 token size 是 2搜「明月」正好命中。但搜单字「月」需要把ngram_token_size改成 1改完要重建索引。这个参数是只读的得在配置文件里改完重启。3.4 分页查询的坑LIMIT OFFSET 越翻越慢诗词列表分页如果用LIMIT 20 OFFSET 10000MySQL 会先扫描前 10020 行再丢弃前 10000 行。翻到后面越来越慢。常见优化是用游标分页-- 传统分页越翻越慢 SELECT poetry_id, title FROM xpz_sc_poetry ORDER BY poetry_id LIMIT 20 OFFSET 10000; -- 游标分页记住上一页最后一个 ID SELECT poetry_id, title FROM xpz_sc_poetry WHERE poetry_id 10000 ORDER BY poetry_id LIMIT 20;游标分页要求poetry_id连续且有序这套库如果是自增主键就满足条件。如果 ID 有空洞游标分页会漏数据得改用WHERE (poetry_id, title) (last_id, last_title)这种元组比较。4. 避坑与排查导入和查询中最容易翻车的五个点4.1 导入报错Unknown command \现象用mysql file.sql导入时中途报错提示Unknown command \或ASCII \0 appeared in the statement。原因SQL 文件里包含 Windows 换行符\r\n或 BOM 头MySQL 客户端在 Linux 下解析异常。也可能是文件里有二进制字符。解决先用file命令确认编码再用dos2unix转一遍file xpz_sc_poetry.sql dos2unix xpz_sc_poetry.sql # 如果 dos2unix 没装用 sed 去掉 \r sed -i s/\r$// xpz_sc_poetry.sql转完再导入。如果还有 BOM用sed -i 1s/^\xEF\xBB\xBF//去掉。4.2 中文乱码导入后 SELECT 出来全是问号现象导入没报错但SELECT出来的诗人名和诗词内容全是???。原因客户端连接字符集和数据库字符集不一致。mysql命令行默认用latin1连接即使库是utf8mb4也会在传输时转码丢失。解决导入和查询时都显式指定字符集mysql --default-character-setutf8mb4 -u root -p poetry_db xpz_sc_poet.sql或者在 MySQL 配置文件my.cnf的[client]和[mysqld]段都加上default-character-setutf8mb4重启后永久生效。4.3 外键约束导致导入失败现象导入诗词表时报ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails。原因诗词表里引用了诗人表不存在的poet_id或者导入顺序反了。解决先确认诗人表已导入且行数正确。如果诗人表确实缺数据临时关闭外键检查再导入SET FOREIGN_KEY_CHECKS 0; -- 导入语句 SET FOREIGN_KEY_CHECKS 1;导入完再打开检查然后用LEFT JOIN找孤儿记录补数据。生产环境不建议长期关闭外键检查。4.4max_allowed_packet截断长内容现象内容表导入到一半报MySQL server has gone away或者导入完成但部分诗词正文被截断。原因单条 INSERT 语句超过max_allowed_packet限制MySQL 服务端主动断开连接。解决临时调大参数再导入SET GLOBAL max_allowed_packet 268435456;如果是云数据库改不了全局参数用mysql命令行的--max-allowed-packet选项mysql --max-allowed-packet256M -u root -p poetry_db xpz_sc_poetry_content.sql导入完记得改回默认值避免内存占用过高。4.5 查询慢没建索引的字段当条件现象按诗人姓名查作品要好几秒EXPLAIN显示typeALL全表扫描。原因诗人表的name字段没索引诗词表的poet_id没索引。解决给高频查询字段加索引-- 诗人姓名索引加速按名查人 ALTER TABLE xpz_sc_poet ADD INDEX idx_name (name); -- 诗词表作者 ID 索引加速 JOIN ALTER TABLE xpz_sc_poetry ADD INDEX idx_poet_id (poet_id); -- 内容表诗词 ID 索引加速关联 ALTER TABLE xpz_sc_poetry_content ADD INDEX idx_poetry_id (poetry_id);加完索引再用EXPLAIN确认type变成ref或eq_ref。注意索引不是越多越好写入频繁的表加太多索引会拖慢导入速度。5. 进阶用法把诗词库接进应用层的两个具体技巧5.1 用视图封装三表 JOIN简化应用层查询应用层每次写三表 JOIN 容易出错建一个视图把常用字段拼好CREATE VIEW v_poetry_full AS SELECT p.poetry_id, p.title, p.genre, p.dynasty, po.name AS poet_name, po.dynasty AS poet_dynasty, c.content, c.annotation FROM xpz_sc_poetry p JOIN xpz_sc_poet po ON p.poet_id po.poet_id LEFT JOIN xpz_sc_poetry_content c ON p.poetry_id c.poetry_id;视图不存数据查询时实时计算。好处是应用层只写SELECT * FROM v_poetry_full WHERE poet_name 杜甫不用关心底层表结构。坏处是视图不能建索引复杂查询还是得回原表。我一般只在列表页和详情页用视图统计类查询直接写 JOIN。5.2 导出 JSON 给前端用JSON_OBJECT和JSON_ARRAYAGGMySQL 5.7 开始支持 JSON 函数可以直接把查询结果拼成 JSONSELECT JSON_OBJECT( poet, po.name, dynasty, po.dynasty, poems, JSON_ARRAYAGG( JSON_OBJECT(title, p.title, genre, p.genre) ) ) AS poet_json FROM xpz_sc_poet po JOIN xpz_sc_poetry p ON po.poet_id p.poet_id WHERE po.name 苏轼 GROUP BY po.poet_id;这个查询返回一个 JSON 对象包含诗人信息和作品列表。前端拿到直接渲染不用二次组装。注意JSON_ARRAYAGG在 MySQL 5.7 里没有排序保证如果要求作品按 ID 排序得用子查询先排好再聚合。5.3 验证数据质量的三个 SQL 习惯从那以后我每次导入这类文本库都强制走一遍三个检查第一SELECT COUNT(*)核对总数第二LEFT JOIN找孤儿记录第三随机抽 10 条SELECT *肉眼确认中文没乱码、字段没串位。这三个动作花不了两分钟但能挡住 90% 的导入事故。希望帮到你。本文还有配套的精品资源点击获取