古诗词数据库MySQL实战：从表结构设计到查询优化

简介中国古诗词数据库是一款面向诗词爱好者、研究者及教育工作者的MySQL数据表资源解决从浩繁古籍中快速获取、检索与分析诗词史料的核心痛点。压缩包共1个文件为43.55MB的sql脚本导入MySQL后即可自动创建数据表并填充近30万首古今诗词作品覆盖诗、词、赋等文体字段设计包含诗词ID、标题、作者、朝代、正文、注释、韵脚、流派及作者简介等结构化信息支持按作者、朝代、流派等多维度筛选也能为基于词频、韵脚和题材的大数据分析提供可靠底表。借助这套数据教师可便捷获取备课素材研究者可开展文学计量分析普通读者可系统赏析名篇佳句无需从零搜集整理古籍文本导入即用。目前已有3403人学习或下载是数字化传承与传播中华诗词文化的实用工具。1. 古诗词数据库把两万多首诗词装进 MySQL先想清楚这三件事如果你接手过一个内容类项目大概会遇到同样的尴尬网上能搜到的诗词数据要么是爬虫抓出来的乱码文本要么是带广告的 JSON 包真正能直接丢进 MySQL 跑查询的干净数据表少之又少。很多人一开始觉得「不就是建两张表插数据吗」实际做下来才发现光是朝代、作者、诗词正文的拆分方式就能让你改三遍表结构。这个标题里的「最全的中国古今诗词集」听起来像营销话术但从工程角度看它真正在解决的问题是如何设计一套能支撑全文检索、分类筛选、作者聚合的 MySQL 表结构并让数据质量经得起查询。这篇文章适合谁准备做诗词类网站、国学教育 App 后端、或者需要批量处理中文古典文本的开发者。你可能不需要真的收录「古今所有诗词」但你需要知道数据从哪来、表怎么建、坑在哪里。我在这条路上踩过的坑包括把注释和正文混在一起导致检索结果乌烟瘴气、朝代字段存成「唐」却有人写「唐朝」、以及最经典的——标题里带着「·」和空格前端怎么都匹配不上。先说结论一套能用的古诗词 MySQL 库至少要拆成诗词表、作者表、朝代表三张核心表诗词表必须单独存标题、正文、注释、赏析索引要照顾到LIKE查询和排序。下面我从表结构设计开始一步一步讲清楚。2. 表结构设计为什么「一张大表」是新手最容易踩的坑2.1 三张表的拆分逻辑诗词、作者、朝代的关联关系常见的错误做法是把所有字段塞进一张表poem_id, title, author, dynasty, content, notes, appreciation。这张表在数据量几千条时毫无问题但一旦超过两万条你会发现两个痛点。第一作者名字重复存储比如李白写了九百多首诗「李白」这两个字在表里重复了九百多次想改作者生卒年时得UPDATE九百行。第二按朝代筛选时如果有人在「作者朝代」字段写「唐代」有人写「唐」你的GROUP BY dynasty就会裂成一堆脏数据。我一般这样拆。poems表只存诗词本体和作者外键authors表存作者信息dynasties表存朝代及起止年份。CREATE TABLE dynasties ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE, start_year SMALLINT NULL, end_year SMALLINT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE authors ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, dynasty_id INT UNSIGNED NULL, birth_year SMALLINT NULL, death_year SMALLINT NULL, description TEXT, INDEX idx_dynasty (dynasty_id), CONSTRAINT fk_author_dynasty FOREIGN KEY (dynasty_id) REFERENCES dynasties(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这里有几个关键决定。dynasties表的主键采用INT UNSIGNED因为诗词数据量轻松超过65535不要用SMALLINT给自己挖坑。authors表里存dynasty_id而不是直接存朝代字符串这是为了后续能按朝代起止年份做「初唐、盛唐、晚唐」这类区间筛选。外键约束在这个场景建议保留因为诗词和作者的绑定关系一旦出错整个库的查询可信度就崩了。2.2 诗词表字段取舍正文、注释、赏析到底要不要分开这是整个设计里最容易反复改的地方。我见过有人把注释和赏析塞进一个字段理由是「前端展示时反正是一起显示的」。但当你需要做「只看注释不看正文」的页面、或者要统计「这首诗有没有赏析」时混在一起就麻烦了。更实际的问题是很多公开数据集里注释和赏析的质量参差不齐有的赏析是后人写的有的注释是古籍原文分开存才能在不同场景下决定展示哪部分。CREATE TABLE poems ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, author_id INT UNSIGNED NOT NULL, dynasty_id INT UNSIGNED NOT NULL, content MEDIUMTEXT NOT NULL, notes TEXT, appreciation TEXT, type VARCHAR(20) DEFAULT 诗, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FULLTEXT KEY ft_content (content), INDEX idx_author (author_id), INDEX idx_dynasty (dynasty_id), CONSTRAINT fk_poem_author FOREIGN KEY (author_id) REFERENCES authors(id), CONSTRAINT fk_poem_dynasty FOREIGN KEY (dynasty_id) REFERENCES dynasties(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;content用MEDIUMTEXT是因为最长的古体诗可能上万字TEXT类型上限 64KB 一般来说够但遇到《长恨歌》这种长诗加标点注释就逼近边界了。FULLTEXT KEY ft_content是给后续全文检索准备的这里要特别注意MySQL 5.7 以上才支持中文全文索引而且默认的ngram分词器对古诗支持并不好后面我会讲到更稳妥的替代方案。我不用type字段区分诗词曲赋除非你有明确的按体裁筛选需求。这个字段真正麻烦的地方在于数据清洗——同一个数据集里「五言绝句」「七律」「词」「曲」的写法五花八门没有统一规范之前这个字段只会增加分组统计时的噪音。2.3 为什么utf8mb4是唯一选择以及unicode_ci的排序陷阱古诗词数据里的生僻字多到你无法想象很多字连输入法都打不出来。MySQL 的utf8mb3也就是常说的utf8只支持基本多语言平面遇到扩展 B 区的汉字会直接变成问号。utf8mb4才是完整的 UTF-8 编码能存下所有 Unicode 字符。这个选择没有讨论余地。COLLATEutf8mb4_unicode_ci的问题在于排序。unicode_ci不区分大小写这对中文没有影响但它对带声调的拼音字符处理方式是「合并同类项」如果后续要做作者名的拼音排序你会发现姓「曾」zēng和「曾」céng被排在了一起。另一个更隐蔽的问题是unicode_ci的比较规则会导致「柳」「刘」这类形近字在某些边界查询时表现异常。实际项目里如果遇到排序相关的玄学问题优先怀疑排序规则而不是你的ORDER BY写错了。3. 数据导入实战从清洗原始文本到生成 SQL 脚本3.1 原始文本的三种常见形态JSON、TXT、爬虫结果市面上的古诗词数据集形态大致分三种。正规一点的会给你 JSON每个对象包含title、author、content、notes等字段这种最好处理。粗糙一点的是 TXT 文件用空格或全角符号分隔字段解析时最怕遇到正文里自带空格的排版。最麻烦的是直接从网页复制下来的爬虫结果标题、作者、正文混在一起中间还夹杂着 HTML 标签和空格符。我建议第一步先把数据归一化成统一的中间格式不要一上来就写 SQL 导入脚本。我的常见做法是写一个 Python 脚本把不同的原始格式都转成 JSON Line每行一个 JSON 对象这样后续不管是导入 MySQL 还是做数据清洗都可以复用同一套逻辑。import json import re def clean_poem_text(raw_text): # 去掉 HTML 标签和全角空格 text re.sub(r[^], , raw_text) text text.replace(\u3000, ).replace(\xa0, ) # 去掉首尾空白但保留诗句内部的换行 text text.strip() # 连续空行压缩为单个换行 text re.sub(r\n\s*\n, \n, text) return text def parse_txt_line(line): parts line.split(||) # 假设 txt 用 || 分隔 if len(parts) 3: return None return { title: parts[0].strip(), author: parts[1].strip(), content: clean_poem_text(parts[2]) } with open(raw_poems.txt, r, encodingutf-8) as f: for line in f: poem parse_txt_line(line.strip()) if poem: print(json.dumps(poem, ensure_asciiFalse))解析逻辑的核心原则是先做格式归一化再按分隔符切字段。||不是标准分隔符你可以根据你的数据集换成\t或全角逗号。关键在于clean_poem_text函数里的正则它能过滤掉爬虫数据里最常见的两类污染HTML 残留和全角空格。这里有个小坑全角空格\u3000在 Python 的strip()里会被清掉但不会在replace( , )里被处理所以必须显式替换。3.2 用LOAD DATA INFILE批量导入一万条数据秒级完成清洗完数据后有两种导入路径。一种是用 Python 的pymysql逐条INSERT代码好写但两万条数据要跑几分钟。另一种是先把数据整理成 CSV用 MySQL 自带的LOAD DATA INFILE一把导入速度快到你怀疑人生。我一般先 INSERT 作者和朝代再导入诗词。因为诗词表的外键依赖作者 ID顺序错了会报外键约束错误。LOAD DATA INFILE /var/lib/mysql-files/authors.csv INTO TABLE authors CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (name, dynasty_id, birth_year, death_year, description);参数说明FIELDS TERMINATED BY ,指定字段分隔符ENCLOSED BY 处理字段值里带逗号的情况古诗内容里出现逗号太常见了没有这个参数你会在第 100 行开始翻车。IGNORE 1 ROWS跳过 CSV 的列头。需要特别注意的是MySQL 8.0 默认限制LOAD DATA INFILE只能读取secure_file_priv指定目录下的文件你需要在my.cnf里配置secure_file_priv来放宽限制或者把文件放到默认的/var/lib/mysql-files/目录。如果你用的是 Docker 里的 MySQL这个命令的路径映射会稍有不同。我习惯先执行SHOW VARIABLES LIKE secure_file_priv;查看当前限制路径然后把 CSV 文件映射到对应的容器目录里避免授权相关的权限报错。3.3 导入后必做的三样校验行数、外键、重复诗词导入完成不等于数据正确。我每次导入后都会跑三组校验花不了两分钟但能拦截 90% 的脏数据问题。第一行数校验对比源文件记录数和SELECT COUNT(*)确认没有静默丢失。第二外键孤儿校验查诗词表里是否有author_id在作者表里不存在的情况。第三重复诗词检测用title author_id做分组找出重复项。-- 校验一基础行数 SELECT COUNT(*) AS total_poems FROM poems; -- 校验二孤儿记录外键断裂 SELECT p.id, p.title, p.author_id FROM poems p LEFT JOIN authors a ON p.author_id a.id WHERE a.id IS NULL; -- 校验三标题作者维度的重复 SELECT title, author_id, COUNT(*) AS cnt FROM poems GROUP BY title, author_id HAVING cnt 1 ORDER BY cnt DESC LIMIT 20;这三条 SQL 是血泪经验的产物。孤儿记录通常是因为作者名在同音不同字「李贺」vs「李贺之」的情况下被清洗脚本拆成了两条而诗词导入时关联到了旧 ID。重复诗词则几乎必然存在——不同数据源对「一首诗」的定义不同有的把序和正文拆成两条有的把「其一」「其二」合并成一条。你不需要完全去重但至少要量化重复率太高的数据集不值得手工清洗换数据源重来可能更省钱。4. 常用查询写法从关键词搜索到按朝代聚合统计4.1 全文检索的替代方案LIKE与FULLTEXT的边界在哪古诗文检索里最常用的场景是「输入一句诗找出处」。LIKE %故人西辞黄鹤楼%能解决问题吗能但只在数据量小的时候。当你的poems表超过五万行LIKE %keyword%会触发全表扫描每次查询几十毫秒是常态并发上来就卡。MySQL 自带的FULLTEXT索引在 InnoDB 上支持中文分词5.7.6 之后引入ngram解析器但古诗文本里「之乎者也」这类虚词会被当成无意义词过滤而且ngram的分词粒度对五言七言的效果只能说勉强能用。我实际项目里更常用的做法是主查询用LIKE保证准确率对高频热词做缓存比如 Redis冷门词查询走FULLTEXT。如果你追求开箱即用的全文体验可以用 MySQL 的ngram全文索引做粗筛再用LIKE精排。ALTER TABLE poems ADD FULLTEXT INDEX ft_poem_search (title, content) WITH PARSER ngram; SELECT id, title, content FROM poems WHERE MATCH(title, content) AGAINST(黄鹤楼 IN NATURAL LANGUAGE MODE) LIMIT 10;WITH PARSER ngram指定了中文分词器默认的 token 大小是 2意味着「黄鹤楼」会被切分成「黄鹤」「鹤楼」两个 bigram。这有两个副作用第一单个汉字查不到比如查「楼」就搜不到第二短词的精确匹配会失灵。所以我对全文索引的判断是在数据量没到百万级之前LIKE加缓存是性价比最高的方案。真到了需要专业全文检索的量级你会需要的不是 MySQL 而是 Elasticsearch但那是另一个话题了。4.2 按朝代聚合统计理解GROUP BY与HAVING的实际用法按朝代统计诗词数量是这类数据库的高频需求做朝代时间线、诗词分布图都靠它。写法本身不复杂但有两个容易忽视的细节。第一GROUP BY要关联朝代表还是直接用诗词表里的dynasty_id第二排序时要不要排除掉那些「朝代未知」的数据。SELECT d.name AS dynasty, COUNT(p.id) AS poem_count FROM poems p INNER JOIN dynasties d ON p.dynasty_id d.id GROUP BY d.id, d.name ORDER BY poem_count DESC;这里GROUP BY d.id, d.name而不是只GROUP BY d.name是有讲究的。MySQL 的ONLY_FULL_GROUP_BY模式默认开启它要求SELECT中出现的非聚合列必须出现在GROUP BY子句中。按d.id分组就能满足这个要求同时加上d.name是保险做法。另一个常见的坑是「宋朝」和「北宋」「南宋」的关系——如果朝代表里这三个是独立记录统计就会分成三行。业务上如果需要合并展示你得在查询时用CASE WHEN合并这是表结构设计时就该想清楚的问题。HAVING的典型场景是筛掉数量过少的朝代比如只保留诗词数大于 100 的朝代。很多新手会在WHERE里写COUNT(p.id) 100然后报错。因为WHERE是在分组前过滤行而COUNT是分组后的聚合结果所以必须用HAVING。4.3 随机获取一首诗ORDER BY RAND()的性能陷阱很多诗词 App 的首页都有「每日一诗」功能新手自然想到SELECT * FROM poems ORDER BY RAND() LIMIT 1。这条 SQL 在 5 万行数据上跑一次大概要 200 毫秒不快但也能忍。但你的表涨到 20 万行以后ORDER BY RAND()要对全表生成随机数再排序耗时直接跳到秒级。推荐方案是「先随机拿 ID再按 ID 取记录」。前提是 ID 没有大规模空洞——删除操作会导致 ID 不连续所以先查MIN(id)和MAX(id)再取区间内的随机值若取到的 ID 不存在则重试一次。SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM poems; SELECT p.* FROM poems p WHERE p.id FLOOR( RAND() * (SELECT MAX(id) - MIN(id) 1) (SELECT MIN(id)) ) ORDER BY p.id LIMIT 1;多余的重试逻辑如果随机到空洞 ID可以通过取下一个存在的 ID 兜底。这个方案在 20 万行数据上单次查询稳定在 1 毫秒以下。如果你需要「每日固定一首」更简单的方式是直接ORDER BY id LIMIT 1 OFFSET N其中 N 是当天距离某基准日期的天数取模总数不需要任何随机函数。5. 避坑指南古诗词数据库最常见的 5 个翻车现场5.1 生僻字变问号字符集问题比你想的更隐蔽现象导入后查出来「龙」字位置显示为?或者应用端读出乱码。原因三个环节里任何一环出错都会导致这个结果——CSV 文件本身的编码不是 UTF-8、MySQL 连接时指定了错误的字符集、表的charset不是utf8mb4。最常见的其实是连接层你用 Python 的pymysql写入时没指定charsetutf8mb4MySQL 默认按utf8mb3处理遇到扩展字符直接丢弃。解决三管齐下。表结构统一utf8mb4连接串显式加charsetutf8mb4导入前SET NAMES utf8mb4;。另外用LOAD DATA INFILE时务必在语句里写CHARACTER SET utf8mb4不写就继承数据库默认字符集很容易踩中老库升级后默认值没变的坑。5.2 同一个作者出现「李白」「李 白」「李太白」三种写法现象按作者关联统计时李白的诗被拆成三条数字完全对不上。原因原始数据源各自为政。有的集子用字有的用名有的中间加了全角空格。解决没有银弹靠两步走。第一导入前做别名映射表把「太白」「李太白」「青莲居士」等映射到标准名「李白」。第二导入后用模糊匹配找出潜在重复组人工确认后合并author_id。注意这里的「人工」是必须的。完全依靠算法去重古诗作者会把「王维」和「王绩」这类名字相近但确实不同的作者错误的合并。5.3 标题里带「·」查询死活匹配不上现象SELECT * FROM poems WHERE title 水调歌头·明月几时有查到 0 行但表里明明有。原因源数据里的「·」可能是全角中圆点·、半角句点.或特殊 Unicode 点字符・肉眼几乎看不出差别但比较时完全不等。解决用十六进制确认字符的真相SELECT HEX(title) FROM poems WHERE id xxx;。一般你会看到E2 80 A2全角中圆点或EF BC 8E全角句点。统一清洗成同一种字符后再存库查询才能一次命中。5.4 Docker 里执行LOAD DATA INFILE报权限错误现象提示The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。原因MySQL 8.0 默认只允许从secure_file_priv指定目录读取文件Docker 容器里这个目录通常不是你映射进去的挂载点。解决两种路径任选。第一种把 CSV 复制到容器内默认的/var/lib/mysql-files/再执行第二种在docker run时加参数--secure-file-priv关闭限制或者直接把 my.cnf 挂载进去配置。我推荐第一种因为关闭全局安全限制不太好有 SQL 注入风险。5.5 诗词正文里有首行缩进和换行前端展示全乱现象导入时把古书排版的全角空格和换行也存了网页显示时左侧缩进参差不齐。原因只做去 HTML 标签没做空白字符规范化。解决导入清洗脚本里统一空格处理逻辑——全角空格转半角行首缩进去掉连续换行压缩为单换行。展示层的换行保留因为古诗格式本身依赖换行。这里要非常克制段内换行保留但每行前面不要有多余空格否则移动端窄屏下会惨不忍睹。6. 进阶从数据库到应用我的性能优化三个偏好最后一个部分讲点实用的技巧。当你的古诗词数据库跑起来之后会遇到几个「不做也行、做了更好」的性能优化点我按性价比从高到低讲。第一个优化点是「按需查列」。很多人写SELECT *习惯了但在诗词这种content字段动辄几百上千字比如《长恨歌》全文 800 多字的表里SELECT *会白白传输几 MB 的数据。列表页只需要标题和作者名详情页才需要content。我的常见做法是列表查询语句里明确列出字段或者把content挪到一张附属表poem_details里按需JOIN。数据翻倍后这个习惯能省下 80% 的无效 IO。第二个优化点是「索引不是越多越好」。诗词表上我最终只保留了四个索引主键、author_id、dynasty_id、title前缀索引。有段时间我为了加快搜索加了content的 B-Tree 索引结果每次插入都变慢而后台搜索走的是LIKE %关键词%前缀索引屁用没有白占空间。之后我养成了习惯每次写完一条查询先EXPLAIN看有没有走索引再问自己一句——这个索引真的有业务访问支撑吗没撑住的索引该删就删。第三个优化点是最容易被忽略的「分页深度」。后台管理页经常要查「第 10 万条之后的 20 条」LIMIT 100000, 20会让 MySQL 扫完前面 10 万行再丢弃耗时随页码线性增长。我的替代方案是记录上一页最后一条记录的 ID下一页查询变成WHERE id last_id ORDER BY id LIMIT 20这样不管翻到第几页扫描行数都恒定为 20。我的习惯是不把所有优化一次做完而是先看慢查询日志slow_query_log找到真正的瓶颈再下手。上次我为一个诗词 API 做调优前三个优化做完后 QPS 从 300 涨到了 2000第四个优化做完几乎没有任何变化——不加思考的堆优化只是在浪费时间。如果你照着上面的表结构和清洗流程走一遍大概率会在第一次SELECT时得到一份能直接用的诗词数据而不是像我当时第一版那样对着三万多条脏数据毫无头绪。这是这个方向最值得投入的原因数据层稳固了上层的搜索、推荐、可视化都只是时间问题。希望帮到你。本文还有配套的精品资源点击获取