简介面向古诗词研究者、教育工作者与传统文化爱好者这份以 MySQL 存储的中华古诗词数据库将海量诗词文本结构化方便用户导入数据库后进行检索、统计与二次开发。压缩包共含 318 个 SQL 文件整体约 53.85MB覆盖唐代至宋代的主要诗人与作品包括约 5.5 万首唐诗和 21050 首宋词并区分 authors、poet 等不同表结构便于分类查询。资源内含完整的建库脚本使用者可直接导入 MySQL 使用无需手工整理数据整个包已有 544 人学习下载。对于需要批量分析诗词内容、构建诗词类应用或整理教学素材的读者这份资料能省去大量数据采集与清洗时间以规范化的数据格式呈现唐诗宋词的丰富风貌兼具学习与实用价值。1. 古诗词库(中文简体-MySQL一个不是为收藏而是为检索而生的数据工程看到“古诗词库(中文简体-MySQL”这个标题第一反应是下载一个现成的 SQL 文件导入完事。但真正动手才发现这个项目的难点根本不在 MySQL 的安装配置上而在“中文数据”四个字上字符集乱码、繁体异体、标点不统一、全文检索索引不生效每一步都能让新手翻车。它的本质不是存数据而是把几十万行古诗文变成可以被“朝代、作者、关键词、名句”任意组合检索的数据源。适合正在学 MySQL 的开发者练手也适合做 JavaWeb、Python 后端时需要一个语料库支撑搜索功能。反直觉的结论是古诗词库的数据量撑死几十万行任何一台 2 核 4G 的机器都能跑性能瓶颈从来不在容量而在你写的 SQL 和表结构是否经得起中文检索。2. 先设计表结构诗人与诗词两张表怎么建才不返工2.1 为什么不能把所有字段塞进一张大宽表公开渠道能找到的古诗词语料大部分是一行一首诗包含作者、朝代、标题、正文、注释、译文等字段。很多第一次做的人图省事直接建一张poem_all表把所有字段塞进去结果查询时发现问题想看某个诗人有多少首诗要GROUP BY author_name但如果同一作者在不同朝代出现比如“李煜”既是南唐也是宋初人物统计口径就会乱想把“诗题”和“正文”分开做索引宽表里单独拎一个字段出来加索引也很别扭。我一般会拆成两张表poet存诗人信息poem存诗词正文。分开的最大理由是减少冗余同一诗人只存一次修改诗人的生平介绍时只动一行其次是查询语义更清晰按朝代统计、按作者聚合、按名句搜索都能直接走主键和外键关联而不是靠字符串比较。这个设计对几十万行的数据量来说算不上性能优化但能让后续所有业务 SQL 少绕弯路。2.2 字段类型、字符集与排序规则的选择poet表的字段不用复杂id主键、name诗人名、dynasty朝代、birth_year和death_year用SMALLINT或INT允许 NULL很多诗人生卒年不详存 0 反而不如 NULL 干净、description用TEXT。poem表要有id、poet_id外键、title、content、translation译文、notes注释。这里的核心决策是正文和译文一定用TEXT而不是VARCHAR(255)。一首长诗《长恨歌》正文超过 800 字VARCHAR最大 65535 字节在utf8mb4下约等于 16383 个字符理论上装得下但TEXT在存储长文本时更省空间而且不会因为单行太长触发行溢出相关的性能问题。字符集只有一个正确选择utf8mb4。古诗词里生僻字、异体字、注释里的特殊符号只有utf8mb4能完整容纳。排序规则我选utf8mb4_unicode_ci它对中文的排序和比较比utf8mb4_general_ci更准确代价是稍慢一点但古诗词库的数据量压根感知不到差别。需要注意如果你用的 MySQL 版本是 5.7不要写utf8mb4_0900_ai_ci那是 MySQL 8.0 才有的排序规则5.7 会直接报错。CREATE DATABASE IF NOT EXISTS shici DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE shici; CREATE TABLE poet ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 诗人主键, name VARCHAR(50) NOT NULL COMMENT 诗人姓名, dynasty VARCHAR(20) NOT NULL COMMENT 朝代如唐/宋/清, birth_year SMALLINT UNSIGNED DEFAULT NULL COMMENT 生年不确定填NULL, death_year SMALLINT UNSIGNED DEFAULT NULL COMMENT 卒年不确定填NULL, description TEXT COMMENT 诗人简介, PRIMARY KEY (id), KEY idx_dynasty_name (dynasty, name) ) ENGINEInnoDB COMMENT诗人表; CREATE TABLE poem ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 诗作主键, poet_id INT UNSIGNED NOT NULL COMMENT 关联poet.id, title VARCHAR(200) NOT NULL COMMENT 诗题, content TEXT NOT NULL COMMENT 正文保留原始换行, translation TEXT COMMENT 白话译文可空, notes TEXT COMMENT 注释/赏析可空, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_poet_id (poet_id), KEY idx_title (title), CONSTRAINT fk_poem_poet FOREIGN KEY (poet_id) REFERENCES poet (id) ON DELETE CASCADE ) ENGINEInnoDB COMMENT诗词正文表;建表语句里的参数有几个值得说明。ENGINEInnoDB必须写MyISAM虽然全文索引老版本也支持但不支持事务和外键清洗数据中途报错会导致表损坏。ON DELETE CASCADE是故意加的如果某个诗人因为数据错误要删除他的诗作应该一起清掉避免孤儿数据。idx_dynasty_name这个联合索引配合「先选朝代再查诗人」的常见检索路径比单独的两个单列索引更省空间。content TEXT NOT NULL不加默认值是因为导入时字段必须有值空字符串也比 NULL 好处理。2.3 外键该不该加数据完整性优先古诗词库这种一次性导入、之后只读的项目外键看起来可有可无但我仍然建议加上。原因不是性能而是清洗阶段的自我保护导入时如果poem.poet_id指向一个不存在的诗人外键约束会直接拒绝插入错误信息能精确到哪条数据有问题如果在应用层做检查要额外写一遍判断逻辑出错还容易被忽略。等数据稳定后真要追求极致性能再考虑ALTER TABLE poem DROP FOREIGN KEY fk_poem_poet也不迟。前期的严格换来的是排查数据的成本大幅下降这笔账值得。3. 把公开语料清洗成 SQL从 JSON 到可以 source 的导入文件3.1 公开语料的常见形态与清洗项网上能找到的古诗词语料最常见的格式是 JSON 数组每个元素大概是{author:李白,dynasty:唐,title:静夜思,paragraphs:[床前明月光,疑是地上霜],notes:}这样。注意paragraphs是数组每句诗是独立元素而 MySQL 里我们想要的是把数组拼接成带换行符的完整文本这个转换如果用 SQL 做非常痛苦但在 Python 里就是一行\n.join(...)。清洗项里最重要的是四个第一繁体转简体。标题里写了“中文简体”如果语料源是繁体你需要用 OpenCC 这类库做转换但这会导致生僻字被转错的风险建议转换后抽查几十首第二统一标点。语料里有的用全角逗号有的用半角逗号有的句子末尾没有句号不统一会让后续按标点切分名句时出错第三去掉空段落。有些 JSON 里paragraphs是空数组这行数据要直接丢弃第四去重。同一首诗可能在不同朝代、不同诗人的条目里重复出现按“作者 标题 前 20 个字”做哈希去重比单纯按标题去重要可靠得多。3.2 用 Python 生成 SQL 文件而不是直接连库插入清洗完的数据我推荐的做法是用 Python 脚本生成一个.sql文件然后通过 MySQL 命令行source导入。为什么不推荐在 Python 里直接pymysql连库插入一是几十万条数据逐条插入太慢需要自己拼executemany分批二是清洗脚本和导入脚本混在一起一旦插入过程报错你分不清是数据问题还是连接问题。先生成 SQL 文件导入失败时可以直接定位到第几行报错重来也快。import json import re from pathlib import Path def clean_text(text: str) - str: # 统一全角/半角逗号与句号 text text.replace(,, ).replace(., 。) # 去掉首尾空白合并连续空行 text re.sub(r\n{2,}, \n, text.strip()) return text def to_sql_value(value: str | None) - str: if value is None: return NULL # 关键把单引号转义为两个单引号避免破坏SQL语法 return value.replace(, ) src Path(poetry.json) out Path(import_poetry.sql) rows json.loads(src.read_text(encodingutf-8)) seen set() poet_id_map {} # 作者名 - poet表里的id next_poet_id 1 next_poem_id 1 lines [] lines.append(SET NAMES utf8mb4;) lines.append(USE shici;) for item in rows: author item.get(author, ).strip() dynasty item.get(dynasty, ).strip() title item.get(title, ).strip() paragraphs item.get(paragraphs, []) notes item.get(notes, ) # 空段落直接跳过 if not paragraphs: continue content clean_text(\n.join(paragraphs)) # 按“作者标题前20字”去重 dedup_key f{author}|{title}|{content[:20]} if dedup_key in seen: continue seen.add(dedup_key) # 把作者名映射到自增id不查库靠内存字典 if author not in poet_id_map: poet_id_map[author] next_poet_id lines.append( fINSERT INTO poet (id, name, dynasty, description) VALUES f({next_poet_id}, {to_sql_value(author)}, {to_sql_value(dynasty)}, NULL); ) next_poet_id 1 pid poet_id_map[author] lines.append( fINSERT INTO poem (id, poet_id, title, content, translation, notes) VALUES f({next_poem_id}, {pid}, {to_sql_value(title)}, {to_sql_value(content)}, fNULL, {to_sql_value(notes)}); ) next_poem_id 1 out.write_text(\n.join(lines), encodingutf-8) print(f生成 {out}诗人 {next_poet_id - 1} 位诗作 {next_poem_id - 1} 首)这段脚本的核心逻辑是内存映射用poet_id_map字典把作者名映射到自增 id生成 SQL 时直接引用避免在导入阶段反复SELECT id FROM poet WHERE name...那会让文件生成时间翻好几倍。to_sql_value里的replace(, )是血泪经验——古诗词标题里带引号的情况比想象中多比如《酬乐天扬州初逢席上见赠》这类标题没有引号但注释里可能出现“所谓‘天涯’”不转义就会把 SQL 截断。这里的SET NAMES utf8mb4放在文件开头是给后续source导入时客户端连接用的字符集开关不能省。3.3 导入命令与两种导入方式的取舍生成好import_poetry.sql后导入命令是mysql -uroot -p --default-character-setutf8mb4 shici import_poetry.sql或者进入 mysql 客户端后执行source /绝对路径/import_poetry.sql。两者等价但source能看到每一条语句的执行结果导入出错时更容易定位。--default-character-setutf8mb4这个参数必须显式写否则客户端默认字符集可能是latin1中文文本写入表时被转成?问号。另一种更稳的方案是 Python 直连数据库并用executemany分批插入。好处是能用事务插入失败可以整体回滚不会留下半截数据坏处是清洗和导入耦合在一起数据量大了以后调试不直观。我的经验是第一次导入用生成 SQL 文件的方式因为它能留下一个可复查的中间产物之后再有增量更新才用executemany走连接池插入。还要注意max_allowed_packet参数默认 4M 对包含长诗的 SQL 文件来说偏小后面避坑章节会细说。4. 检索性能关键词、朝代、作者组合查询的索引与存储过程方案4.1 LIKE 模糊搜索为什么在大表上会拖垮查询数据量到十万行以后WHERE content LIKE %明月%这种查询会让 MySQL 做全表扫描因为它无法利用普通 B 树索引——%在关键词左边意味着索引无法定位起点。十万行数据全表扫描大概几百毫秒看起来还能忍但如果你要做的是名句检索应用用户每次输入都会触发这种扫描并发一上来查询会越来越慢。更麻烦的是LIKE %床前明月光%只能做连续字符串匹配用户输入“明月光 床前”这种带空格的组合这条 SQL 就完全失效了。4.2 用 ngram 全文索引解决中文检索的硬伤MySQL 5.7 之后支持中文全文索引核心是ngram解析器它把中文文本按固定长度切成词元。我在建表后做的第一件事就是加全文索引ALTER TABLE poem ADD FULLTEXT INDEX ft_content_ngram (content) WITH PARSER ngram; ALTER TABLE poem ADD FULLTEXT INDEX ft_title_ngram (title) WITH PARSER ngram;ngram_token_size是全文索引的核心参数默认是 2表示把文本每两个字切一个词元。对古诗词来说2 字词元基本够用比如“明月”“床前”“光”这种“光”是单字默认不建索引但用户搜“光”的场景本来就少不值得为它把ngram_token_size改成 1那会让索引体积暴增。这个参数在 MySQL 8.0 里可以动态设置5.7 需要改配置文件重启建议在建索引前确认。建好全文索引后查询用MATCH ... AGAINST而不是LIKESELECT p.id, p.title, po.name AS author, LEFT(p.content, 50) AS snippet FROM poem p JOIN poet po ON p.poet_id po.id WHERE MATCH(p.content) AGAINST(明月 故乡 IN BOOLEAN MODE);IN BOOLEAN MODE下表示关键词必须出现-表示不能出现不带符号表示可选。古诗词检索里最常见的组合是“要找同时出现两个词的诗句”这时候必须写明月 故乡如果写成空格分割MySQL 返回的是包含任意一个词的结果排列顺序可能让你以为检索失效。全文索引的排序默认按相关性但相关性算法并不理解古诗词语义所以不要让排序承担语义理解的责任排序规则留在业务层决定。4.3 朝代 作者 关键词组合查询的 SQL 模板实际场景里用户很少只搜一个关键词通常是“唐诗里带‘明月’的句子”或者“李清照写的带‘愁’的词”。组合查询的 SQL 要点是把精确匹配字段和全文索引字段分开处理精确匹配走普通索引全文检索走MATCH别把两者混在一个条件里。我给一个可复用的模板SELECT po.name AS author, po.dynasty, p.title, LEFT(p.content, 80) AS excerpt FROM poem p JOIN poet po ON p.poet_id po.id WHERE po.dynasty 唐 AND po.name 李白 AND MATCH(p.content) AGAINST(明月 IN BOOLEAN MODE) ORDER BY p.id DESC LIMIT 20;这里的优化点是po.dynasty 唐和po.name 李白能通过idx_dynasty_name联合索引快速过滤到几百条记录再在这几百条里做MATCH全文检索比反过来先全文索引全表再过滤朝代快一个数量级。ORDER BYp.id DESC是因为古诗语料通常乱序或按来源排列按主键倒序能保证每次查出来的结果相对稳定不会出现两次查询结果不一致的情况。LEFT(p.content, 80)做截断展示避免把整首《将进酒》拉到客户端再裁切网络传输成本差别很大。4.4 用存储过程封装高频检索DELIMITER 与参数化热词里反复出现 mysql 存储过程在这个项目里存储过程不是炫技而是有实际价值古诗词查询往往需要“按朝代查数量”“按作者查诗集”“按关键词查名句”三个固定入口把它们封装成存储过程后客户端只需要CALL search_by_keyword(唐, 明月, 20)不需要在 Java 或 Python 代码里拼 SQL也避免了字符串拼接带来的注入风险。写存储过程最需要注意的是DELIMITER它把默认的分号结束符临时改成$$否则 MySQL 客户端会误以为存储过程体里的分号是语句结束符。DELIMITER $$ CREATE PROCEDURE search_poems( IN p_dynasty VARCHAR(20), IN p_author VARCHAR(50), IN p_keyword VARCHAR(100), IN p_limit INT ) BEGIN SET sql SELECT po.name AS author, po.dynasty, p.title, LEFT(p.content, 80) AS excerpt FROM poem p JOIN poet po ON p.poet_id po.id WHERE 11; IF p_dynasty IS NOT NULL AND p_dynasty THEN SET sql CONCAT(sql, AND po.dynasty , p_dynasty, ); END IF; IF p_author IS NOT NULL AND p_author THEN SET sql CONCAT(sql, AND po.name , p_author, ); END IF; IF p_keyword IS NOT NULL AND p_keyword THEN SET sql CONCAT(sql, AND MATCH(p.content) AGAINST(, p_keyword, IN BOOLEAN MODE)); END IF; SET sql CONCAT(sql, ORDER BY p.id DESC LIMIT , p_limit); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;这段存储过程用的是动态 SQL因为MATCH ... AGAINST的参数不能直接用PREPARE绑定的占位符传入所以拼进字符串。注意我把LIMIT也用CONCAT拼进去了这要求调用方传入的p_limit必须是整数否则有注入风险。如果你的项目不允许动态 SQL也可以退一步把三个查询拆成三个存储过程每个写死条件虽然代码冗余但更安全。调用方式CALL search_poems(唐, 李白, 明月, 20);Oracle 和 MySQL 的存储过程语法有差异MySQL 里DELIMITER是客户端指令不是 SQL 语法所以通过 JDBC 或 Python 驱动调用CREATE PROCEDURE时不能用DELIMITER需要把整个存储过程体作为一个 SQL 字符串发送这是很多人直连数据库建存储过程时翻车的点。5. 避坑古诗词库导入与查询的 5 个常见问题5.1 乱码所有中文变成问号现象是导入后SELECT查出来全是???或者某些字变成锟斤拷这类乱码。原因是客户端连接字符集、文件字符集、表字符集三者不一致。生成 SQL 文件时 Python 默认写 UTF-8 没有 BOM但 mysql 客户端连接时默认字符集可能是latin1数据进入表之前就被转坏了。解决方法是导入前先执行SET NAMES utf8mb4;或者在命令行加--default-character-setutf8mb4。建表时确认DEFAULT CHARSET是utf8mb4。这里有个细节如果 SQL 文件里已经写了SET NAMES utf8mb4;命令行再传一次也不冲突两次保险。5.2 ERROR 1366 (HY000): Incorrect string value现象是导入时报错提示某一行某个字段有非法字符导入中断。原因是表或字段的字符集不是utf8mb4数据里存在生僻字或特殊符号。常见于从旧库导出的表还是utf8utf8mb3它最多存 3 字节字符像“”这种超出 BMP 的字直接报错。解决方法是把表转换成utf8mb4ALTER TABLE poem CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE poet CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意CONVERT TO CHARACTER SET会重写整张表几十万行可能需要几十秒甚至几分钟期间会锁表业务低峰期操作。5.3 关联查询报错 Illegal mix of collations现象是JOIN poem和poet表时MySQL 报Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)。原因是两张表的字符集虽然都是utf8mb4但排序规则不同MySQL 不允许直接比较不同排序规则的字符串字段。解决方法是统一排序规则优先改表而不是改查询ALTER TABLE poet CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;如果只在少数字段上不一致只改字段也行。这个错非常隐蔽因为两张表看起来都是utf8mb4不报错时你根本不会去检查排序规则。5.4 导入大文件提示 Got a packet bigger than max_allowed_packet现象是source导入到一半报错Got a packet bigger than max_allowed_packet bytes然后连接断开。原因是单条 SQL 语句太大超过了服务器端允许的最大包大小。古诗词里长诗上万字节而max_allowed_packet默认只有 4M一条 INSERT 语句包含整首《长恨歌》可能达到几百 KB正常情况下够用但如果你把多首诗合并成一条批量 INSERT很容易超限。解决方法是临时调大参数SET GLOBAL max_allowed_packet 134217728;134217728是 128M。注意这是全局参数新连接才会生效当前会话要执行SET SESSION max_allowed_packet 134217728;才能立即生效。如果还不行检查 SQL 文件本地有没有被某些编辑器做了隐式换行导致每行过长。5.5 标题和注释里的单引号、反斜杠导致 SQL 语法报错现象是导入到某条数据时报语法错误定位后发现是标题里带了或\把整条 INSERT 语句截断了。原因是生成 SQL 文件时没有做转义。最常见的是两个方案用 Python 字符串的replace(, )手工转义或者用pymysql.converters.escape_string()处理。前者对单引号足够后者能额外处理反斜杠等特殊字符。推荐后一种因为它覆盖的转义场景更全。如果已经导入了部分数据重导之前先TRUNCATE TABLE poem;别接着往半截数据里继续插否则主键和作者映射会乱掉。注意生成 SQL 文件后务必抽查文件里是否有疑似被截断的长行。一个简单办法是统计文件行数如果 INSERT 语句数量和你清洗时统计的记录数对不上说明有 SQL 语法错误被忽略了。6. 进阶验证用 EXPLAIN 和存储过程把古诗词库做成查询服务6.1 用 EXPLAIN 逐个验证查询计划建完索引、写完存储过程我会逐个核心查询跑一遍EXPLAIN确认它走了哪个索引。比如上面那条组合查询你的预期是先用idx_dynasty_name过滤朝代和作者再在结果集上做全文索引。但EXPLAIN结果可能出乎意料优化器可能选择先用全文索引因为MATCH的权重很高也可能选择先走诗人表再 nested-loop 关联。怎么看看possible_keys和key列。如果key显示idx_dynasty_name而Extra里出现Using where说明过滤是在索引后做的性能可接受如果key是NULL说明全表扫了需要ANALYZE TABLE poet;更新统计信息或者用FORCE INDEX临时干预。调优这件事没有一劳永逸唯一的可靠路径就是每次改动后重新EXPLAIN别凭感觉。6.2 做两个只读视图把高频查询变成固定入口视图在这个项目里最大的作用是简化业务代码。我建的第一个视图是v_poem_detail把诗人和诗作 join 好展示author、dynasty、title、content四个必要字段第二个视图是v_famous_line把正文按换行符切分成单句方便做“名句”展示。注意视图不是物化视图MySQL 每次查视图都会重新执行里面的 SQL所以视图内部必须复用索引不要在里面做LEFT(content, 20)这类无法用索引的函数操作。视图建好之后业务侧只对着视图写SELECT ... WHERE dynasty宋 AND author苏轼不用关心底层表结构。最后一件事是给这个库做一个定期验证的习惯。我过去每次导完数据只会统计SELECT COUNT(*) FROM poem但没有验证内容完整性某首诗的正文被截断了也不知道。现在我会随机抽 20 首人工比对原文和库里的内容并且写一个定时脚本统计poem表和poet表的主键最大值是否异常增长用来判断是否有重复导入。这个习惯帮我挡掉过好几次数据源坏掉的坑。做数据类的 MySQL 项目真正怕的不是不会写 SQL而是不知道数据什么时候已经错了。希望帮到你。本文还有配套的精品资源点击获取
