从零构建中华古诗词数据库：表结构设计、SQL优化与向量检索实战

简介：数据库设计是数据管理的核心基础，而关系型数据库的建模与查询优化直接决定了应用系统的性能与扩展性。从实体关系模型到范式化拆分，再到索引策略与SQL执行计划，每一步都影响着数据存储与检索的效率。在实际工程中，无论是处理CSV导入、解决中文乱码，还是通过连接池提升并发能力，这些技术都构成了数据库运维与开发的必备技能。随着AI时代到来，向量数据库与RAG技术的引入，更让传统文本数据具备了语义检索能力，为知识图谱与智能问答提供了新的可能。本文以中华古诗词数据库的完整构建过程为例，从表结构设计、数据清洗，到SQL实战、全文索引、性能优化，再到向量化检索扩展，系统展示了一条从关系型数据库到现代AI检索的实践路径。 做中华古诗词数据库这个念头，在我脑子里转了大半年才真正落地。原因不复杂：项目本身看着不大，但真要动手，牵扯出来的东西一串接一串——数据从哪来、怎么清洗、表怎么建、索引怎么设、几万条诗词怎么快速查、数据怎么备份同步，甚至现在热门的向量检索、RAG 都能往这个题目上挂。如果你正在找数据库课程设计题目，或者想系统练一遍 SQL，再或者只是想把几千首诗词整理成随时能查的资料库，这个项目都挺合适。

我做完这套东西之后最大的感受是：古诗词数据太适合拿来练数据库了。它有明确的结构（诗人、朝代、诗体、内容），有足够大的数据量（全唐诗加全宋词轻松破十万条），有复杂的查询需求（按作者、按朝代、按内容模糊查、按名句查），还能折腾全文索引、并发控制、甚至向量化检索。这篇文章把我从零到一实现的全过程拆开来讲，包括表结构设计、环境选型、SQL 实战、性能优化、常见坑和面试延伸，希望能给你省点弯路。

1. 内容整体设计与思路拆解

1.1 古诗词数据到底适合怎么做成数据库

先想清楚一个问题：古诗词数据不是普通的结构化数据，它比“用户表+订单表”要复杂一截。一首诗里既有诗人信息、朝代信息，又有正文、题目、注释、赏析、名句标注，还会涉及多对多关系（比如一首诗可以同时归入“送别诗”“边塞诗”两个标签）。所以在动手建表之前，最该做的是先跑一遍数据建模。

我最初想偷懒，直接建一张大宽表，把所有字段都塞进去。后来发现几个问题：诗人重复出现，每次都要重复写一遍诗人生卒年、字号；同一首诗关联多个标签时，标签字段只能拼字符串，查起来非常难受；后面想统计“某个朝代的诗人数量”，宽表根本不好写 SQL。所以最后还是回归了经典的三范式设计，把诗人、诗歌、标签、名句拆成多张表。

这种设计的好处，等你真正做课程设计答辩或者给同事演示的时候就体会到了。老师或者领导最喜欢问的问题就是：你为什么这么拆表？多对多关系怎么处理？统计类查询怎么写？三范式拆完之后，这些问题用一条 SQL 就能说清楚。

核心思路：用“诗人表 + 诗词表 + 标签表 + 诗词-标签关联表”这种经典模型，先保证数据不冗余、扩展性够，再考虑查询性能。对古诗词这个业务场景来说，范式化设计带来的好处远大于它带来的 JOIN 成本。

1.2 项目要覆盖哪些核心功能

我给自己定的目标是做一个“能拿得出手”的古诗词查询系统，功能上覆盖了数据库课程设计爱考的几大块：

• 诗词增删改查：按题目、作者、朝代、内容检索，这是最基本的“增删改查”能力。
• 多表关联查询：查某位诗人的全部作品，同时关联出朝代、标签信息。
• 统计聚合：按朝代统计诗词数量、按诗人统计作品数量 TOP10。
• 全文检索：支持按关键词搜索诗句内容，比如输入“明月”能快速找出所有含“明月”的诗句。
• 视图与存储过程：把常用的复杂查询封装成视图，把批量统计逻辑封装成存储过程。
• 并发与事务：模拟多个用户同时收藏、点赞同一首诗的场景，验证数据一致性。
• 数据备份与同步：演示 mysqldump 备份、binlog 同步、或者用 DataX 同步到另一个库。

你会发现，这些功能没有一个需要高深算法，但每一个都能把数据库课程里的核心知识点串起来。我在后面几节里会逐个展开讲怎么做、为什么这么做。

2. 核心细节解析与实操要点

2.1 表结构设计：不要一上来就写 CREATE TABLE

很多初学者拿到数据就开始建表，我建议先画一份数据字典，把字段名、类型、约束定下来再动手。我最终设计是四张核心表加一张关联表：

• poets（诗人表）：poet_id 主键、name、dynasty（朝代）、birth_year、death_year、hometown、intro。
• poems（诗词表）：poem_id 主键、poet_id 外键、title、content、category（诗/词/曲）、created_time。
• tags（标签表）：tag_id 主键、tag_name，预置“送别、边塞、咏物、抒情、写景”等。
• poem_tags（诗词标签关联表）：poem_id、tag_id 联合主键。
• famous_lines（名句表）：line_id 主键、poem_id 外键、content、source，用于存“床前明月光”这类经典名句，方便后面单独检索和展示。

字段类型上有个细节要提醒：诗词正文往往不短，别用 VARCHAR(255)，建议直接上 TEXT/MEDIUMTEXT。朝代字段我一开始用 VARCHAR(20) 存“唐”“宋”“元”，后来发现统计时有人会录入“唐代”“宋朝”这种带后缀的写法，导致 GROUP BY 结果乱七八糟，所以后来干脆改成了代码表，统一约定存“唐”“宋”“元”“明”“清”，导入时做一层映射清洗。

注意：主键不要用自增 INT 以外的东西吗？不一定。这个项目我用的是自增 INT，纯因为简单。但如果你后面要同步数据到其他库、或者做数据合并，建议用雪花算法生成的 BIGINT 或直接上 UUID。我在后续“数据库同步”这一节会讲到为什么。

2.2 数据从哪来、怎么清洗入库

古诗词数据网上很多，但格式普遍很乱。有 JSON 的、有 Markdown 表格的、有纯文本的。我在网上找了一份全唐诗 + 全宋词的 CSV，大概几万行，结果打开一看：有的字段没转义，有的诗歌正文里带换行，有的作者名字前后有空格，甚至还有“作者：佚名”这种写在标题里的脏数据。所以清洗是必经之路。

我的清洗流程是这样：

1. 先用 Python 的 pandas 读入 CSV，指定 encoding='utf-8'，当时遇到某些文件是 GBK 编码，直接读会报错，要逐个试编码。
2. 去除全角空格和首尾空白。
3. 统一朝代字段，做映射清洗。
4. 去掉明显的重复数据，比如同一首诗在不同文件中出现多次，用“标题 + 作者 + 正文前50字”做去重逻辑。
5. 清洗完的数据转成 DataFrame 再批量写入 MySQL。

写入方式我强烈建议用参数化 SQL + 批量 execute，不要一条一条 insert。一万条数据逐条 insert 可能要几十秒，批量 execute 只需要一两秒。而且参数化 SQL 可以防止引号、换行符把语句搞坏，也能防注入，一举两得。

2.3 导入 Excel 场景：工具选择与实操

热搜词里有“excel 导入数据库”，这其实是很多运营同事和课程设计同学的真实需求。我提供两种方式：

• 用 Navicat 的导入向导：选择 Excel 文件、指定映射字段、跑一遍导入。优点是快，缺点是处理复杂清洗逻辑很弱。
• 用 Python pandas：read_excel 读入，清洗后再入库。推荐这种方式，尤其当数据需要做朝代映射、去重或字段拆分的时候。

我当时清洗完生成的标准 CSV 大概长这样：

poet_name,dynasty,title,content 李白,唐,静夜思,床前明月光，疑是地上霜。举头望明月，低头思故乡。 杜甫,唐,春望,国破山河在，城春草木深。感时花溅泪，恨别鸟惊心。

入库之后再用 SQL 验证一下总行数和抽样数据，确保没丢数据。

3. 实操过程与核心环节实现

3.1 建库建表：把设计落成 SQL

清洗完数据后，就要正式开始建库建表了。我用的是 MySQL 8.0，字符集直接指定 utf8mb4。为什么不用 utf8？因为古诗词排版偶尔会出现特殊字符、生僻字，utf8mb4 才能完整兼容，不然插入某个生僻字直接报错，很烦。

建表 SQL 核心部分长这样：

CREATE DATABASE IF NOT EXISTS poetry_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE poetry_db; CREATE TABLE poets ( poet_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, dynasty VARCHAR(20) NOT NULL, birth_year INT, death_year INT, hometown VARCHAR(100), intro TEXT, KEY idx_dynasty (dynasty), KEY idx_name (name) ) ENGINE=InnoDB; CREATE TABLE poems ( poem_id INT PRIMARY KEY AUTO_INCREMENT, poet_id INT NOT NULL, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, category VARCHAR(20) DEFAULT '诗', created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FULLTEXT KEY ft_content (content) WITH PARSER ngram, CONSTRAINT fk_poet FOREIGN KEY (poet_id) REFERENCES poets(poet_id) ON DELETE CASCADE ) ENGINE=InnoDB;

这里有两个细节值得你抄作业：

• 诗词正文的全文索引我用的是 ngram 解析器，因为默认全文索引按空格分词，中文根本不好使。MySQL 8.0 自带 ngram 插件，直接用就行。
• 外键加 ON DELETE CASCADE，这样删除某位诗人时，他的诗词也会被自动删掉，避免产生孤儿数据。有人可能会说生产环境不用外键，但对课程设计和中小项目来说，外键能省很多事。

3.2 核心查询：从简单 SELECT 到 JOIN 再到统计

数据导进去之后，最有意思的部分来了：写各种查询。我按照从简到难的顺序列几个经典查询。

最基本的是单体查询，比如“查李白的所有诗”：

SELECT title, content FROM poems WHERE poet_id = 1;

更常用的是按作者名，而不是按作者 ID 查，这时候就要 JOIN 了：

SELECT p.title, p.content FROM poems p JOIN poets a ON p.poet_id = a.poet_id WHERE a.name = '李白';

统计类查询是课程设计里最爱出的题，比如“统计每个朝代的诗词数量”：

SELECT a.dynasty, COUNT(*) AS poem_count FROM poems p JOIN poets a ON p.poet_id = a.poet_id GROUP BY a.dynasty ORDER BY poem_count DESC;

再比如“作品数量最多的前十位诗人”：

SELECT a.name, COUNT(p.poem_id) AS cnt FROM poets a LEFT JOIN poems p ON a.poet_id = p.poet_id GROUP BY a.poet_id ORDER BY cnt DESC LIMIT 10;

全文检索是个亮点功能。比如搜“含明月的诗句”，不能用 LIKE '%明月%'，几万行数据下 LIKE 会全表扫描，慢得明显。正确姿势是走 FULLTEXT 索引：

SELECT title, content FROM poems WHERE MATCH(content) AGAINST('明月' IN NATURAL LANGUAGE MODE);

实测下来，同样的关键词，LIKE 查询耗时从几百毫秒到一秒以上，全文索引基本在几十毫秒内返回。差距在地量级上体现得很清楚。

补充一个细节：如果你用 MariaDB 或者 MySQL 5.7 及以下版本，ngram 可能需要额外配置，MySQL 8.0 直接原生支持，这也是我推荐 8.0 的一个原因。

3.3 视图、存储过程与触发器：把“课程设计味”拉满

很多课程设计题目会明确要求用到视图、存储过程、触发器。它们在实际业务里当然有争议，但作为学习项目它们是很好的练习载体，而且能帮你把分拿稳。

视图：把“诗词 + 作者 + 朝代”这种高频 JOIN 封装成视图，查询时直接 SELECT 视图，代码清爽很多。

CREATE OR REPLACE VIEW v_poem_info AS SELECT p.poem_id, p.title, p.content, a.name AS poet_name, a.dynasty FROM poems p JOIN poets a ON p.poet_id = a.poet_id;

存储过程：比如做一个按朝代统计的存储过程，带输入参数：

DELIMITER // CREATE PROCEDURE sp_count_by_dynasty(IN dy VARCHAR(20)) BEGIN SELECT a.name, COUNT(*) AS cnt FROM poets a JOIN poems p ON a.poet_id = p.poet_id WHERE a.dynasty = dy GROUP BY a.poet_id ORDER BY cnt DESC; END // DELIMITER ;

触发器：比如给诗词表加一个点赞数字段，用触发器自动更新统计表。这种写法虽然不是必需的，但能演示你对数据库“完整性控制”的理解。

3.4 同步工具与备份策略

数据做出来之后，千万记得备份。热搜词里有“数据库同步软件”“数据库同步工具”，说明这块也是大家关注的实操点。我实际用的方案：

• 定时逻辑备份：mysqldump 每天晚上跑一次。命令很简单：mysqldump -u root -p poetry_db > poetry_db_backup.sql。恢复时用 mysql -u root -p poetry_db < poetry_db_backup.sql 即可。
• 主从或 binlog 同步：如果想实现准实时同步，可以开启 binlog，配置主从复制。对古诗词项目来说有点重，但如果你要跟面试官聊“数据库高可用”，这个知识点绕不开。
• 异构同步：如果你想把 MySQL 里的数据同步到达梦、人大金仓、PostgreSQL，可以用 DataX 或 kettle。DataX 是阿里开源的离线同步工具，配置一个 json 文件把 reader 和 writer 指定好就能跑。我在下面拿“从 MySQL 导出到达梦”举例，思路是通用的。

为什么大家关心这类需求？因为现在很多高校和单位要求信创环境，数据要从传统数据库往达梦、人大金仓这类国产数据库迁移，迁移过程中还得保证表和索引结构不丢。我建议有兴趣的读者可以拿古诗词这样的小数据量项目练手，比拿生产数据试错成本低太多。

4. 常见问题与排查技巧实录

4.1 中文乱码与生僻字问题

这是我遇到的第一道坎。导入 CSV 时中文全部变成“???”，原因就是连接字符串没指定 utf8mb4。用 Python 连接 MySQL 时 pymysql 要写成：

conn = pymysql.connect( host='localhost', user='root', password='123456', database='poetry_db', charset='utf8mb4' )

建表字符集也要是 utf8mb4。这两处都对上后，生僻字比如“龘”“爨”都能正常入库。另外注意如果表已经建好才想起来改字符集，可以用 ALTER TABLE 修改：

ALTER TABLE poems CONVERT TO CHARACTER SET utf8mb4;

4.2 批量插入太慢，怎么提速

如果你还是逐条 insert 而且不在事务里，一万条数据可能要几分钟。我做了三个优化：

• 使用 executemany 批量执行。
• 把多条 insert 放进一个事务里，最后统一 commit。
• 如果追求极致速度，可以临时关闭唯一校验和索引更新，导入完再重新开启，但这对有外键的项目不太友好，普通场景没必要。

建议控制在每次批次 500~2000 条，实测这是性能和内存的平衡点。

4.3 并发锁与死锁排查

这个项目我用来模拟过“多人同时点赞同一首诗”的场景，结果真就复现了死锁。原因很简单：两个事务分别对两首不同的诗加锁，然后又互相请求对方持有的锁，形成环路。

真实发生时的错误信息是 Deadlock found when trying to get lock，靠印象背是没用的，得会查。我的排查流程：

1. 用 SHOW ENGINE INNODB STATUS; 查看最近一次死锁信息，里面会明确显示两个事务持有哪些锁、在等哪些锁。
2. 看事务里 SQL 的执行顺序，大部分死锁的根源是加锁顺序不一致。
3. 修复思路：统一事务内的加锁顺序，比如都按 poem_id 升序处理；另一个思路是降低隔离级别，但如果事务本身逻辑要求较高，不建议为了躲死锁就盲目降级别。

下面这张表是我整理的死锁信息解读速查表：

查看命令	能看到什么	有什么用
SHOW ENGINE INNODB STATUS	最近一次死锁的事务和锁信息	定位死锁发生的事务与持锁顺序
information_schema.innodb_trx	当前所有未结束的事务	查看是否有长事务持有锁不释放
SHOW PROCESSLIST	当前连接的执行状态	排查哪个会话卡住了
EXPLAIN SELECT ... FOR UPDATE	行锁命中的索引情况	判断是否因无索引导致锁范围扩大

4.4 数据库连接池配置：别在代码里反复 new Connection

初期我用 Python 写脚本，每次查询都新建连接，跑一个统计脚本能慢到怀疑人生。后来意识到是因为每建一次连接都要经过 TCP 握手、认证、分配资源，开销非常大。解决办法是用连接池。

如果你是 Java 后端，用 HikariCP 或者 Druid；如果是 Python，用 SQLAlchemy 的连接池；如果你在用 Kettle 做数据同步，Kettle 的数据库连接里面也能配置连接池参数。连接池的核心参数有四个：

• initialSize：初始连接数。
• maxActive：最大活动连接数。
• maxWait：获取连接的最大等待时间。
• minIdle：最小空闲连接数。

以 HikariCP 为例，我一般这样配：

spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000

对于古诗词这种量级的项目，连接池其实开 10~20 就够了，开太大反而浪费数据库资源。

4.5 面试题角度：从古诗词数据库延伸出去

我把这个项目给学弟学妹讲的时候，经常会顺带帮他们把面试题串一遍。古诗词数据库这个项目能直接回答的面试题包括但不限于：

• 为什么 InnoDB 用 B+ 树做索引而不是 B 树？用诗词表的全文索引和主键索引解释，B+ 树的叶节点形成双向链表，范围查询效率高，而这个项目里大量“按朝代查全部”“按诗人查全部”就是范围查询。
• 为什么主键用自增 INT 而不是 UUID？因为 InnoDB 聚簇索引按主键顺序排列，自增主键插入时是顺序追加，UUID 是随机写入，会导致页分裂和碎片增加。但数据量一旦大到需要分布式合并，又得考虑雪花 ID。
• 索引什么时候会失效？比如在索引列上用了函数、隐式类型转换、LIKE 前置百分号。
• 三范式与反范式的权衡？古诗词项目我选了范式化，但如果是需要极高并发读的场景，可能要把诗词信息和作者冗余到一张大宽表里，用空间换时间。
• 怎么做 SQL 优化？先把慢查询日志打开，再 EXPLAIN 看执行计划，优先优化扫描行数和 extra 里的 using filesort。

备考计算机三级数据库的时候，这个项目也可以当作活案例来复习。三级数据库考的事务、并发控制、范式、ER 图，在这个项目里全都有对应物：事务就是“批量导入保证数据一致性”，并发控制就是“点赞/收藏的锁问题”，范式就是“为什么拆四张表”，ER 图就是那几张表的关系。

5. 扩展思路：从关系数据库走向向量数据库和 RAG

最后展开说一下这个项目可以继续延伸的新方向。热搜词里频繁出现“向量数据库”“RAG、知识图谱与向量数据库”，这确实是当前数据库生态里最热的几个方向。古诗词数据本身很适合做向量化：

把每首诗的 content 通过 embedding 模型转成向量，存到 Milvus、Chroma 或者 Elasticsearch 的 dense vector 字段里，然后就能实现“以诗找诗”：输入“举头望明月”，向量检索能找出语义相近的“月下飞天镜，云生结海楼”，而不是只靠关键词匹配。

实操上最大难度不在工具，而在数据清洗和切片策略。古诗词篇幅差异很大，五言绝句就 20 个字，排律却能几百字，直接整首诗 embedding 效果会不稳定。我试过的方案是：对于长诗先拆成“联句”级别的小片段，每联一句向量化，诗歌标题和朝代作为元数据一起存。效果比整首 embedding 更精准。

而且这个思路还能和 RAG 串起来：做一个“古诗词问答机器人”，先用向量数据库做召回，再拿召回结果去大模型那边做生成。整个链路用 Python 写，几百行代码就能跑通。我强烈推荐学数据库的同学试试这个方向，理由很简单：它把传统 SQL 能力和现代 AI 技术结合在了一起，做出来既有工程感又新潮。

另外一个可选方向是知识图谱。把诗人之间的师徒关系、同游关系、唱和关系整理成图谱，存到 Neo4j 里，就能查“李白的朋友圈有哪些人”“杜甫和李白认识吗”。这个方向我对接课程设计和面试都很有用，展示出来绝对惊艳。

说了这么多，这个项目从最基础的建表到向量检索，全链路我都走了一遍。我个人在实际操作中最深的体会是：做个古诗词数据库，收获最大的不是写会了多少条 SQL，而是你对“选型—建模—实现—优化”这个完整链路有了体感。下次让我从 MySQL 换到 PostgreSQL，或者换到达梦、人大金仓，我至少知道先看什么、后看什么、踩坑点在哪里。

如果你正在为数据库课程设计找方向，或者想在简历上写一个“既能聊 SQL 又能聊新趋势”的项目，中华古诗词数据库确实是个性价比很高的选择。数据安全、易得、方向多，往上走的路径也清晰。从一张诗词表开始，你永远不知道最终它会变成一个查询系统，还是一个向量检索服务，又或者是一个诗词知识图谱。动手吧，这个坑值得踩。

本文还有配套的精品资源，点击获取