古诗词MySQL数据库：表结构设计、导入与查询实战

简介一份古诗词MySQL数据库资源面向诗词爱好者、语文教师、研究者及文本挖掘开发者用于搭建本地诗词查询与分析环境解决海量诗词资料难以系统整理的问题。资源包仅包含1个SQL文件压缩后大小为43.55MB导入MySQL即自动生成数据表并填充近30万首诗词数据无需手工录入。表结构围绕创作与阅读场景设计了诗词ID、标题、作者、朝代、内容、注释、韵脚、流派、作者简介等字段可按诗人查全集、按朝代看风格、按流派做对比也支持基于韵脚和内容的量化研究。目前已有3403人浏览或下载学习。借助此库教师能快速获取备课素材研究者可深挖文学规律开发者可搭建诗词搜索或学习应用适用于诗词检索、在线诵读、传统文化教学课件等内容开发是一份可直接落地的传统文化数字资源。1. 古诗词数据库打开就能用的MySQL数据表专治诗词数据零散问题做诗词类网站、国学小程序或者自然语言处理语料时最头疼的不是写代码而是找一份像样的底表。网上的诗词数据要么按作者零散分布要么抓下来带着乱码和繁体异体字要么朝代信息缺到没法按时间轴做筛选。这份古诗词MySQL数据表直接把问题收口了解压后是一个结构完整的SQL文件包含朝代、诗人、诗词正文、名句摘录四张核心表导入MySQL即可开始增删改查适合正在做内容管理系统、公众号配文工具或语料分析项目的一线开发者直接落库使用。2. 表结构设计四张表怎么拆字段为什么这么定拿到SQL文件先别急着导入建议先用文本编辑器打开看一眼建表语句。这份资源的表结构设计得比较接近生产环境实际用法不是把所有诗词塞进一张大表的偷懒做法下面拆开讲。2.1 四张核心表与各自职责整份库拆成四张表朝代表、诗人表、诗词表、名句表。拆表的好处是数据不冗余例如诗人名、生卒年、字号这些信息只存在诗人表里一份诗词表通过诗人ID去关联查询时再用JOIN拼回来。表名核心字段职责说明dynastyid, name, start_year, end_year存放从先秦到近现代的朝代或时期poetid, name, dynasty_id, alias, birth_year, death_year, intro诗人基本信息与所属朝代poemid, poet_id, dynasty_id, title, type, content, created_at诗词正文type区分诗、词、曲、文famous_lineid, poem_id, content, note独立名句表按句子粒度做检索更快2.2 字段类型选择为什么用这些类型ID字段用的是自增INT配合主键索引。对古诗词这个量级的数据诗词总量大概在十几万条INT的四十多亿上限完全够用没必要上BIGINT徒增存储开销。正文和名句字段选择的是TEXT而非VARCHAR。VARCHAR虽然也能存长文本但超出长度后要么报错要么截断而且VARCHAR在排序和临时表操作时比TEXT更吃内存。TEXT类型上限是65535字节一首词加标点也就几百字节完全放得下。朝代字段设计成SMALLINT类型的ID而不是直接存朝代名字符串这样统计“哪个朝代存诗最多”时按ID分组比按名字分组效率高。CREATE TABLE poem ( id int(11) NOT NULL AUTO_INCREMENT, poet_id int(11) NOT NULL DEFAULT 0, dynasty_id smallint(6) NOT NULL DEFAULT 0, title varchar(255) NOT NULL, type varchar(10) NOT NULL DEFAULT 诗, content text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_poet_id (poet_id), KEY idx_dynasty_id (dynasty_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段建表语句里有两个容易被忽略的设计细节。一个是 poet_id 和 dynasty_id 都单独建了普通索引这决定了后面按诗人查作品、按朝代做统计时能走索引而不是全表扫描。另一个是 ENGINE 指定为 InnoDB它支持行级锁和事务做批量导入或并发查询时比 MyISAM 稳。CREATE TABLE famous_line ( id int(11) NOT NULL AUTO_INCREMENT, poem_id int(11) NOT NULL DEFAULT 0, content varchar(255) NOT NULL, note varchar(255) DEFAULT NULL, PRIMARY KEY (id), KEY idx_poem_id (poem_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;名句表单独存一个短句字段而不是从 poem.content 里做 LIKE 截取是为了检索性能。用户搜索“床前明月光”时直接查 famous_line.content 可以走索引前缀匹配如果每次都在几万字的长文本里 LIKE 搜数据库会慢一个数量级。2.3 字符集与排序规则utf8mb4 不是可选项建表语句里 CHARSETutf8mb4 这句务必保留。很多老库用的是 utf8但 MySQL 的 utf8 实际只支持最多三字节编码存不了生僻字和冷门异体字。诗词库里恰好容易遇到这类字比如“澹”“麴”“龘”这类字。utf8mb4 才是完整的四字节 UTF-8 实现兼容所有 Unicode 字符。排序规则这里用的是 utf8mb4_general_cici 表示大小写不敏感。如果对中文排序准确性有更高要求可以改成 utf8mb4_unicode_ci它按 Unicode 默认排序规则比较排序结果更接近字典序代价是略微多花一点 CPU。这类文本检索场景一般建议用 unicode_ci古诗文里对排序准确性有诉求时会体现出差别。2.4 索引设计要点JOIN 字段全部建索引四张表之间的关联字段——poet.dynasty_id、poem.poet_id、poem.dynasty_id、famous_line.poem_id——在原始SQL里都已经建好普通索引不需要额外补。新手拿到库后容易犯的毛病是直接对 content 字段建索引不仅没效果还白白占磁盘空间。TEXT 类型必须指定前缀长度才能建索引而且 LIKE %关键词% 这种模糊查询即使有索引也用不上。3. 导入 MySQL从命令行到客户端的完整步骤SQL文件拿到手后导入是关键一步。常见的翻车点集中在字符集、文件编码和导入权限这三类问题上。下面按两种方式分别操作。3.1 命令行导入最靠谱的路径假设SQL文件放在 /data/shici/shici.sql直接在终端执行mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS shici DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; mysql -uroot -p shici /data/shici/shici.sql第一条命令把数据库 shici 建出来并显式指定字符集为 utf8mb4。这一步很重要如果数据库层面默认是 latin1后面任何中文都会变成问号。第二条命令把整个SQL文件内容定向给 mysql 客户端执行重定向符号 是告诉 shell 把文件当作命令行输入。mysql -uroot -p --default-character-setutf8mb4 shici /data/shici/shici.sql加上 --default-character-setutf8mb4 参数的作用是强制客户端与服务器通信时使用 utf8mb4 编码避免SQL文件里的中文在连接层被错误转码。如果SQL文件本身是 UTF-8 编码这个参数能让导入过程少踩很多乱码坑。3.2 进入 MySQL 后使用 source 命令如果文件路径里有中文或者想在导入前先看表结构可以先登进 MySQL 再执行 sourceUSE shici; SOURCE /data/shici/shici.sql;source 命令的路径是相对当前客户端所在机器解析的不是相对数据库服务器。如果客户端和服务器不在同一台机器上source 读取的必须是本机路径。这也是它和重定向导入最大的区别重定向是服务器角度处理输入source 是客户端读取文件内容后一条条发送。3.3 导入后的完整性校验导入完成后不要急着写业务代码先做三个校验动作分别看表数量、行数和关键表的字段分布USE shici; SHOW TABLES; SELECT COUNT(*) AS poet_cnt FROM poet; SELECT COUNT(*) AS poem_cnt FROM poem; SELECT COUNT(*) AS line_cnt FROM famous_line;如果 poet 表行数能在万级、poem 表在十万级基本可以确认数据完整。行数对不上时优先检查 SQL 文件里是否有 DROP TABLE 语句被执行过——有些SQL文件会在开头 DROP TABLE IF EXISTS重复导入会把已有数据先清掉。3.4 导入报错的通用排查顺序导入报错时按三个方向排查效率最高先确认数据库字符集是否为 utf8mb4再确认SQL文件的编码是否为 UTF-8 且不带 BOM 头最后看是不是 max_allowed_packet 太小导致大批量插入失败。前两个跟乱码相关最后一个是经典报错 Got a packet bigger than max_allowed_packet bytes后面避坑章节会专门展开。4. 查询实战按朝代、诗人、名句检索的 SQL 写法数据导入只是开始实际业务中高频需求是三类统计各朝代诗词量、查某位作者的全部作品、按关键词搜名句。这三类查询的SQL写法刚好覆盖分组聚合、子查询和多表关联是这套库最常用的几条语句。4.1 按朝代统计诗词数量SELECT d.name AS dynasty_name, COUNT(p.id) AS poem_count FROM dynasty d LEFT JOIN poem p ON d.id p.dynasty_id GROUP BY d.id, d.name ORDER BY poem_count DESC;这条语句把朝代表和诗词表做关联按朝代分组后统计每组的诗词数最后按数量降序排列。LEFT JOIN 保证了一条诗都没有的朝代也会出现在结果列表里配合 COUNT(p.id) 只统计非空诗词ID统计数据不会失真。输出结果会直观看到某个朝代的存量远高于其他时期这在内容运营选专题时很有参考价值。SELECT d.id, d.name, COUNT(p.id) AS poem_count FROM poem p RIGHT JOIN dynasty d ON p.dynasty_id d.id GROUP BY d.id, d.name ORDER BY poem_count DESC;这里用 RIGHT JOIN 是等效写法两种关联方向结果一致。选哪种取决于习惯但 GROUP BY 后面最好带上 d.id 和 d.name 两列只 group by d.name 在 MySQL 的 ONLY_FULL_GROUP_BY 模式下会直接报错。4.2 查询某位诗人的全部作品SELECT p.title, p.type, LEFT(p.content, 50) AS content_preview FROM poem p JOIN poet a ON p.poet_id a.id WHERE a.name 李白 ORDER BY p.created_at ASC;JOIN 诗人表后用 WHERE 条件过滤诗人名字再取正文前50个字做预览。这里没有直接拿 LIKE 去查 poem 表而是先精确命中诗人ID再通过 poet_id 索引回表取数据查询效率远高于全表扫描。ORDER BY created_at 让作品按录入时间排出先后顺序。4.3 名句关键词检索SELECT fl.content AS line_content, p.title AS poem_title, a.name AS poet_name FROM famous_line fl JOIN poem p ON fl.poem_id p.id JOIN poet a ON p.poet_id a.id WHERE fl.content LIKE %明月% LIMIT 20;查名句表而不是诗词表是这个库最值得利用的设计。famous_line 里每条都是完整短句用户搜索“明月”“春风”“相思”这类关键词时LIKE %明月% 走一次前缀整整20毫秒上下就能出结果。如果去 poem.content 里搜长文本字段的 LIKE 性能会差很多。4.4 用 EXPLAIN 验证索引是否生效EXPLAIN SELECT d.name, COUNT(p.id) AS poem_count FROM dynasty d LEFT JOIN poem p ON d.id p.dynasty_id GROUP BY d.id, d.name ORDER BY poem_count DESC;EXPLAIN 是排查查询性能的第一工具。看输出里的 key 列如果显示 idx_dynasty_id 说明 JOIN 字段的索引被用上了如果显示 NULL说明关联字段上没索引或者优化器选择了全表扫描。type 列里的 ref 或 range 也是好信号ALL 则意味着全表。常见做法是拿实际查询语句过一遍 EXPLAIN确认没有异常后再想优化的事。5. 避坑与常见问题排查乱码、主键冲突、导入超时这章把导入和后续使用中最高频的几个故障列出来每条都按现象、原因、解决三步写。大部分问题不在这份库里而在导入方式或服务器配置上。5.1 现象导入后中文全部变成问号或乱码原因几乎可以锁定在字符集链路断裂。SQL 文件是 UTF-8 编码但数据库建库时用了 latin1或者客户端连接层没有指定 utf8mb4导入过程中编码信息丢失。解决方法是先看数据库实际字符集SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME shici;如果结果是 latin1直接重建数据库先 DROP DATABASE 再按第3章的语句重新建库并导入。已经导坏的数据没必要清洗重来比补救快。5.2 现象重复导入时报主键冲突或数据翻倍原因是对同一条 SQL 文件执行了两次导入而第一次导入没有提示错误。如果 SQL 文件里没有 DROP TABLE IF EXISTS 语句第二次导入就会因为主键重复而中断。解决方法是导入前先手动清空四张表SET FOREIGN_KEY_CHECKS 0; TRUNCATE TABLE famous_line; TRUNCATE TABLE poem; TRUNCATE TABLE poet; TRUNCATE TABLE dynasty; SET FOREIGN_KEY_CHECKS 1;TRUNCATE 会重置自增主键计数之后再导入就不会撞键。SET FOREIGN_KEY_CHECKS 在表间有外键约束时避免因清表顺序报错最后一定要改回 1。5.3 现象导入报错 Got a packet bigger than max_allowed_packet bytes原因SQL 文件中一条 INSERT 语句插入了多行数据数据包超过了服务器允许的最大包大小。MySQL 默认 max_allowed_packet 常见值是 4MB 或 16MB诗词这类带长文本的数据很容易触顶。解决方法是临时调大会话级别的参数SET GLOBAL max_allowed_packet 67108864;64MB 对这份库足够。注意这只对之后的新连接生效已经打开的 MySQL 连接需要重连一次。如果用的是云数据库这个参数通常在控制台参数组里修改需要重启实例才生效。5.4 现象Windows 下用 PowerShell 重定向导入中文字符偏移原因PowerShell 的默认输出编码不是 UTF-8直接用重定向会把文件内容按系统默认编码重新编码后发送导致中文错乱。解决方法是不要用 PowerShell 执行重定向导入改用 cmd 命令行或者直接在 navicat 等图形化工具中运行 SQL 文件。如果必须用 PowerShell先执行chcp 65001切换代码页到 UTF-8 再执行 mysql 命令。5.5 现象诗人表与朝代表关联数据错位原因导入顺序问题。如果 dynasty 表还没导入完成poet 表的 dynasty_id 关联的就是空值或错位值。解决方法是严格按照 SQL 文件中的建表顺序导入。这份资源的 SQL 文件在文件头部按 dynasty → poet → poem → famous_line 的顺序建表和插入千万不要为了跳过报错而手动改表顺序。导入完成后抽查一条数据验证SELECT a.name AS poet_name, d.name AS dynasty_name FROM poet a JOIN dynasty d ON a.dynasty_id d.id WHERE a.name 苏轼;结果应该正确显示苏轼和其所属朝代。查出来是 NULL 就回到 5.5 的排查逻辑清空后按顺序重导。6. 进阶用法视图、随机取诗与全文检索落地数据表本身提供的是最底层能力日常使用中我更推荐在此基础上做三个小改造建一个合并视图、实现随机取诗、给名句表加全文索引。这三个改造都不动原始数据却能明显提升使用体验。6.1 建一个诗词完整视图CREATE OR REPLACE VIEW v_poem_full AS SELECT p.id AS poem_id, p.title, p.type, p.content, a.name AS poet_name, d.name AS dynasty_name FROM poem p JOIN poet a ON p.poet_id a.id JOIN dynasty d ON p.dynasty_id d.id;之后查询只需要SELECT * FROM v_poem_full WHERE dynasty_name 唐不用每次写三表 JOIN。视图本质是保存好的查询逻辑MySQL 会在查询时动态执行不占额外存储空间。6.2 随机取一首诗SELECT * FROM v_poem_full ORDER BY RAND() LIMIT 1;ORDER BY RAND() 在十几万行上执行会有性能损耗但每日一次或者低频调用时完全够用。如果要做高频接口建议先SELECT id FROM poem ORDER BY RAND() LIMIT 1拿到随机ID再回表取一行两段式写法能把临时表的压力降到最低。6.3 给名句表加全文索引ALTER TABLE famous_line ADD FULLTEXT INDEX ft_line_content (content); SELECT poem_id, content FROM famous_line WHERE MATCH(content) AGAINST(明月 IN NATURAL LANGUAGE MODE) LIMIT 10;全文索引让中文关键词搜索摆脱 LIKE %明月% 的前缀限制查询效果等同于包含关系性能也更稳定。注意 fulltext 索引不支持停用词表自定义查“之”“乎”这类单字时会自动忽略。从那以后我每次导入这份库都会先执行一遍 SHOW CREATE TABLE 确认索引和字符集没有在传输过程被改动再跑一次 COUNT 校验行数这套固定流程已经成了我的习惯。希望帮到你。本文还有配套的精品资源点击获取