古诗词数据库MySQL导入实战：从txt到结构化表的完整指南

简介一份基于MySQL的中国古诗词数据库资源面向诗词研究者、语文教师、传统文化爱好者和软件开发人员整合了从先秦到近现代约三十万首古今诗词旨在解决诗词资料分散、检索不便、难以批量利用等实际问题。整个资源为一个zip压缩包大小43.55MB内部仅含一个SQL脚本文件在本地MySQL环境中执行脚本即可自动创建数据表并填充完整诗词数据无需手动逐条录入。数据表设计包含诗词ID、标题、作者、朝代、诗词内容、注释、韵脚、流派、作者简介等字段既能按作者查全作品、按朝代归纳风格、按流派梳理脉络也能结合韵脚分析格律特点适合教学备课、学术研究、诗词类网站或App后端搭建及二次开发。目前已有3403人学习下载可直接导入生产或学习环境省去大量数据清洗与整理时间。1. 古诗词数据库不是 txt是建模问题做产品的人都会有这种经历想在应用里加一个诗词卡片、每日一句、背诗打卡第一反应是上网找一份古诗词语料下下来发现是几万行 txt标题、作者、正文混在一起想按朝代筛选写半天正则换一套数据格式又要重写解析器。这时候再去搜“古诗词数据库”看到的是某个 SQL 文件、号称最全的古今诗词集导入 MySQL 就能用。这个标题指的就是一份结构化的中国古今诗词 MySQL 数据表——不是让你拿去训练模型的语料而是开箱即用的业务数据。它的实际价值有三个第一诗词、作者、朝代被拆成独立字段查询和统计不再靠字符串匹配第二数据量通常在三五万首以上覆盖唐诗宋词、先秦到近现代的常见篇目第三用 MySQL 存意味着你可以在现有系统里直接 join、索引、分页。适合谁做诗词类应用、教育类小程序、内容型网站的开发者和产品经理。注意这类数据库没有官方唯一标准网上流传的版本鱼龙混杂本文只讲“拿到一份声称最全的诗词 SQL 后如何判断质量、完成导入、避开常见的坑并把它真正用起来”。2. 先理解诗词数据表的建模逻辑为什么是 MySQL 而不是文本文件2.1 诗词数据的本质是一张主表和一张明细表网上流传的“最全诗词集”大体分两种存储形态一种是单表把整首诗塞进一个 text 字段另一种是把一首诗拆成 poem诗词主表和 poem_sentence句子表。后者才是值得用的结构。你要知道用户搜的是“mysql 数据表”重点在“表”这个字——表的意义在于每条记录有明确的属性而不是把正文当一个 blob 存起来。我一般会用两张表建模表名作用核心字段poet作者表id, name, dynasty, birth_year, death_year, intropoem诗词主表id, poet_id, title, dynasty, category, content, created_atpoem_sentence诗句明细表id, poem_id, sentence, order_no如果你拿到的库只有一张表字段叫 title、author、content那也能用但后续做词频统计、按句检索、单句卡片展示会很别扭。推荐先把它拆开。2.2 为什么数据库选型要落在 MySQL 上不少开发者问数据量又不算大SQLite 也行为什么偏要用 MySQL核心原因有两个。第一业务系统里诗词只是其中一个模块你的用户表、收藏表、笔记表大概率已经活在 MySQL 里诗词数据直接导入同一套库外键 join 和事务都顺理成章第二MySQL 生态里 utf8mb4 字符集对生僻字支持完整诗词里的“閒”“云”这类异体字不会因为编码变成问号。再往下说数据表引擎要用 InnoDB。MySQL 5.7 以上默认引擎就是 InnoDB它的行锁和事务特性对写入和并发查询都友好。诗词表不是只读的后续用户标注、收藏、纠错都要写MyISAM 在全表锁下会翻车。字符集则在建库时指定 utf8mb4别用 utf8mb3原因后面避坑章细讲。2.3 从 txt 转换到结构化表的三个核心步骤如果你手里只有一份 txt 诗词集常见做法是分三步走清洗与切分、作者拆分、正文按句切分。下面是一个简单但能跑通的 python 参考脚本把文本切成两张表可导入的 CSV再用 LOAD DATA 导入 MySQL。import re import csv # 假设每首诗以「【作者】《标题》」开头正文在下一行 # 这是很多诗词语料的通用格式不匹配时按实际格式调整 raw_text open(poetry.txt, encodingutf-8).read() blocks re.split(r【(.?)】《(.?)》, raw_text) poems [] sentences [] for i in range(1, len(blocks), 3): author blocks[i].strip() title blocks[i 1].strip() content blocks[i 2].strip() # 按行切分诗句空行跳过 lines [ln.strip() for ln in content.splitlines() if ln.strip()] poem_id len(poems) 1 poems.append([poem_id, author, title, \n.join(lines)]) for order, line in enumerate(lines): sentences.append([len(sentences) 1, poem_id, line, order]) with open(poems.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([id, author, title, content]) writer.writerows(poems) with open(sentences.csv, w, newline, encodingutf-8) as f: writer csv.writer(f) writer.writerow([id, poem_id, sentence, order_no]) writer.writerows(sentences)这段脚本的比赛重点是正则切分。re.split(r【(.?)】《(.?)》, raw_text)利用了捕获括号split 后奇数位置是作者和标题偶数位置是正文。切分完后正文按行拆成句子并记录order_no保证原诗顺序不错乱。生成的两个 CSV 就是导入 MySQL 的原料。参数说明encodingutf-8在 Windows 上可能踩坑如果源文件是 GBK要先转码或者把 open 的 encoding 改成gbk。正则里的.?是非贪婪匹配能应对多首连排的场景但如果文件名里带了括号需要改成字符类排除例如「【(.?)】《([^》]?)》。3. 把诗词 SQL 导入 MySQL从命令行到可视化工具的完整操作3.1 导入前先看 SQL 文件内容别急着执行拿到手的诗词 sql 文件第一个动作不是双击导入而是先看头部。用文本编辑器打开确认三件事建库语句是CREATE DATABASE还是直接USE表名是什么字段定义里有没有ENGINEInnoDB DEFAULT CHARSETutf8mb4。常见翻车点是文件名带poetry.sql但里面表名是poem_poetry你后面写的查询全部要改表名。我一般会在命令行下先做这一步head -100 poetry.sql如果看到SET NAMES utf8mb4说明作者已经考虑过字符集导入时不容易乱码。看到INSERT INTO语句里中文是正常显示而不是\uXXXX转义序列也说明文件相对干净。如果 SQL 里出现大量LOCK TABLES、UNLOCK TABLES导入时会锁表但数据量不大时可忽略。3.2 命令行导入最简单也最稳的一条路径假设你本地已经装好了 MySQL并且数据文件在/data/poetry.sql导入命令如下mysql -uroot -p --default-character-setutf8mb4 -e CREATE DATABASE IF NOT EXISTS poetry_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; mysql -uroot -p --default-character-setutf8mb4 poetry_db /data/poetry.sql第一条命令负责建库指定 utf8mb4 字符集避免继承 MySQL 全局配置里的老编码。第二条命令把 SQL 文件执行到新库中。如果不加--default-character-setutf8mb4导入时 MySQL 会按客户端默认字符集解析文件里的中文很可能出现乱码。执行完后验证一下数据量mysql -uroot -p --default-character-setutf8mb4 -e SELECT COUNT(*) FROM poetry_db.poem;需要注意的是如果 SQL 文件里已经有CREATE DATABASE语句第一句会报错但通常不影响整体导入报错后继续执行后面的语句。想避免这个噪音可以把第一句改成mysql -uroot -p /data/poetry.sql有权限就让它自己建库。3.3 Docker 环境下的 MySQL 导入避开通配符的坑很多读者用的是 Docker MySQL常见问题是宿主机和容器之间文件传递。导入诗词库的正确方式不是docker exec -i mysql mysql -u root -p poetry.sql那样管道方式虽然能用但遇到大文件容易因编码问题中断。我一般先把 SQL 文件复制进容器再在容器内执行docker cp /data/poetry.sql mysql_container:/tmp/poetry.sql docker exec -it mysql_container mysql -uroot -p --default-character-setutf8mb4 -e CREATE DATABASE IF NOT EXISTS poetry_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; docker exec -it mysql_container mysql -uroot -p --default-character-setutf8mb4 poetry_db /tmp/poetry.sql参数说明mysql_container是容器名先通过docker ps确认。用docker cp把文件送进容器后再重定向可以避免 Windows 下路径解析错误。如果你在 Windows 上用 PowerShell 执行重定向记得使用cmd /c或统一改用docker exec -i管道模式不然会被解析成别的含义。导入完成后建议顺手清理容器内 SQL 文件docker exec -it mysql_container rm /tmp/poetry.sql3.4 图形化工具导入的边界Workbench 与 Navicat如果你习惯图形化工具流程就是连接 MySQL新建数据库字符集选 utf8mb4然后导入 SQL 文件。但这类工具对大 SQL 文件超过 100MB会有假死风险中途断了还容易产生半截数据。我的建议是图形化只用来查看表结构和写查询导入一次性任务一律走命令行。真遇到超大文件就用source命令分步执行mysql -uroot -p --default-character-setutf8mb4 poetry_db source /data/poetry.sqlsource的优势是逐条执行失败时报错信息能精确定位到哪一行方便修复。4. 数据质量与查询性能这张表值不值得信先看这四个地方4.1 去重同一首诗被收两遍是常态一份自称“最全”的诗词集几乎必然有重复。常见现象是同一首《静夜思》因朝代写法不同出现两行或者是“李白”和“李太白”被当成两个作者。这就是数据质量问题。先在作者维度查重SELECT name, COUNT(*) FROM poet GROUP BY name HAVING COUNT(*) 1;再在诗词维度查重通常用标题和正文前 20 个字组合判重SELECT title, LEFT(content, 20) AS head, COUNT(*) FROM poem GROUP BY title, LEFT(content, 20) HAVING COUNT(*) 1;查到重复之后不是简单删除而要确认内容是否完全一致。有的重复是繁体与简体并存有的重复是标点符号不同。保留哪一条取决于你的业务标准如果是展示保留内容更完整的那条如果是做数据统计建议两条都标记为同一poem_group_id。4.2 朝代字段的脏数据不只是“唐、宋、元、明、清”我在实际处理中遇到过朝代字段里混入“宋辽金”“南北宋”“五代十国”这种组合词还有直接写“不详”的。如果你要做朝代的聚合查询这些值会导致分组结果出现十几个零散桶。常见修正方法是在 SQL 里做归一化映射UPDATE poem SET dynasty CASE WHEN dynasty LIKE %唐% THEN 唐 WHEN dynasty LIKE %宋% THEN 宋 WHEN dynasty LIKE %元% THEN 元 WHEN dynasty LIKE %明% THEN 明 WHEN dynasty LIKE %清% THEN 清 ELSE dynasty END;这个 CASE 语句的执行顺序是从上往下第一行命中的就不会再走到后面所以“唐”不会误伤“南唐”。参数说明如果库里出现“南唐”和“唐”并存且你希望区分可以先把“南唐”改成“五代十国”再映射或者保留“南唐”为独立朝代。4.3 生僻字和异体字utf8mb4 是底线诗词里常见“閒”“裏”“峯”这类异体字以及部分生僻字。MySQL 的 utf8mb3 只支持 BMP 平面字符遇到一些扩展区的字会直接报错或变成 ?。这也是为什么建库时指定 utf8mb4导入时指定--default-character-setutf8mb4都是同等重要。验证一下你的库有没有出现乱码SELECT COUNT(*) FROM poem WHERE content LIKE %?%;这个查询如果返回大于 0说明要么导入时的字符集设置不对要么数据源本身就把字弄丢了。字符集问题在不换库的前提下没有后悔药只能重导。4.4 索引设计全文检索别走 LIKE诗词表最常用的查询是“按标题搜”和“按内容搜”。直接WHERE title LIKE %静夜%在几万行数据上性能还能忍一旦表扩大到百万级句子表全表扫描就慢到不可接受。所以索引至少要建两个ALTER TABLE poem ADD INDEX idx_title (title); ALTER TABLE poem ADD INDEX idx_poet_id (poet_id); ALTER TABLE poem_sentence ADD INDEX idx_poem_id (poem_id);标题字段用普通 B-Tree 索引就能覆盖路径前缀匹配若要支持中间模糊匹配则需要配合全文索引。MySQL 5.7 以后支持中文全文索引ngram 插件是绕不开的建议在 6.2 小节展开说。5. 把诗词库用起来增删改查与五个高频查询场景5.1 最常用的增删改查语句数据导入后日常逃不开增删改查。下面这四条是基础-- 查询某作者全部诗词 SELECT * FROM poem WHERE poet_id 12 ORDER BY id ASC; -- 新增一首纠错补充的诗词 INSERT INTO poem (poet_id, title, dynasty, content) VALUES (12, 某新辑诗, 唐, 第一句\n第二句); -- 修改标题错字 UPDATE poem SET title 静夜思 WHERE id 10086; -- 删除错误录入的重复诗 DELETE FROM poem WHERE id 10087;参数说明新增时content里的换行用\n保存查询后在应用层展示时再转br或按行切分。删除操作务必先确认id最好先 SELECT 再 DELETE避免删错。5.2 按朝代、作者、词牌名查询的 SQL 写法-- 按朝代统计诗词数量 SELECT dynasty, COUNT(*) AS cnt FROM poem GROUP BY dynasty ORDER BY cnt DESC; -- 查询某作者在某个朝代的诗词列表按字数升序 SELECT title, CHAR_LENGTH(content) AS len FROM poem WHERE poet_id 12 AND dynasty 唐 ORDER BY len ASC; -- 查询包含某关键词的诗句 SELECT s.sentence, p.title, po.name AS author FROM poem_sentence s JOIN poem p ON s.poem_id p.id JOIN poet po ON p.poet_id po.id WHERE s.sentence LIKE %明月% LIMIT 20;中间那个CHAR_LENGTH值得注意它按字符数统计而不是LENGTH的字节数。中文在 utf8mb4 下一个字占 4 字节用LENGTH统计字数会翻倍。5.3 诗词推荐模块最简实现是排序加条件过滤很多产品要做“每日推荐”。不用上机器学习一个 SQL 就能解决按作者随机、按朝代轮播、按收藏量排序都可以。-- 随机取一首唐诗 SELECT * FROM poem WHERE dynasty 唐 ORDER BY RAND() LIMIT 1;ORDER BY RAND()在小表几万行上可接受但如果你把句子表也拿来随机建议改成SELECT * FROM poem WHERE id CEIL(RAND() * (SELECT MAX(id) FROM poem)) LIMIT 1避免全表排序。5.4 关联查询的黄金搭档作者表与诗词表 join作者表和诗词表分开存的最大好处就是改作者信息只需改一处。比如作者知名度字段、头像字段都挂在 poet 表上。查询时 join 一次SELECT po.name, po.dynasty, p.title FROM poem p JOIN poet po ON p.poet_id po.id WHERE po.name 李白;这里要求poet_id外键一致。有的原始 SQL 里诗人名字直接冗余在 poem 表没有 poet 表那就要先做数据迁移用UPDATE poem p JOIN poet po ON p.author po.name SET p.poet_id po.id补齐。6. 验证诗词库完整性的三个自查与进阶从全库到全文检索6.1 数据导入后先做三个自查命令拿到诗词库并导入成功后建议按下面的顺序自查确认这份数据到底能不能信。第一个命令查总数和原始文件声明的篇目数对比误差超过 20% 就说明拆分或导入有缺失第二个命令查空值第三个命令查乱码。SELECT COUNT(*) AS total_poems FROM poem; SELECT COUNT(*) AS no_title_count FROM poem WHERE title IS NULL OR title ; SELECT COUNT(*) AS bad_encoding FROM poem WHERE content LIKE %?% OR content LIKE %%;这三个查询执行时间都在秒级。如果total_poems与声明值差得不多且第三个查询返回 0基本可以放心用。反之说明文件里的声明数字本身有水分或者导入过程有编码问题。6.2 全文检索的进阶配置ngram 解析器当诗词库到达几十万行、且你需要对诗句做“关键词包含”查询时LIKE %关键词%会走全表扫描。MySQL 5.7 以上的全文索引配合 ngram 解析器是标准解法。建索引前先确认插件可用SHOW VARIABLES LIKE ngram_token_size;默认值通常是 2表示按两个连续汉字切词。查询时用AGAINST语法注意模式选IN NATURAL LANGUAGE MODE还是IN BOOLEAN MODEALTER TABLE poem_sentence ADD FULLTEXT INDEX ft_sentence (sentence) WITH PARSER ngram; SELECT sentence FROM poem_sentence WHERE MATCH(sentence) AGAINST(明月 IN NATURAL LANGUAGE MODE) LIMIT 20;参数说明ngram_token_size 为 2 时“床前明月光”会被切成“床前、前明、明月、月光”查询“明月”能命中。如果要查询单个字必须把 token 大小改成 1但这样索引体积会明显变大。我的实践是保持 2业务层不提供单字检索用户体验没有损失。6.3 我对这份诗词库的最终判断最后说说结论。这份“古诗词数据库”值不值得做、能不能作为业务底座取决于你看中的字段结构是否已经拆分好。只有一张大宽表没有诗人表、没有句子表的版本不建议直接用花半天时间按 2.3 的脚本拆一次后续收益很大建表合理、有作者表、有朝代的版本导入后重点做去重和朝代归一化这个清洗工作量是必须付的成本。我处理这类数据时养成的习惯是把清洗 SQL 全部存成一个clean.sql随时可以重放。万一哪天发现某批数据有问题不用手动一条条改直接重跑脚本。这就是数据工作的后悔药——把过程固化下来就不怕翻车。希望这篇笔记帮你把古诗词数据真正变成产品里的一个模块而不是躺在本地的几个 txt。本文还有配套的精品资源点击获取