数据库-唐诗三百首数据集：建模清洗导入与查询实践

简介《唐诗三百首》结构化数据集共收录320条唐诗记录面向古诗爱好者、数据库初学者及数据分析师等不同人群。数据内容涵盖诗题、作者与正文结构清晰规范既适合完成建表、插入、查询、索引优化等数据库基础操作训练也可作为中文分词、情感分析、诗人风格比对等文本挖掘任务的素材还可直接用于诗词学习网站、小程序或前端展示项目的数据支撑。压缩包内共含4个独立文件整体压缩后大小约141KB体量轻巧易于传输与携带文件类型覆盖sql、json、csv、xlsx四种sql脚本可一键导入MySQL等关系型数据库json与csv方便Python、Java等语言进行读取和二次加工xlsx表格则便于在Excel中浏览、筛选与人工标注。目前已有1449人下载学习尤其适合作为课程设计、毕业设计、数据可视化实践或API演示的现成数据集帮助使用者免去从零整理古诗数据的繁琐过程集中精力完成业务逻辑与界面开发。1. 数据库-唐诗三百首数据集为什么把古诗装进数据库比读文本文件更值得做中文 NLP、搞课程设计、或者想快速搭一个诗词鉴赏小应用的人迟早会遇到同一个问题网上下载的《唐诗三百首》大多是 TXT 或 Word格式五花八门有的带注释有的只有诗名和作者有的连标点都是全角半角混着来。直接拿去训练模型或者写查询接口光是清洗就能耗掉一整天。我见过不少人最后选择手工建一张表把三百首抄进去抄到一半就放弃了。真正靠谱的路径是把这份数据当成一个“数据库-唐诗三百首数据集”来构建先建模再清洗最后导入成结构化记录。这套流程做完你手里就不只是一堆文字而是一套能支撑增删改查、全文检索、甚至诗词接龙接口的资产。这篇文章适合三类人要交数据库课程设计的学生、正在做中文文本处理的项目开发者以及想让古诗数据复用的内容创作者。2. 从原文到数据模型设计唐诗三百首核心表与字段边界2.1 拆解一首诗作者、体裁、诗句、注释的实体关系拿到原始文本后第一件事不是急着写代码而是想清楚一首诗由哪些信息构成。以最常见的《唐诗三百首》版本为例一首诗至少包含标题、作者、正文若干句、体裁五言绝句、七言律诗等。如果原始文本里带有注释、赏析那还要考虑注释的归属。如果把所有内容塞进一张宽表比如“id, title, author, content, comment”用起来会非常痛苦。原因很简单查询“某个作者写过哪些诗”需要拆 content 字符串“统计所有五言绝句的平均字数”也得靠正则。更合理的做法是把实体拆开作者单独一张表诗单独一张表诗句拆分独立存储注释关联到具体的诗句或诗篇。我建议的最小实体关系是作者表author存作者姓名、字号、生卒年诗词表poem存诗题、体裁、创作背景诗句表verse存每一句的序号、原文、平仄标注如果后续要做格律分析。注释表可以后续按需加初期不一定建。这样的模型既能支持常见的数据库增删改查也为后续做全文检索和数据分析留出了余地。2.2 表结构设计诗词表、作者表、诗句表的字段与索引下面是我在 MySQL 里常用的建表语句这套结构在达梦、Oracle 上稍作类型替换也能用。先建作者表CREATE TABLE author ( author_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 作者ID, name VARCHAR(50) NOT NULL COMMENT 作者姓名, courtesy VARCHAR(50) DEFAULT NULL COMMENT 字号, birth_year VARCHAR(20) DEFAULT NULL COMMENT 生年模糊可用空值, death_year VARCHAR(20) DEFAULT NULL COMMENT 卒年 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT作者表;再建诗词表CREATE TABLE poem ( poem_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 诗词ID, title VARCHAR(100) NOT NULL COMMENT 诗题, author_id INT NOT NULL COMMENT 作者ID关联author, genre VARCHAR(20) DEFAULT NULL COMMENT 体裁如五言绝句, preface TEXT COMMENT 诗序或背景, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT诗词表;最后是诗句表CREATE TABLE verse ( verse_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 诗句ID, poem_id INT NOT NULL COMMENT 所属诗ID, line_no INT NOT NULL COMMENT 句序从1开始, content VARCHAR(200) NOT NULL COMMENT 诗句原文, INDEX idx_poem_line (poem_id, line_no), INDEX idx_content_prefix (content(10)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT诗句表;三个表都用了utf8mb4目的是覆盖生僻字和特殊符号。索引方面verse表的idx_poem_line是组合索引用于快速取出某一首诗的全部诗句idx_content_prefix是前缀索引适合LIKE 举头%这类查询但不适合直接替代全文索引。字段边界要注意birth_year我特意用 VARCHAR 而不是 INT因为很多作者的生卒年是模糊表述比如“约公元701年”存整数会丢信息。这是数据建模时最容易被忽略的细节。2.3 字符集与排序规则中文数据集的第一个坑很多人在导入中文数据集时遇到Incorrect string value报错十有八九是建表时没指定字符集或者库级默认是latin1。在 MySQL 中排序规则要看清楚utf8mb4_general_ci和utf8mb4_unicode_ci都能处理中文但unicode_ci对多语言的排序更准确代价是性能略低。唐诗数据量很小三百首而已根本不用纠结性能直接用utf8mb4_unicode_ci最稳。在达梦数据库中字符集要在初始化实例时选UTF-8建表时用VARCHAR2类型时要注意字节长度和字符长度的换算。默认情况下VARCHAR2(50)是按字节算的一个中文字符占 2 到 3 字节写 50 可能只能存十几个汉字。解决方法是建表时显式指定VARCHAR2(50 CHAR)。Oracle 同样有这个习惯很多从 MySQL 转过来的同学在这里翻车。3. 构建数据集用 Python 把纯文本清洗成结构化 SQL3.1 原始文本的常见形态与预处理思路网上流传的《唐诗三百首》文本主要有三种形态第一种是纯诗文本每首诗以“题名作者”开头后面跟正文第二种是带注释的版本诗句之间穿插注释第三种是带拼音或译文的版本。最省事的是第一种但即便如此换行、空行、缩进也不统一。我先说通用思路不要指望一个正则吃遍所有版本。拿到文本后先做三件事——统一换行符、去全角空格、规范标点。下面这段预处理代码能处理大部分情况import re def clean_text(raw: str) - list[str]: # 把 \r\n 和 \r 统一成 \n raw raw.replace(\r\n, \n).replace(\r, \n) # 去掉行首行尾的空格、全角空格 lines [line.strip().replace(\u3000, ) for line in raw.split(\n)] # 删除空行和只含标点的行 lines [line for line in lines if line and not re.fullmatch(r[。、\s]*, line)] return lines这段代码的作用是清洗出有效行。注意replace(\u3000, )是把全角空格去掉很多文本是从网页复制的全角空格是隐形杀手。删除空行和标点行是为了避免把空行当正文。3.2 解析标题、作者、正文的正则与状态机干净的文本行到手后下一步需要识别哪一行是标题哪一行是作者正文从哪里开始。常见的格式是“静夜思 李白”或者“静夜思李白”中间用空格、冒号或制表符分隔。我见过更复杂的情况诗题里有冒号比如“秋日登吴公台上寺远眺寺即陈将吴明彻战场”这种就不能直接 split。优先用正则处理# 匹配 标题 作者标题可能带标点作者一般是2-4个汉字 POEM_HEAD_RE re.compile(r^(?Ptitle.{1,30}?)[\s:\t](?Pauthor[\u4e00-\u9fa5]{2,4})$) def split_head(line: str): m POEM_HEAD_RE.match(line) if m: return m.group(title), m.group(author) return None这里用非贪婪.{1,30}?匹配标题避免把作者也吞进标题。但正则不是万能的有些文本将标题和作者分成两行例如静夜思 李白 床前明月光...此时需要换一种方式先检测行是否以常见姓氏开头李、杜、王、孟等再结合下一行判断。我一般用状态机维护一个current_poem变量遇到疑似标题行就启动新诗遇到作者行就填充 author遇到正文行就追加到诗句列表。代码结构如下def parse_poems(lines: list[str]) - list[dict]: poems [] cur None for line in lines: head split_head(line) if head: if cur and cur[lines]: poems.append(cur) cur {title: head[0], author: head[1], lines: []} continue # 如果当前没有诗跳过 if cur is None: continue # 如果行是2-4个汉字且当前诗还没有任何诗句认为是作者行 if not cur[lines] and re.fullmatch(r[\u4e00-\u9fa5]{2,4}, line): cur[author] line continue # 否则当诗句 cur[lines].append(line) if cur and cur[lines]: poems.append(cur) return poems这段逻辑不依赖固定文件名而是按内容特征区分。为什么要判断“当前诗还没有任何诗句”因为有些版本作者写在诗题下、正文前如果不加这个判断会把“李白”两个字的作者行当成诗句存进去。状态机虽然比正则多几行但对格式变化的容忍度高很多。3.3 生成SQL脚本与CSV文件一个可复用的落地脚本解析后的poems列表要变成能导入数据库的东西。我习惯同时生成两个东西一个是poems.sql一个是poems.csv。SQL 用于直接导入CSV 用于可视化检查和后续用 Python 做进一步处理。生成 SQL 时要注意转义单引号和反斜杠。下面脚本会输出INSERT语句并补齐关联 IDdef to_sql(poems: list[dict]) - str: author_map {} next_author_id 1 next_poem_id 1 sql_lines [] for p in poems: author p[author] if author not in author_map: author_map[author] next_author_id sql_lines.append( fINSERT INTO author (author_id, name) VALUES ({next_author_id}, {author}); ) next_author_id 1 aid author_map[author] sql_lines.append( fINSERT INTO poem (poem_id, title, author_id) VALUES ({next_poem_id}, {p[title]}, {aid}); ) for idx, line in enumerate(p[lines], start1): escaped line.replace(, ).replace(\\, \\\\) sql_lines.append( fINSERT INTO verse (verse_id, poem_id, line_no, content) fVALUES ({next_poem_id * 100 idx}, {next_poem_id}, {idx}, {escaped}); ) next_poem_id 1 return \n.join(sql_lines)这里的verse_id用poem_id * 100 idx生成是为了避免额外维护自增序列。如果一首诗超过 99 句这个算法会撞 ID但三百首里最长的诗也不会超过 100 句实际够用。更稳妥是把verse_id也设成自增但生成 SQL 时就不好控制关联了所以用简单方案。生成 SQL 后先导入空表再根据poem_id关联查询不要试图一条语句插入全部。参数说明author_map的内存字典负责去重因为《唐诗三百首》里李白的诗很多同一作者只插入一次。aid是动态分配的所以诗表需要先拿到作者 ID 再插入顺序不能乱。4. 导入数据库MySQL、达梦、Oracle下的三种实践与连接池注意点4.1 MySQL导入与连接池参数调整拿到poems.sql之后导入 MySQL 最简单的方式是命令行mysql -u root -p --default-character-setutf8mb4 tangshi poems.sql这里必须指定--default-character-setutf8mb4。如果漏了客户端默认字符集可能是latin1中文会变成乱码。导入后立刻验证SELECT COUNT(*) FROM poem; SELECT * FROM verse WHERE poem_id 1;如果项目要用 Java 或 Python 操作这批数据连接池参数需要调整。常见做法是用 HikariCP 或 Druid但很多人在配置文件里只写了连接串忘了加characterEncodingutf8。对于 MySQL 8 以上的驱动还需要allowPublicKeyRetrievaltrue否则连接时报公钥检索错误。连接池的关键参数是initialSize、maxActive、maxWait。唐诗数据集数据量小不需要开很多连接initialSize2, maxActive5, maxWait3000足够。真正要注意的是空闲连接超时MySQL 默认wait_timeout是 8 小时如果连接池没有validationQuery半夜空闲后第二天第一次查询会抛异常。4.2 达梦数据库与Oracle兼容性处理如果你的目标数据库是达梦或 Oracle生成的 SQL 不能直接跑。差异主要在三个方面自增列的语法、字符串类型、以及分号分隔的批处理方式。MySQL 的AUTO_INCREMENT在达梦里要写成IDENTITY(1,1)或者用序列。Oracle 用SEQUENCE配合触发器。为了不增加复杂度我建议在达梦和 Oracle 中放弃自增直接使用生成好的poem_id和verse_id反正数据一次导入后基本只读。建表语句改成CREATE TABLE poem ( poem_id INT PRIMARY KEY, title VARCHAR2(100 CHAR) NOT NULL, author_id INT NOT NULL, genre VARCHAR2(20 CHAR), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );注意VARCHAR2(100 CHAR)的CHAR关键字不能省。达梦和 Oracle 的VARCHAR2默认按字节100 字节只能存 33 个汉字而唐诗标题超过 20 字的不少很容易溢出报错。导入时不要用SOURCE命令最好用达梦的DM 数据迁移工具或 Oracle 的SQL Loader。我一般是先导成 CSV再用工具导入比直接执行长 SQL 稳定。4.3 验证导入结果增删改查与数据质量检查无论哪个数据库导入后都要做一套系统验证。先查总数SELECT COUNT(*) FROM poem; SELECT COUNT(DISTINCT author_id) FROM poem;《唐诗三百首》的标准数量是 311 首有些版本 320 首作者约 77 人。如果你的统计偏差太大说明解析时把标题行当成了诗句或者把注释也当成了正文。这时要回到poem表看title字段是否干净。再抽查几首熟诗SELECT v.line_no, v.content FROM verse v JOIN poem p ON v.poem_id p.poem_id WHERE p.title 静夜思 ORDER BY v.line_no;检查格式前两句应该是“床前明月光疑是地上霜”。如果顺序乱了大概率是原始文本的换行被合并。还要做一次“反向”检查——查verse.content里有没有包含空格或括号的异常行SELECT content FROM verse WHERE content REGEXP [():];这些异常行往往来自带注释的版本。遇到就直接 UPDATE 人工修正不用重新导入。5. 避坑与排查唐诗数据集最常见的5个翻车现场5.1 现象导入后中文全变成问号或乱码原因有两个一是建表时没指定utf8mb4二是导入命令没带--default-character-set。解决方式修改表字符集可以补救一部分数据但已损坏的数据无法逆转。最干净的办法是把表 DROP 掉重建再用正确的字符集重新导入。另一个隐蔽原因是用 Python 的pymysql写入时连接串少了charsetutf8mb4。Python 驱动默认用utf8mb4吗并不是。pymysql.connect()如果不指定charset默认是utf8mb4的可能是latin1。所以写代码时务必显式传入。5.2 现象诗句断行错乱一首诗被拆成两首原始文本里有些诗题下面还带“序”比如《长恨歌》有一段很长的序。状态机遇到序的时候会把它当成诗句。最终结果一首《长恨歌》可能被解析成多首因为序中恰好有两行看起来像标题作者。解决思路增加一个“序”白名单。凡是当前诗已经有超过 6 行正文再遇到标题特征行不要轻易开新诗。更稳的办法是把包含“并序”或“序曰”的先单独提取出来放到preface字段不走状态机。对三百首而言这种特殊情况不超过 10 处人工检查一遍最省心。5.3 现象作者归并错误李白被拆成两个ID原因很常见一个版本写作“李白”另一个版本写作“李 白”中间有个空格或者全角字符“李⽩”这是 Unicode 中的兼容字符。清洗文本时没有做规范化导入后同一个作者出现多个 ID。解决方式在生成 SQL 之前先对 author 字段做unicodedata.normalize(NFKC, author)这个函数能把全角字符和兼容字符统一成普通汉字。然后做一次GROUP BY author检查SELECT name, COUNT(*) FROM author GROUP BY name HAVING COUNT(*) 1;发现了就直接 UPDATE 合并再清理外键。5.4 现象用 LIKE 查询“举头望明月”很慢三百首的数据量就算全表扫描也就几十毫秒大多数情况不算慢。但如果你用LIKE %明月%查诗句命中次数多时前缀索引无法生效。而且日后接入更多诗词比如全唐诗四万首性能立刻暴露。解决方式启用全文索引。MySQL 5.7 之后支持中文ngram全文解析器建索引时指定即可ALTER TABLE verse ADD FULLTEXT INDEX ft_verse_content (content) WITH PARSER ngram;注意全文索引的ngram分词器默认 token 大小是 2查单字“月”依然不理想。这个后面再说。5.5 现象数据库备份恢复后中文恢复不了用mysqldump备份时没加--default-character-setutf8mb4生成的 dump 文件里中文可能已经是乱码恢复后无论怎么设字符集都没用。所以备份命令和数据导入命令要同样重视字符集mysqldump -u root -p --default-character-setutf8mb4 tangshi tangshi_backup.sql恢复时再带一次同样的参数。这个坑我在做课程设计演示时踩过那时候备份文件中文全损预警里第一条就是它。6. 让数据集发挥价值全文检索与诗词接龙接口的落地细节数据集入库只是开始真正让“数据库-唐诗三百首数据集”体现价值的是上层应用。这里说一个我常用的落地技巧用全文索引做一个“关键字找诗句”接口。MySQL 全文索引加ngram后查询不需要写LIKESELECT p.title, a.name, v.line_no, v.content FROM verse v JOIN poem p ON v.poem_id p.poem_id JOIN author a ON p.author_id a.author_id WHERE MATCH(v.content) AGAINST(明月 IN NATURAL LANGUAGE MODE) LIMIT 20;这个查询会返回包含“明月”的诗句。注意“明月”是两字词正好是 ngram 的最小单元能命中索引。如果只查“月”用全文索引基本无效退回来用LIKE %月%全表扫描反正数据量小。在此基础上扩展“诗词接龙”只需要把上一句最后一个字作为关键词检索下一句首字包含该字的诗。比如接龙的上一句末字是“光”就执行SELECT v2.content FROM verse v2 WHERE v2.content LIKE CONCAT(光, %) LIMIT 1;这种查询不走索引但 300 首诗不到 2000 行毫秒级返回完全没有性能压力。如果你想做得更专业可以把末字按押韵分类用RIGHT(content,1)分组统计韵脚SELECT RIGHT(content, 1) AS rhyme_char, COUNT(*) AS cnt FROM verse GROUP BY rhyme_char ORDER BY cnt DESC LIMIT 20;这种方法能快速统计出唐诗三百首里出现频率最高的韵脚字对做格律分析很有帮助。最后提醒一件事数据集的版本管理。我习惯把清洗脚本和生成的数据一起放进 Git 仓库poems.csv作为只读资产build.py作为可重复执行的脚本。每当拿到一个新版本文本只需要改输入文件路径重新运行脚本就能生成一套新的入库 SQL。这样数据集的更新不再是黑匣子每一条记录都能追溯到原始文本的哪一行。这个习惯帮我省了无数重复劳动。三百首也好将来扩展到四万首全唐诗也罢思路完全一致——先建模再清洗再入库最后用查询把数据盘活。希望帮到你。本文还有配套的精品资源点击获取