企业官网建设流程全解析

简介：这是一份面向古诗词爱好者、语文教师与文史研究者的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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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( host="127.0.0.1", port=3306, user="root", password="yourpass", database="poetry_db", charset="utf8mb4" ) with open("poems.json", encoding="utf-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快一个数量级，几万条数据几秒能跑完；charset="utf8mb4"必须显式指定，否则连接层会用默认字符集，生僻字照样丢。

3.2 用 LOAD DATA 处理百万级数据

数据量上到几十万、上百万条时，Python 逐条插入就慢了。这时候把清洗后的数据导出成制表符分隔的文本，用LOAD DATA LOCAL INFILE导入，速度能快好几倍。

# 先把清洗结果导出为 TSV，字段间用 \t，行内换行用 \n 转义 # 然后登录 mysql 执行： mysql -u root -p poetry_db

LOAD 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_infile=OFF，用SET GLOBAL local_infile=1;打开；生产环境出于安全考虑通常禁用 LOCAL，改用LOAD DATA INFILE把文件放到服务端目录。

注意：LOAD DATA遇到一行字段数不对会整行报错并中断，导入前先用head -n 5检查 TSV 列数是否一致。

4. 古诗词数据库的查询与全文检索：从 LIKE 到 ngram

4.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_genre，JOIN 时p.author_id走idx_author，三个索引都用上了。参数说明：ORDER BY p.id而不是ORDER BY p.title，因为按主键排序能直接用聚簇索引，按标题排序会触发 filesort，数据量大时明显变慢。如果确实要按标题排，考虑给title加索引，但中文排序结果依赖 collation，utf8mb4_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，连接串里加charset=utf8mb4，Python 的 pymysql 要传charset="utf8mb4"，JDBC 连接串加characterEncoding=utf8&connectionCollation=utf8mb4_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 doesn't 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里type=ALL。原因：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(host="127.0.0.1", user="root", password="yourpass", database="poetry_db", charset="utf8mb4") 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几万行，锁表锁到业务报警。从那以后凡是「一对多」的属性，我一律先拆表。另一个习惯是导入前一定先在小批量数据上跑通全流程，确认字符集、去重、全文索引都正常，再上全量——全量导入跑一半失败重来，那种等待是真的难受。希望帮到你。

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