MySQL古诗词数据库实战：建表、导入与全文检索优化

简介这是一份面向古诗词爱好者、语文教师与文史研究者的MySQL诗词数据库资源以SQL脚本形式提供从古至今近30万首诗词的完整数据可直接导入本地MySQL环境解决诗词资料分散、检索困难的问题。压缩包内共1个文件为sql格式整体约43.55MB运行后即可自动建表并填充数据。数据表设计涵盖诗词ID、标题、作者姓名、朝代、诗词内容、注释、韵脚、流派及作者简介等字段支持按作者、朝代、流派等多维度检索与统计分析。目前已有3401人学习下载适合用于教学备课、诗词比较研究、文学流派梳理及大数据分析等场景帮助读者快速搭建属于自己的诗词数据平台感受中华诗词的艺术魅力与历史深度。1. 古诗词数据库落地从一张 mysql 数据表到可检索的诗词全集手里有一份号称「最全」的中国古今诗词集几万到几十万首不等格式五花八门——TXT、CSV、JSON、甚至从网页扒下来的半结构化文本。真正要用的时候才发现全文检索慢、作者朝代对不上、异体字乱码、重复收录一堆。这时候把数据规整进 mysql 数据表几乎是绕不开的一步有了结构化的古诗词数据库才能做按朝代筛选、按作者聚合、按词牌统计、全文模糊匹配这些事。这篇笔记面向的是手里已经拿到诗词原始数据、想用 mysql 建库建表并跑通查询的开发者也适合做 JavaWeb 项目实例、需要一份真实中文语料练手的人。下面从表结构设计讲到导入脚本再到查询优化和踩坑全部是可复现的命令和代码。2. 古诗词数据表怎么设计字段、类型与索引的取舍2.1 先想清楚一首诗到底有哪些属性诗词数据的字段看着简单真动手就会纠结。一首诗至少有标题、作者、朝代、正文、体裁诗/词/曲、标签如「唐诗三百首」。词还多一个词牌名有的还有序言、注释、译文。如果一股脑塞进一张宽表字段会膨胀到二三十个大部分行都是 NULL查询和存储都不划算。我一般的做法是拆成两张核心表poems存诗词主体authors存作者信息。作者表单独拆出来的理由是——同一个作者会出现在成百上千首诗里把朝代、生卒年、简介放在作者表既省空间改一处就全生效也方便做「按作者聚合」的统计。体裁、标签这类多值属性如果只是简单分类用枚举字段就够如果要做多标签检索再补一张poem_tags关联表。字段类型上有几个具体决定。正文用TEXT而不是VARCHAR因为长调、排律可能超过几千字VARCHAR在 utf8mb4 下单行有长度限制容易截断。标题、作者名用VARCHAR(255)足够。朝代用VARCHAR(32)而不是枚举因为「先秦」「魏晋」「五代十国」这类值不固定硬编码枚举以后加数据要改表结构。created_at用DATETIME并设默认值方便后续增量同步。2.2 建表语句与字符集这个必踩的坑字符集是古诗词数据库第一个翻车点。诗词里有大量生僻字、异体字比如「龘」「」这种超出基本多文种平面的字符用utf8其实是 utf8mb3存会直接报错或变成问号。必须用utf8mb4排序规则用utf8mb4_unicode_ci或utf8mb4_general_ci前者对多语言更准后者快一点。-- 建库字符集必须是 utf8mb4否则生僻字存不进去 CREATE DATABASE poetry_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE poetry_db; -- 作者表一个作者一行朝代、简介放这里 CREATE TABLE authors ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL COMMENT 作者名, dynasty VARCHAR(32) DEFAULT NULL COMMENT 朝代如唐、宋, birth_year SMALLINT DEFAULT NULL COMMENT 生年公元前用负数, death_year SMALLINT DEFAULT NULL COMMENT 卒年, intro TEXT COMMENT 作者简介, PRIMARY KEY (id), UNIQUE KEY uk_name_dynasty (name, dynasty), KEY idx_dynasty (dynasty) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 诗词主表正文用 TEXT避免长调被截断 CREATE TABLE poems ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL COMMENT 标题, author_id INT UNSIGNED DEFAULT NULL COMMENT 关联 authors.id, dynasty VARCHAR(32) DEFAULT NULL COMMENT 冗余朝代便于直接筛选, genre VARCHAR(16) DEFAULT NULL COMMENT 体裁诗/词/曲, cipai VARCHAR(64) DEFAULT NULL COMMENT 词牌名诗为 NULL, content TEXT NOT NULL COMMENT 正文, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_author (author_id), KEY idx_dynasty_genre (dynasty, genre), KEY idx_cipai (cipai), FULLTEXT KEY ft_content (title, content) WITH PARSER ngram ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;逻辑说明authors表用(name, dynasty)做唯一键防止同一作者重复插入poems表里冗余了一个dynasty字段看起来违反范式但查询「唐宋诗词」时不用每次 JOIN 作者表是典型的读多写少场景下的空间换时间。FULLTEXT ... WITH PARSER ngram是中文全文检索的关键MySQL 默认分词按空格切中文整段会被当成一个词必须用 ngram 分词器按 2-gram 切分。参数说明ngram_token_size默认是 2意味着「春风」会被切成一个 token检索「春风」能命中但检索单个「春」字命中不了。如果要做单字检索得在 my.cnf 里把ngram_token_size改成 1但索引会变大、检索变慢一般不建议。SMALLINT存年份够用范围 -32768 到 32767公元前用负数表示。提示建表前先确认SHOW VARIABLES LIKE ngram_token_size;如果返回空说明当前 MySQL 没装 ngram 插件全文索引会建失败。3. 把诗词数据导进 mysql清洗、批量插入与去重3.1 原始数据清洗的三个动作拿到手的诗词数据直接 INSERT 十有八九会失败或产生脏数据。导入前我固定做三件事。第一统一换行符把\r\n和\r都换成\n否则正文里会混入不可见字符检索时对不上。第二去掉正文首尾空白和全角空格\u3000很多从网页扒的数据每行前面带一堆全角空格。第三作者名归一化把「李白」「李太白」「青莲居士」这类指向同一人的写法合并这一步通常靠一张别名映射表人工维护没有银弹。清洗用 Python 做最顺手因为要处理编码和正则。下面这段脚本读一个 JSON 数组格式的诗词文件清洗后批量写入。import json import re import pymysql # 全角空格、零宽字符、多余换行统一清理 def clean_text(s): if not s: return s s.replace(\r\n, \n).replace(\r, \n) s s.replace(\u3000, ).replace(\u200b, ) s re.sub(r[ \t]\n, \n, s) # 行尾空白 s re.sub(r\n{3,}, \n\n, s) # 连续空行压成一个 return s.strip() # 作者别名映射实际项目里从单独的表或文件加载 ALIAS {李太白: 李白, 青莲居士: 李白, 杜工部: 杜甫} def norm_author(name): name clean_text(name) return ALIAS.get(name, name) conn pymysql.connect( host127.0.0.1, port3306, userroot, passwordyourpass, databasepoetry_db, charsetutf8mb4 ) with open(poems.json, encodingutf-8) as f: raw json.load(f) rows [] for item in raw: title clean_text(item.get(title)) content clean_text(item.get(content)) if not title or not content: continue # 标题或正文为空直接丢弃 rows.append(( title, norm_author(item.get(author, )), clean_text(item.get(dynasty, )), clean_text(item.get(genre, )), clean_text(item.get(cipai, )) or None, content, )) sql INSERT IGNORE INTO poems (title, author_id, dynasty, genre, cipai, content) VALUES (%s, NULL, %s, %s, %s, %s) with conn.cursor() as cur: cur.executemany(sql, [(r[0], r[2], r[3], r[4], r[5]) for r in rows]) conn.commit() print(inserted:, cur.rowcount) conn.close()逻辑说明clean_text集中处理所有文本字段避免每个字段各写一遍。INSERT IGNORE配合唯一索引能跳过重复行但注意这里poems表没有对(title, author_id)建唯一键所以IGNORE实际拦不住重复——真正去重要么在 Python 里用 set 去重要么给表加唯一键。参数说明executemany比循环单条execute快一个数量级几万条数据几秒能跑完charsetutf8mb4必须显式指定否则连接层会用默认字符集生僻字照样丢。3.2 用 LOAD DATA 处理百万级数据数据量上到几十万、上百万条时Python 逐条插入就慢了。这时候把清洗后的数据导出成制表符分隔的文本用LOAD DATA LOCAL INFILE导入速度能快好几倍。# 先把清洗结果导出为 TSV字段间用 \t行内换行用 \n 转义 # 然后登录 mysql 执行 mysql -u root -p poetry_dbLOAD DATA LOCAL INFILE /data/poems_clean.tsv INTO TABLE poems CHARACTER SET utf8mb4 FIELDS TERMINATED BY \t LINES TERMINATED BY \n (title, dynasty, genre, cipai, content) SET author_id NULL;逻辑说明LOCAL表示文件在客户端机器上需要客户端和服务端都开启local_infile。FIELDS TERMINATED BY \t要求导出时字段里不能含制表符清洗阶段要把正文里的\t替换成空格。参数说明如果导入时报ERROR 1148说明服务端local_infileOFF用SET GLOBAL local_infile1;打开生产环境出于安全考虑通常禁用 LOCAL改用LOAD DATA INFILE把文件放到服务端目录。注意LOAD DATA遇到一行字段数不对会整行报错并中断导入前先用head -n 5检查 TSV 列数是否一致。4. 古诗词数据库的查询与全文检索从 LIKE 到 ngram4.1 按朝代、作者、词牌做结构化筛选结构化查询是最常用的。比如查唐代所有李白的诗或者统计各朝代诗词数量。这类查询走普通索引就够关键是别让索引失效。-- 查李白在唐代的诗走 idx_author 和 idx_dynasty_genre SELECT p.title, p.content FROM poems p JOIN authors a ON p.author_id a.id WHERE a.name 李白 AND p.dynasty 唐 ORDER BY p.id LIMIT 50; -- 统计每个朝代的诗词数量按数量倒序 SELECT dynasty, COUNT(*) AS cnt FROM poems WHERE dynasty IS NOT NULL GROUP BY dynasty ORDER BY cnt DESC; -- 查某个词牌下的所有词 SELECT title, content FROM poems WHERE cipai 水调歌头;逻辑说明第一条查询里a.name 李白走authors的唯一键p.dynasty 唐走idx_dynasty_genreJOIN 时p.author_id走idx_author三个索引都用上了。参数说明ORDER BY p.id而不是ORDER BY p.title因为按主键排序能直接用聚簇索引按标题排序会触发 filesort数据量大时明显变慢。如果确实要按标题排考虑给title加索引但中文排序结果依赖 collationutf8mb4_unicode_ci和utf8mb4_general_ci排出来可能不一样。4.2 中文全文检索为什么必须用 ngram用LIKE %春风%查诗词数据量小的时候能用上到十万条就是全表扫描几秒起步。MySQL 的全文索引默认按空格和标点分词中文没有空格整句会被当成一个词所以必须用 ngram 分词器。ngram 把文本按固定长度默认 2切成连续片段「春风又绿江南岸」会被切成「春风」「风又」「又绿」「绿江」「江南」「南岸」这些 token检索「春风」就能命中。-- 用全文索引查含「春风」的诗词比 LIKE 快得多 SELECT title, dynasty, MATCH(title, content) AGAINST(春风 IN NATURAL LANGUAGE MODE) AS score FROM poems WHERE MATCH(title, content) AGAINST(春风 IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 20;逻辑说明MATCH ... AGAINST必须和建表时的FULLTEXT KEY ft_content (title, content)字段完全一致否则用不上索引。IN NATURAL LANGUAGE MODE是自然语言模式按相关度打分如果要做「必须包含某词」的精确匹配用IN BOOLEAN MODE语法是AGAINST(春风 -秋雨 IN BOOLEAN MODE)表示必须含-表示必须不含。参数说明ngram 默认 token 长度 2检索词少于 2 个字比如单字「春」在自然语言模式下匹配不到这是最常见的「明明有数据却查不出来」的原因。提示全文索引的score是相对值不同查询之间不可比只用于同一查询内排序。5. 导入和检索古诗词数据时的常见问题排查5.1 生僻字变成问号或插入报错现象插入含「」「龘」等字的诗词时报Incorrect string value或者存进去查出来是?。原因数据库、表、连接三层字符集只要有一层不是 utf8mb4 就会出问题最常见的是连接层用了默认的 latin1 或 utf8mb3。解决建库建表显式指定utf8mb4连接串里加charsetutf8mb4Python 的 pymysql 要传charsetutf8mb4JDBC 连接串加characterEncodingutf8connectionCollationutf8mb4_unicode_ci。改完用SHOW VARIABLES LIKE character_set%;逐项确认。5.2 全文检索查不到明明存在的词现象MATCH ... AGAINST(春)返回空但表里确实有含「春」的诗。原因ngram_token_size 默认是 2单字检索匹配不到。解决要么检索词至少两个字要么把ngram_token_size改成 1 并重建全文索引。改配置要重启 MySQL重建索引用ALTER TABLE poems DROP INDEX ft_content, ADD FULLTEXT ft_content (title, content) WITH PARSER ngram;。注意改小 token 会让索引体积明显增大。5.3 批量导入中途报字段数不匹配现象LOAD DATA跑到一半报Row 12345 doesnt contain data for all columns。原因某行正文里含制表符或换行符被当成了字段分隔符或行分隔符。解决导出 TSV 前把正文里的\t替换成空格把\n转义成\\n导入时用FIELDS TERMINATED BY \t ESCAPED BY \\配合。更稳妥的做法是导入前用脚本校验每行列数。5.4 重复数据越导越多现象同一首诗在表里出现多次COUNT(*)比原始数据多。原因INSERT IGNORE只在有唯一键约束时才生效poems表如果没建(title, author_id, content)唯一键重复行照插不误。解决给表加唯一键UNIQUE KEY uk_poem (title, author_id, content(255))注意content是 TEXT做唯一键要指定前缀长度前缀太短可能误判不同诗为重复太长索引会超限。或者干脆在导入脚本里用 set 按(title, author)去重。5.5 查询慢EXPLAIN 显示全表扫描现象按作者查诗要好几秒EXPLAIN里typeALL。原因WHERE条件里的字段没索引或者对索引列用了函数、隐式类型转换导致索引失效。比如WHERE dynasty 唐能走索引但WHERE LEFT(dynasty,1) 唐就走不了。解决用EXPLAIN看key列是否命中预期索引没命中就补索引或改写 SQL。古诗词数据读多写少索引可以适当多加代价是导入变慢。6. 让古诗词数据库更好用增量同步与检索效果验证数据导进去只是开始真正长期用起来还得解决两件事新数据怎么增量进来以及检索结果到底准不准。增量同步我一般用「更新时间戳 唯一键」的组合。给poems表加一个updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP每次从上游数据源拉取时只取updated_at晚于上次同步时间的记录用INSERT ... ON DUPLICATE KEY UPDATE写入。这样既不会重复插入也能更新已有记录的正文。如果上游是另一张 mysql 表直接INSERT INTO poems SELECT ... FROM source WHERE updated_at ?就行跨库的话用mysqldump导出增量再导入或者上主从复制把远程库的这张表同步到本地配置好server-id、log-bin和CHANGE MASTER TO之后从库会自动追平。检索效果验证这块很多人导完数据就完事结果上线发现搜「明月」出来的全是含「明月」但完全不相关的打油诗。我的习惯是准备一组「查询-期望结果」的测试用例比如查「静夜思」应该第一条就是李白那首查「水调歌头」应该优先出苏轼的。用下面这段脚本跑一遍看命中率和排序。import pymysql CASES [ (静夜思, 李白), (水调歌头, 苏轼), (春江花月夜, 张若虚), ] conn pymysql.connect(host127.0.0.1, userroot, passwordyourpass, databasepoetry_db, charsetutf8mb4) with conn.cursor() as cur: for kw, expect_author in CASES: cur.execute( SELECT p.title, a.name, MATCH(p.title, p.content) AGAINST(%s) AS score FROM poems p LEFT JOIN authors a ON p.author_id a.id WHERE MATCH(p.title, p.content) AGAINST(%s) ORDER BY score DESC LIMIT 3 , (kw, kw)) rows cur.fetchall() hit any(r[1] expect_author for r in rows) print(f{kw}: {命中 if hit else 未命中} - {rows}) conn.close()逻辑说明这段脚本对每个关键词取相关度最高的三条检查期望作者是否在结果里。参数说明MATCH的查询词和AGAINST的参数要一致score越高越相关。如果发现某类查询总是排不对可以考虑给标题单独加权重——MySQL 全文索引不支持字段权重变通办法是把标题重复拼进一个隐藏的检索字段或者干脆上 Elasticsearch 做更细的排序。我踩过最深的一个坑是早期图省事把作者名直接存在poems表里没拆表后来要改一个作者的朝代得UPDATE几万行锁表锁到业务报警。从那以后凡是「一对多」的属性我一律先拆表。另一个习惯是导入前一定先在小批量数据上跑通全流程确认字符集、去重、全文索引都正常再上全量——全量导入跑一半失败重来那种等待是真的难受。希望帮到你。本文还有配套的精品资源点击获取