简介：这是一份面向古诗词研究者、中文信息处理开发者及传统文化爱好者的专业级MySQL数据库资源，旨在解决诗词数据分散、结构缺失、难以批量分析等实际问题。资源以单个43.55MB的SQL文件（古诗词.sql）为核心，开箱即用，导入后可立即生成含诗词ID、标题、作者、朝代、全文、注释、韵脚、流派、作者简介等10余字段的规范化数据表，完整覆盖近30万首中国古今诗词，支持多维度检索、统计与教学科研应用。包内仅1个ZIP压缩包，解压即得可执行SQL脚本，无需额外依赖或配置，适合具备基础数据库操作能力的中高级用户快速部署。目前已有3403人学习下载，读者可直接获得结构清晰、字段完备、内容权威的生产级诗词数据集，用于构建诗词检索系统、开展文学流派分析、支撑NLP文本挖掘，或作为高校数字人文课程的数据实践基底。
1. 古诗词数据库：为什么一个 MySQL 表结构设计，比爬完十万首诗更难？
你花三天写完爬虫，把《全唐诗》《宋词三百首》《清诗别裁集》甚至地方志里的佚诗都扒下来，存进 MySQL——结果一查“春风又绿江南岸”，返回 87 条重复记录；想按作者生卒年排序，发现字段是VARCHAR存的“约701–762”；想统计某诗人作品数量，却发现“李白”“李太白”“青莲居士”在author字段里各自为政。这不是数据量不够，而是古诗词数据的语义混沌性，在数据库表结构层面就已埋下系统性翻车的种子。这个标题说的不是“有个现成数据库下载”，而是如何从零构建一个能支撑检索、校勘、年代分析、作者谱系挖掘的生产级古诗词 MySQL 数据库——它必须承载标点异文、版本源流、注释层级、格律信息，还要让非文史背景的工程师能加字段、写 JOIN、建索引不踩坑。适合正在做数字人文项目、高校古籍整理平台、AI 诗词生成底座，或单纯想用真实数据练手 SQL 与 ETL 流程的开发者。别被“最全”二字带偏：真正决定项目成败的，从来不是数据总量，而是poem表里第 7 个字段叫什么、variant_text和collated_text是否分表、dynasty_id是 INT 还是 ENUM——这些细节，才是古诗词数据库的命门。
2. 表结构设计：从“一首诗存一行”到支撑校勘与版本溯源的六张核心表
古诗词数据绝非“标题+作者+正文”三字段能概括。真实业务中，你会频繁遇到：同一首诗在《四库全书》和《永乐大典》残卷中文字不同；王维《鹿柴》的“返景入深林”在敦煌写本作“返影入深林”；苏轼《念奴娇·赤壁怀古》通行本与元刊本词牌名、小序、断句均有差异。若强行塞进单表，text字段会爆炸，WHERE条件写到怀疑人生。我一般会拆成以下六张表，每张表解决一类不可妥协的语义需求：
2.1 主诗表（poem）：锚定唯一诗作实体，拒绝“诗题即ID”
CREATE TABLE `poem` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，全局唯一诗作ID', `poem_code` VARCHAR(32) NOT NULL COMMENT '标准化诗作编码，如 QTS-001234，永不变更', `title` VARCHAR(255) NOT NULL COMMENT '标准题名，不含卷次/序号，如"春晓"', `title_variant` TEXT COMMENT '异题列表，JSON数组，如["春晓","春晚"]', `genre` ENUM('shi','ci','qu','fu','yuefu') NOT NULL COMMENT '体裁，强制枚举，杜绝"诗""词"混用', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_poem_code` (`poem_code`), KEY `idx_title` (`title`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;逻辑说明：
poem_code是关键设计。它不依赖题名（题名可变），也不用自增ID（易被误当业务ID）。格式QTS-001234中QTS代表《全唐诗》来源，后缀为原始卷目页码映射，确保跨版本可追溯。title_variant用 JSON 而非关联表，因异题数量极少（通常≤3），避免 JOIN 开销；genre强制枚举而非字符串，堵死“词”“词牌”“宋词”等混乱值。
2.2 作者表（author）与朝代表（dynasty）：解决“李白是谁”的身份歧义
-- 朝代表（静态，预置24个朝代+“先秦”“民国”“当代”） CREATE TABLE `dynasty` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL COMMENT '朝代全称，如"唐朝"', `start_year` SMALLINT COMMENT '起始年份，西元，如618', `end_year` SMALLINT COMMENT '终止年份，如907', `code` VARCHAR(16) NOT NULL COMMENT '编码，如"TANG"，用于程序识别', PRIMARY KEY (`id`), UNIQUE KEY `uk_code` (`code`) ); -- 作者表（重点：一人多号、生卒模糊、籍贯多源） CREATE TABLE `author` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `author_code` VARCHAR(32) NOT NULL COMMENT '作者唯一编码，如 LI-BAI-001', `name_standard` VARCHAR(128) NOT NULL COMMENT '标准姓名，如"李白"', `name_aliases` JSON COMMENT '别名数组，如["李太白","青莲居士","谪仙人"]', `dynasty_id` TINYINT UNSIGNED NOT NULL COMMENT '外键，指向dynasty.id', `birth_year` VARCHAR(16) COMMENT '出生年，支持"约701"、"701?"、"697–701"等模糊表达', `death_year` VARCHAR(16) COMMENT '卒年，同上', `hometown` VARCHAR(255) COMMENT '籍贯，如"陇西成纪"，非标准化地址', `bio_summary` TEXT COMMENT '200字内生平摘要，供前端展示', PRIMARY KEY (`id`), UNIQUE KEY `uk_author_code` (`author_code`), KEY `idx_name_std` (`name_standard`), KEY `idx_dynasty` (`dynasty_id`) );参数说明：
birth_year/death_year用VARCHAR而非YEAR或DATE，因为古籍记载常为“开元间”“贞元末”，无法转为精确年份；name_aliases用 JSON 存储，既保持查询灵活性（JSON_CONTAINS(name_aliases, '"李太白"')），又避免为每个别名建行导致作者表膨胀十倍。author_code格式LI-BAI-001中LI-BAI是拼音标准化，001为同名作者序号（如存在两位“王安石”，则为WANG-AN-SHI-001/002）。
2.3 版本文本表（poem_version）：同一首诗，多个“真相”
CREATE TABLE `poem_version` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `poem_id` BIGINT UNSIGNED NOT NULL COMMENT '外键，指向poem.id', `version_code` VARCHAR(64) NOT NULL COMMENT '版本编码，如"QTS-V1-1234"、"DUNHUANG-P0123"', `source_type` ENUM('canonical','manuscript','print','modern_edition') NOT NULL COMMENT '来源类型', `source_ref` VARCHAR(255) COMMENT '具体出处，如"《全唐诗》卷162"、"P.2555背"、"中华书局1982版p45"', `text_content` LONGTEXT NOT NULL COMMENT '纯文本，含规范标点，无HTML/排版', `line_count` SMALLINT UNSIGNED NOT NULL COMMENT '行数，用于格律校验', `is_collated` TINYINT(1) DEFAULT 0 COMMENT '是否为校勘定本（1=是，0=原始版本）', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_poem_id` (`poem_id`), KEY `idx_version_code` (`version_code`), KEY `idx_source_type` (`source_type`) );为什么必须分表？当你要查“《登鹳雀楼》所有异文”，若
text_content在poem表里，只能SELECT * FROM poem WHERE title='登鹳雀楼'—— 但poem表只存“标准文本”，其他版本根本不在里面。而poem_version表允许你：
SELECT * FROM poem_version WHERE poem_id = 123 AND source_type = 'manuscript'查敦煌写本；SELECT * FROM poem_version WHERE poem_id = 123 AND is_collated = 1查权威校勘本；JOIN时带上source_ref，直接定位到古籍影印页码。这才是数字人文需要的“可验证”。
2.4 格律与韵部表（poem_metric）：让“平仄”不再靠玄学判断
CREATE TABLE `poem_metric` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `poem_version_id` BIGINT UNSIGNED NOT NULL COMMENT '外键，指向poem_version.id', `metric_pattern` VARCHAR(128) NOT NULL COMMENT '格律模板，如"七言绝句·平起首句入韵"，需预定义字典', `rhyme_scheme` VARCHAR(64) COMMENT '韵脚序列，如"ABAB"，对应每句末字', `rhyme_chars` JSON COMMENT '押韵字数组，如["楼","流","舟"]', `tone_analysis` JSON COMMENT '平仄分析结果，JSON对象：{"lines":[{"text":"白日依山尽","pattern":"仄仄平平仄","analysis":"✓"}]}', `is_verified` TINYINT(1) DEFAULT 0 COMMENT '是否经人工校验（1=是）', PRIMARY KEY (`id`), UNIQUE KEY `uk_version_id` (`poem_version_id`), KEY `idx_metric_pattern` (`metric_pattern`) );落地价值：
tone_analysis字段存完整分析结果，而非只存“平仄合格”。当你开发 AI 生成模型时，可直接SELECT tone_analysis FROM poem_metric WHERE poem_version_id = ?拿到每句的平仄标注，喂给模型训练；is_verified字段标记人工核验过的格律，避免算法误判污染训练集。metric_pattern用预定义字符串而非自由文本，保证GROUP BY metric_pattern能准确统计各体裁分布。
2.5 注释与笺证表（poem_annotation）：把“王琦注《李太白全集》”变成可查询字段
CREATE TABLE `poem_annotation` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `poem_version_id` BIGINT UNSIGNED NOT NULL COMMENT '注释所属版本', `annotator_id` BIGINT UNSIGNED COMMENT '注者ID，可关联author表（如王琦）', `annotation_type` ENUM('explanation','historical_context','allusion','linguistic','critical_comment') NOT NULL, `content` TEXT NOT NULL COMMENT '注释正文，支持Markdown基础语法', `location` JSON COMMENT '位置标记，如{"line":3,"char_offset":5,"length":2}，指向原文字符位置', `source_ref` VARCHAR(255) COMMENT '注释出处，如"王琦《李太白全集》卷三"，必填', PRIMARY KEY (`id`), KEY `idx_version_id` (`poem_version_id`), KEY `idx_annotator` (`annotator_id`), KEY `idx_type` (`annotation_type`) );关键设计：
location字段用 JSON 记录注释在原文中的精确位置（第3行、第5个字符起、覆盖2字），这使得前端可实现“点击‘孤帆’二字，弹出王琦对此处的典故解释”。若不用结构化存储，只能全文搜索“孤帆”，匹配精度极低。source_ref强制非空，确保每条注释可溯源，这是学术数据库的底线。
2.6 关系表（poem_author）：处理“联句”“集句”“无名氏”的复杂归属
CREATE TABLE `poem_author` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `poem_id` BIGINT UNSIGNED NOT NULL COMMENT '诗作ID', `author_id` BIGINT UNSIGNED NOT NULL COMMENT '作者ID', `role` ENUM('primary','secondary','compiler','attributed_to','doubtful') DEFAULT 'primary' COMMENT '角色：主作者、次作者、编者、托名、存疑', `confidence` TINYINT UNSIGNED COMMENT '归属可信度，0-100，如"《黄鹤楼》崔颢作"为100，"《胡笳十八拍》蔡琰作"为60', `note` TEXT COMMENT '说明，如"《乐府诗集》归入古辞，今多认为汉末文人拟作"', PRIMARY KEY (`id`), UNIQUE KEY `uk_poem_author_role` (`poem_id`, `author_id`, `role`), KEY `idx_poem_id` (`poem_id`), KEY `idx_author_id` (`author_id`) );为什么不用
poem.author_id单字段？因为《兰亭序》是王羲之书写+孙绰作序+谢安赋诗；《滕王阁序》是王勃作序+众宾客赋诗；大量边塞诗题为“无名氏”，实为军中幕僚集体创作。poem_author表支持一对多、多对多、角色区分、可信度量化，这才是处理古籍作者问题的正确姿势。
3. 数据清洗与入库：从杂乱文本到结构化 MySQL 的三阶段流水线
有了表结构，真正的硬仗才开始：你拿到的原始数据，大概率是 PDF 扫描OCR错字、网页HTML混排、TXT无标点、Excel列错位。我坚持用三阶段流水线处理，每阶段输出可验证中间产物，拒绝“一键导入”这种黑匣子操作。
3.1 阶段一：源数据标准化（Source Normalization）
目标：将所有输入统一为 UTF-8 编码、无 HTML 标签、段落分明的.txt文件，每首诗以===POEM===分隔。
# 示例：清洗一个含HTML的网页导出TXT # 1. 去HTML标签（用sed简单处理，复杂页面用BeautifulSoup） sed -E 's/<[^>]*>//g' raw_web.txt > no_html.txt # 2. 合并连续空行，保留诗作分隔 awk 'BEGIN{RS=""; ORS="\n\n"} {gsub(/\n\s*\n/, "\n\n"); print}' no_html.txt > cleaned.txt # 3. 插入标准化分隔符（用Python脚本更可靠） python3 normalize_separator.py --input cleaned.txt --output poems_normalized.txt
normalize_separator.py核心逻辑：
- 读取文件，按空行切分“疑似诗作块”；
- 对每块调用正则
r'^[《〈【\[][^》〉】\]]+[》〉】\]]$'匹配题名行；- 若块内含题名且行数≥3，则在块首插入
===POEM===；- 输出为严格分隔的文本，供下一阶段解析。
血泪经验：跳过此步直接解析，会导致“《静夜思》床前明月光……”和后面广告“©2023古诗网”连成一首诗，后续所有清洗失效。
3.2 阶段二：结构化解析（Structured Parsing）
目标：将poems_normalized.txt解析为 JSONL（每行一个 JSON 对象），字段对齐poem、author、poem_version表。
# parse_poem.py import re import json def parse_poem_block(block: str) -> dict: # 提取题名：匹配《》【】等括号内的文字 title_match = re.search(r'[《〈【\[](.*?)[》〉】\]]', block) title = title_match.group(1).strip() if title_match else "" # 提取作者：常见模式“作者：李白”、“——李白”、“右XXX” author_match = re.search(r'(?:作者[:：]|——|右|署名[:：])\s*([^\n]+)', block) author_raw = author_match.group(1).strip() if author_match else "" # 提取正文：去掉题名、作者行，取剩余非空行 lines = [l.strip() for l in block.split('\n') if l.strip()] text_lines = [] for l in lines: if l == title or l == author_raw or re.match(r'(?:作者|——|右)', l): continue text_lines.append(l) text_content = '\n'.join(text_lines) return { "title": title, "author_raw": author_raw, "text_content": text_content, "source_ref": "raw_web_source_v1" # 后续替换为真实出处 } # 主流程 with open("poems_normalized.txt", "r", encoding="utf-8") as f: blocks = f.read().split("===POEM===") with open("poems_parsed.jsonl", "w", encoding="utf-8") as out: for block in blocks: if not block.strip(): continue parsed = parse_poem_block(block) out.write(json.dumps(parsed, ensure_ascii=False) + "\n")参数说明：
parse_poem_block不追求100%准确，而是保证可调试、可修复。输出 JSONL 后，可用jq快速验证：jq 'select(.title == "")' poems_parsed.jsonl查空题名；jq 'select(.text_content | length < 10)' poems_parsed.jsonl查异常短文本；
发现问题，直接改parse_poem_block函数，重跑即可，无需碰数据库。
3.3 阶段三：MySQL 批量入库（Bulk Insert with Validation）
目标：将 JSONL 导入 MySQL，同时校验外键、去重、填充默认值。
-- 创建临时表，结构同poem_version，但无外键约束 CREATE TEMPORARY TABLE `poem_version_staging` LIKE `poem_version`; ALTER TABLE `poem_version_staging` DROP FOREIGN KEY `fk_poem_id`; -- 使用LOAD DATA INFILE（需MySQL有FILE权限） LOAD DATA INFILE '/path/to/poems_parsed.jsonl' INTO TABLE `poem_version_staging` FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n' (@json_line) SET `poem_id` = JSON_UNQUOTE(JSON_EXTRACT(@json_line, '$.poem_id')), `version_code` = CONCAT('RAW-', MD5(@json_line)), `source_type` = 'modern_edition', `source_ref` = JSON_UNQUOTE(JSON_EXTRACT(@json_line, '$.source_ref')), `text_content` = JSON_UNQUOTE(JSON_EXTRACT(@json_line, '$.text_content')), `line_count` = LENGTH(JSON_UNQUOTE(JSON_EXTRACT(@json_line, '$.text_content'))) - LENGTH(REPLACE(JSON_UNQUOTE(JSON_EXTRACT(@json_line, '$.text_content')), '\n', '')) + 1;关键技巧：
- 用
JSON_EXTRACT直接从 JSONL 行提取字段，避免 Python 中转；version_code用MD5(@json_line)生成，确保相同文本内容永远得同一 code，天然去重；line_count用字符串函数计算，比应用层解析更高效；- 入库后，再用
INSERT ... SELECT将校验通过的数据从 staging 表转入正式表，并ON DUPLICATE KEY UPDATE处理冲突。
提示：LOAD DATA INFILE比INSERT快 10 倍以上，10 万首诗可在 2 分钟内完成初载。
4. 避坑指南：古诗词 MySQL 数据库的 5 个高频翻车现场
古诗词数据的特殊性，会让常规数据库经验瞬间失效。以下是我在三个数字人文项目中踩出的血坑，按“现象→原因→解决”列出，每一条都附带可验证的 SQL。
4.1 现象：SELECT * FROM poem WHERE title = '江雪'返回 0 条，但明明有这首诗
原因：题名字段含不可见 Unicode 字符（如全角空格、零宽空格），或前端传参用了urlencode导致%E6%B1%9F%E9%9B%AA未解码。
解决：
-- 查看title字段实际字节 SELECT title, HEX(title) FROM poem WHERE title LIKE '%江雪%'; -- 若HEX结果含C2A0（NBSP）或E2808B（ZWSP），用以下清洗 UPDATE poem SET title = REPLACE(REPLACE(title, '\xC2\xA0', ' '), '\xE2\x80\x8B', '') WHERE title REGEXP '[\xC2\xA0|\xE2\x80\x8B]';4.2 现象：COUNT(*)统计作者数量为 12,345，但SELECT COUNT(DISTINCT name_standard)为 11,987
原因：author.name_standard存在肉眼不可辨差异，如“王维” vs “王維”（繁体）、“欧阳修” vs “歐陽修”，MySQL 默认 utf8mb4_unicode_ci 排序规则不区分简繁。
解决：
-- 建立简繁转换映射表（示例） CREATE TABLE `char_mapping` ( `simplified` CHAR(1) NOT NULL, `traditional` CHAR(1) NOT NULL, PRIMARY KEY (`simplified`,`traditional`) ); INSERT INTO char_mapping VALUES ('维','維'), ('欧','歐'), ('刘','劉'); -- 查询时统一转简体 SELECT COUNT(DISTINCT REPLACE(REPLACE(a.name_standard, '維', '维'), '歐', '欧') ) FROM author a; -- 长期方案：入库前用Python的opencc库统一转简体4.3 现象：poem_version.text_content字段存了 500KB 的长文本，SELECT极慢，LIKE '%春风%'全表扫描
原因：未建全文索引，且text_content未分词，LIKE无法利用 B+Tree。
解决：
-- 添加全文索引（MySQL 5.6+） ALTER TABLE `poem_version` ADD FULLTEXT(`text_content`); -- 用MATCH AGAINST替代LIKE（快100倍） SELECT id, text_content FROM poem_version WHERE MATCH(text_content) AGAINST('+春风 +江南' IN BOOLEAN MODE); -- 注意：需配置ft_min_word_len=2（默认为4，会忽略“春”“风”）4.4 现象：JOIN poem_version pv ON p.id = pv.poem_id返回笛卡尔积，一首诗出现 200 行
原因：poem_version表中poem_id未建索引，或poem_id值为 0（外键约束被禁用时插入的脏数据）。
解决：
-- 第一步：查出所有无效poem_id SELECT DISTINCT poem_id FROM poem_version WHERE poem_id NOT IN (SELECT id FROM poem) AND poem_id != 0; -- 第二步：修复（设为NULL或删除） UPDATE poem_version SET poem_id = NULL WHERE poem_id = 0; DELETE FROM poem_version WHERE poem_id NOT IN (SELECT id FROM poem); -- 第三步：强制加索引（即使建表时写了KEY，也要确认） ALTER TABLE `poem_version` ADD KEY `idx_poem_id` (`poem_id`);4.5 现象：INSERT INTO poem_version报错Data too long for column 'text_content'，但LENGTH()显示仅 2000 字符
原因：text_content含大量 emoji 或生僻汉字（如“龘”），占用 4 字节，而TEXT类型最大 65,535 字节，但LENGTH()返回字节数，CHAR_LENGTH()返回字符数，混淆导致误判。
解决：
-- 查看实际字节数上限 SELECT @@max_allowed_packet, @@innodb_log_file_size; -- 永久方案：改用MEDIUMTEXT（16MB）或LONGTEXT（4GB） ALTER TABLE `poem_version` MODIFY `text_content` LONGTEXT; -- 临时应急：截断超长文本（保留前5000字符） UPDATE poem_version SET text_content = LEFT(text_content, 5000) WHERE LENGTH(text_content) > 5000;5. 进阶技巧：用 MySQL 窗口函数做古诗词年代热力图与作者谱系推演
当基础表结构和清洗流程跑通，真正的价值才开始释放。这里分享一个实战技巧：不用 Python Pandas，纯用 MySQL 8.0+ 窗口函数，实时生成“历代诗人活跃热力图”和“作者师承关系链”。它证明古诗词数据库不是静态仓库，而是可计算的知识图谱起点。
5.1 生成“朝代-年份”二维热力图（支持前端 ECharts 直接渲染）
目标：统计每十年间，各朝代有多少诗人有作品存世（以poem_version为依据），输出格式：{"dynasty":"唐朝","decade":710,"count":12}。
-- 步骤1：为每位作者计算“活跃年代区间”（取生卒年中值，或仅用朝代年份） WITH author_active AS ( SELECT a.id as author_id, d.name as dynasty_name, d.start_year, d.end_year, -- 若生卒年存在，取中值；否则用朝代中值 COALESCE( ROUND((CAST(REPLACE(a.birth_year, '约', '') AS SIGNED) + CAST(REPLACE(a.death_year, '约', '') AS SIGNED)) / 2), ROUND((d.start_year + d.end_year) / 2) ) as active_year FROM author a JOIN dynasty d ON a.dynasty_id = d.id WHERE a.birth_year REGEXP '^[0-9?]+[-–][0-9?]+$' OR a.birth_year REGEXP '^[0-9?]+$' ), -- 步骤2：将活跃年份映射到“十年”桶（710=710-719年） decade_map AS ( SELECT dynasty_name, FLOOR(active_year / 10) * 10 AS decade, COUNT(*) as count FROM author_active GROUP BY dynasty_name, FLOOR(active_year / 10) ) -- 步骤3：补全缺失的朝代-十年组合（避免前端画图缺数据） SELECT d.name as dynasty, COALESCE(dm.decade, 600) as decade, -- 起始年份 COALESCE(dm.count, 0) as count FROM dynasty d CROSS JOIN (SELECT 600 as decade UNION SELECT 610 UNION SELECT 620 /* ... up to 1950 */) decades LEFT JOIN decade_map dm ON d.name = dm.dynasty_name AND decades.decade = dm.decade ORDER BY d.name, decades.decade;为什么用窗口函数？上述 SQL 中虽未显式用
OVER()，但CROSS JOIN+LEFT JOIN的组合，本质是窗口思维：为每个朝代生成完整十年序列，再左连接实际数据。若用传统GROUP BY，缺失年份会直接消失，前端热力图出现大片空白。此查询在 10 万作者数据上耗时 <0.5 秒，可设为视图供 BI 工具直连。
5.2 推演“作者师承关系链”（基于注释与文献互引）
目标：找出“王维 → 王昌龄 → 岑参”这样的传播链。原理：若王昌龄诗注中多次引用王维诗句并称“右丞云”，且岑参注中引用王昌龄，则构成间接传承。
-- 构建“注释中提及作者”的关系表（物化视图） CREATE TABLE `annotation_mention` AS SELECT pa.poem_version_id, pa.annotator_id, JSON_UNQUOTE(JSON_EXTRACT(pa.content, '$.mentioned_author')) as mentioned_author, pa.source_ref FROM poem_annotation pa WHERE pa.content REGEXP '"mentioned_author":'; -- 然后用递归CTE找3层关系（MySQL 8.0+） WITH RECURSIVE mentor_chain AS ( -- 种子：直接提及（A注中提B） SELECT am1.annotator_id as mentor_id, am2.mentioned_author as mentee_name, 1 as level FROM annotation_mention am1 JOIN author a ON am1.annotator_id = a.id JOIN annotation_mention am2 ON am1.poem_version_id = am2.poem_version_id WHERE am2.mentioned_author = a.name_standard UNION ALL -- 递归：B注中提C，且B在上层是mentee SELECT mc.mentor_id, am.mentioned_author, mc.level + 1 FROM mentor_chain mc JOIN annotation_mention am ON am.mentioned_author = mc.mentee_name JOIN author a ON am.annotator_id = a.id WHERE mc.level < 3 ) SELECT a1.name_standard as mentor, a2.name_standard as mentee, mc.level FROM mentor_chain mc JOIN author a1 ON mc.mentor_id = a1.id JOIN author a2 ON a2.name_standard = mc.mentee_name WHERE mc.level <= 3 ORDER BY mc.level, a1.name_standard;落地效果：此查询输出可直接导入 Gephi 做网络图，或转为 JSON 供 D3.js 渲染动态谱系树。关键在于
annotation_mention表的构建——它把非结构化的注释文本，转化为可 JOIN 的关系表，这是古籍知识抽取的核心范式。
5.3 我的日常维护习惯：给古诗词数据库装上“后悔药”
最后分享一个保命习惯：所有 DDL 变更（加字段、改类型）必须配套生成回滚 SQL，并存入schema_history表。古诗词项目周期长，半年后你可能忘了poem_metric.tone_analysis为何要 JSON 而非 TEXT。
-- 创建变更历史表 CREATE TABLE `schema_history` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `version` VARCHAR(32) NOT NULL COMMENT '版本号，如20231001v1', `description` TEXT NOT NULL COMMENT '变更描述', `up_sql` TEXT NOT NULL COMMENT '升级SQL', `down_sql` TEXT NOT NULL COMMENT '回滚SQL', `applied_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_version` (`version`) ); -- 示例：为poem_version添加is_verified字段 INSERT INTO schema_history VALUES ( NULL, '20231001v1', 'Add is_verified flag to poem_version for human validation tracking', 'ALTER TABLE poem_version ADD COLUMN is_verified TINYINT(1) DEFAULT 0;', 'ALTER TABLE poem_version DROP COLUMN is_verified;', NOW() );每次上线前，我运行SELECT down_sql FROM schema_history WHERE version = 'xxx'复制回滚语句，粘贴到测试库验证无误再执行。这招让我在一次误删poem_author外键后，30 秒内恢复全部关联，没影响下游 API。古诗词数据不可再生，你的数据库 Schema 就是它的第一道护城河。
希望帮到你。
本文还有配套的精品资源，点击获取
