企业官网建设流程全解析

简介：这是一份面向古诗词研究者、语文教育工作者及传统文化爱好者的MySQL数据库资源，将中华古典诗词以结构化SQL形式沉淀，便于导入关系型数据库后进行检索、统计与二次开发。资源包共318个文件，全部为sql脚本，压缩后约53.85MB，按作者与诗体分卷存储，涵盖唐诗、宋词及五言、七言、律诗等多种体裁，可支撑数据导入、查询分析与内容整理等场景。其中唐诗约5.5万首，覆盖初唐至晚唐各期名家；宋词收录约1564位作者、21050首作品，豪放与婉约并存。数据库还附有注释与赏析线索，帮助理解诗词深层意涵。目前已有545人学习下载，适合需要批量整理、研究或分享古诗词资源的读者高效上手。

1. 古诗词库(中文简体)-MySQL：一个能直接落地的中文文本数据工程

很多人第一次做中文文本类项目，卡住的地方不是算法，而是数据怎么存。手上有一份几万首的古诗词，简体中文，带标题、作者、朝代、正文、注释，想做一个能按作者查、按朝代筛、按关键词搜、还能按字数排序的小系统，这时候选 MySQL 还是选文件、选 NoSQL，就成了第一个要拍板的事。古诗词库(中文简体)-MySQL 这个组合，本质上是把结构化的元数据（作者、朝代、体裁）和半结构化的长文本（正文、注释）放进同一个关系型数据库里，用一张主表加若干维表撑起检索、排序、统计三类需求。它适合做诗词类小程序后端、语文教学工具、文本挖掘的语料底座，也适合当成练手 MySQL 建表、索引、字符集、全文检索的完整案例。下面我按自己实际搭过的一套方案，从建库到查询到踩坑讲清楚。

2. 建库建表：字符集、字段类型和主键怎么定

2.1 为什么字符集必须一次定死 utf8mb4

古诗词里出现生僻字、异体字、带声调的拼音注释是常事，更别说书名号、破折号这些全角符号。MySQL 里 utf8 其实是 utf8mb3，最多 3 字节，遇到某些扩展汉字和 emoji 会直接报Incorrect string value。所以建库第一步就把字符集和排序规则定死：

CREATE DATABASE poetry_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;

utf8mb4_0900_ai_ci是 MySQL 8.0 默认的排序规则，ai 表示 accent insensitive（声调不敏感），ci 表示 case insensitive。对中文来说声调不敏感影响不大，但如果你的注释里混了拼音，ai会让lǜ和lu在比较时等价，这点要心里有数。如果是 5.7 版本，用utf8mb4_unicode_ci更稳。建库时定死的好处是后面建表不用每张表重复写，减少漏写的概率。

提示：字符集只在建库建表时生效，后期改字符集要ALTER TABLE ... CONVERT TO CHARACTER SET，大表上会锁表重建，几百万行能跑很久，所以一开始就定对。

2.2 主表字段类型：正文用 TEXT 还是 VARCHAR

诗词正文长度差异极大，五言绝句 20 字，长篇歌行能到上千字。字段类型选错，要么截断要么浪费。我一般这样分：

正文用 TEXT 而不是 VARCHAR，是因为 VARCHAR 在行内存储，过长会把整行撑大影响其他字段读取；TEXT 溢出页单独存，主表行更紧凑。char_count是冗余字段，插入时用程序算好，避免每次ORDER BY CHAR_LENGTH(content)全表扫描。

CREATE TABLE poems ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, author VARCHAR(64) NOT NULL DEFAULT '', dynasty VARCHAR(32) NOT NULL DEFAULT '', genre VARCHAR(32) NOT NULL DEFAULT '', content TEXT NOT NULL, annotation TEXT, char_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_author (author), KEY idx_dynasty (dynasty), KEY idx_char_count (char_count) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

author、dynasty建普通索引，因为按作者和朝代筛选是最常见的查询。char_count建索引是为了排序，但要注意：如果数据量小，MySQL 可能直接走全表扫描加 filesort，索引反而不一定用上，用EXPLAIN确认。

2.3 作者表和朝代表要不要拆

有人喜欢把作者、朝代拆成独立维表，用外键关联。我的经验是：诗词库这种读多写少、维度值有限的场景，拆表收益不大，反而每次查询多一次 JOIN。除非你要存作者的生平、籍贯、生卒年这些扩展信息，那才值得单独建authors表。朝代就十几个值，更没必要拆。保持单表加冗余字段，查询最快，维护最简单。

3. 批量导入：从 TXT/JSON 到 MySQL 的完整脚本

3.1 数据清洗：简体转换和字段对齐

原始语料常见格式是每首诗一段，字段用特定分隔符隔开，或者直接是 JSON。导入前必须做三件事：统一转简体（用 opencc 之类的库）、去掉首尾空白和不可见字符、校验字段数量。清洗不干净，后面查询会冒出各种诡异结果，这是血泪经验。

import json import pymysql from opencc import OpenCC cc = OpenCC('t2s') # 繁体转简体 def clean_poem(raw): return { 'title': cc.convert(raw.get('title', '').strip()), 'author': cc.convert(raw.get('author', '').strip()), 'dynasty': cc.convert(raw.get('dynasty', '').strip()), 'genre': cc.convert(raw.get('genre', '').strip()), 'content': cc.convert(raw.get('content', '').strip()), 'annotation': cc.convert(raw.get('annotation', '').strip()), }

OpenCC('t2s')是繁转简配置，如果你的语料本来就是简体，这步可以省，但保留着能防止混入繁体字导致检索漏结果。strip()去掉首尾空白，注意不要用replace(' ', '')把正文里的空格也删了，诗词里的空格有时是排版需要。

3.2 用 executemany 批量插入而不是逐条

逐条INSERT几万次，网络往返和事务开销能把导入时间拉到几十分钟。正确做法是攒批，用executemany一次提交几百到几千条：

def import_poems(json_path, batch_size=1000): conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='yourpass', database='poetry_db', charset='utf8mb4' ) cur = conn.cursor() sql = """INSERT INTO poems (title, author, dynasty, genre, content, annotation, char_count) VALUES (%s, %s, %s, %s, %s, %s, %s)""" batch = [] with open(json_path, 'r', encoding='utf-8') as f: for line in f: raw = json.loads(line) p = clean_poem(raw) p['char_count'] = len(p['content'].replace('\n', '')) batch.append(( p['title'], p['author'], p['dynasty'], p['genre'], p['content'], p['annotation'], p['char_count'] )) if len(batch) >= batch_size: cur.executemany(sql, batch) conn.commit() batch.clear() if batch: cur.executemany(sql, batch) conn.commit() cur.close() conn.close()

batch_size=1000是折中值，太大内存吃紧，太小提交频繁。char_count用len(content.replace('\n', ''))算，去掉换行符，因为换行不算正文字数。注意executemany底层还是逐条执行，只是减少了网络往返，真正想更快可以用LOAD DATA INFILE，但要求数据是规整的 CSV/TSV，JSON 得先转。

注意：导入前把autocommit关掉，手动commit，否则每条都自动提交，慢得离谱。上面代码里pymysql默认就是手动提交模式，commit()显式调用。

3.3 导入后校验：行数和抽样比对

导入完别急着用，先校验：

SELECT COUNT(*) FROM poems; SELECT id, title, author, char_count FROM poems ORDER BY id DESC LIMIT 5;

行数应该和源文件行数一致（如果源文件一行一首）。抽样看最后几条，确认字段没串位、没截断。如果发现某条content为空，回去查源数据是不是有字段缺失。这一步花两分钟，能省后面几小时的排查。

4. 查询与索引：按作者、朝代、关键词检索怎么写才快

4.1 按作者和朝代筛选：走索引的写法

最常见的查询是「查李白的所有诗」或「查唐代的五言绝句」。这类等值查询走索引很快：

SELECT id, title, dynasty, char_count FROM poems WHERE author = '李白' ORDER BY char_count DESC LIMIT 20;

author上有索引，ORDER BY char_count如果数据量大，MySQL 可能先用idx_author过滤再 filesort。想避免 filesort，可以建联合索引(author, char_count)，让排序也走索引。但联合索引字段顺序有讲究：等值条件在前，排序字段在后。

ALTER TABLE poems ADD INDEX idx_author_count (author, char_count);

建完用EXPLAIN看Extra列，如果出现Using index condition而不是Using filesort，说明排序用上索引了。

4.2 关键词检索：LIKE 的边界和全文索引的取舍

「搜正文里含『明月』的诗」这种需求，第一反应是LIKE '%明月%'：

SELECT id, title, author FROM poems WHERE content LIKE '%明月%' LIMIT 50;

但LIKE '%...%'前置通配符会让索引失效，全表扫描。几万行还能忍，上百万行就明显卡。MySQL 8.0 支持ngram全文索引，对中文分词检索更友好：

ALTER TABLE poems ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT id, title FROM poems WHERE MATCH(content) AGAINST('明月' IN BOOLEAN MODE) LIMIT 50;

ngram解析器默认ngram_token_size=2，也就是按 2 字切分，适合中文双字词。但全文索引有坑：它按词匹配，搜「明月」能命中，搜「明」这种单字可能命中不了，因为 token 是 2 字。而且全文索引对短文本、生僻词效果一般。我的做法是：数据量小用 LIKE，数据量大且检索频繁用 ngram 全文索引，或者干脆上外部搜索引擎。别指望 MySQL 全文索引能替代专业搜索。

4.3 排序和分页：LIMIT 深分页的优化

ORDER BY char_count LIMIT 100000, 20这种深分页，MySQL 会先扫前 100020 行再丢掉前 100000 行，越翻越慢。优化思路是用游标：

SELECT id, title, char_count FROM poems WHERE char_count > 200 ORDER BY char_count LIMIT 20;

记住上一页最后一个char_count，下一页从它之后取。前提是排序字段有索引且值相对唯一，否则会漏数据。如果char_count重复多，加id做二级排序。

5. 避坑与排查：导入和查询里最容易翻车的五件事

5.1 中文乱码：现象是问号或方块

现象：导入后查询显示???或口口口。原因：连接字符集、表字符集、客户端字符集三者不一致。解决：连接串里显式写charset='utf8mb4'，建表用utf8mb4，查询前执行SET NAMES utf8mb4。三处对齐基本就不会乱码。

5.2 插入报 Incorrect string value

现象：插入含生僻字的诗词时报Incorrect string value: '\xF0\xA4...'。原因：字段或表还是 utf8mb3，存不下 4 字节字符。解决：ALTER TABLE poems CONVERT TO CHARACTER SET utf8mb4;，同时确认连接也是 utf8mb4。改完记得重建索引，因为字符集变更会重建。

5.3 全文索引搜不到词

现象：明明正文里有「明月」，MATCH ... AGAINST却返回空。原因：ngram_token_size默认 2，单字搜不到；或者词被停用词表过滤。解决：确认搜索词长度不小于 token size，检查innodb_ft_min_token_size和停用词设置。实在不行退回 LIKE。

5.4 导入中途断连

现象：导入几万条后报Lost connection to MySQL server。原因：单次提交数据量太大，超过max_allowed_packet，或者事务日志撑爆。解决：减小batch_size，调大max_allowed_packet，或者分批提交。我一般把 batch 控制在 500 到 1000。

5.5 ORDER BY 加索引反而更慢

现象：建了idx_char_count，但ORDER BY char_count还是慢。原因：优化器判断走索引回表成本高于全表扫描，尤其当筛选条件过滤性差时。解决：用EXPLAIN看执行计划，如果type=ALL且数据量不大，全表扫描反而合理。别盲目加索引，索引也要维护成本。

6. 进阶技巧：用生成列和存储过程把统计做进数据库

6.1 生成列自动算字数，省掉程序维护

前面char_count是程序算好插入的，容易漏。MySQL 5.7 以后支持生成列，可以自动算：

ALTER TABLE poems ADD COLUMN char_count_auto SMALLINT UNSIGNED GENERATED ALWAYS AS (CHAR_LENGTH(REPLACE(content, '\n', ''))) STORED;

STORED表示实际存储，能建索引；VIRTUAL不存储，查询时算。用STORED加索引，排序和筛选都能用上。这样插入时不用管这个字段，数据库自己维护。代价是每次插入多一次计算，但换来数据一致性，值。

6.2 用存储过程做朝代统计

想快速看每个朝代有多少首诗、平均字数多少，写个存储过程封装：

DELIMITER // CREATE PROCEDURE dynasty_stats() BEGIN SELECT dynasty, COUNT(*) AS poem_count, ROUND(AVG(char_count), 1) AS avg_chars FROM poems GROUP BY dynasty ORDER BY poem_count DESC; END // DELIMITER ;

调用CALL dynasty_stats();就能出结果。存储过程的好处是把复杂统计逻辑收进数据库，应用层只管调。但别滥用，业务逻辑还是放应用层更灵活，存储过程适合这种纯统计、变动少的场景。

6.3 验证方法：用已知数据反查

搭完库，拿几首你熟悉的诗做验证。比如搜「床前明月光」，确认能命中《静夜思》；查李白，确认返回数量合理；按字数排序，确认最短的是五言绝句。这种反查能快速发现字段错位、字符集问题、索引失效。我每次建完库都这么过一遍，比看日志快。

6.4 一个习惯：先 EXPLAIN 再上线

任何要上生产的查询，我都会先EXPLAIN看一眼。type是不是ref或range，rows估算多少，Extra有没有Using filesort或Using temporary。有这两个基本就要优化。这个习惯帮我挡掉过好几次全表扫描的慢查询。古诗词库这种读多写少的场景，查询性能直接决定用户体验，索引和 SQL 写法值得多花时间。

希望帮到你。

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