简介周公解梦数据集将传统梦境文化与数据库技术结合收录约7261条梦境解析记录适合数据分析师、传统文化研究者、心理学爱好者以及前端/后端开发者使用可用于梦境心理学研究、文化数据挖掘或搭建趣味查询应用。资源共4个文件压缩包仅3.84MB分别提供JSON、SQL、CSV、XLSX四种格式JSON适合Web服务与前后端交互SQL可通过标准查询实现主题筛选和统计CSV便于跨工具导入导出XLSX则支持排序、过滤与图表可视化。目前已有612人浏览学习。借助这套数据读者既能按关键词快速检索梦境释义又能开展梦境主题频次与情感倾向的关联分析若需构建解梦查询系统可直接导入SQL或JSON文件同样适合在教学演示或文化类应用中作为示例数据是一份小巧实用的中文数据集。1. 数据库-周公解梦数据集从一张梦单到可检索的中文文本库「数据库-周公解梦数据集」听起来像一张“梦见什么、解成什么”的对照表但真拿到手你会发现它是半结构化的中文文本语料条目里有“梦见蛇主财”这样的短句也有“梦见掉牙主亲属有难”这类带情感倾向的长描述。我把它整理成 MySQL 表之后最大的价值不是拿去做玄学解读而是让检索、接口调用、小程序搜索都能直接拿数据说话。这篇笔记写给两类人做内容站、闲聊机器人、运势类 App 的开发以及刚入门想把一份现成数据变成关系型库的从业者。下面是建表、清洗、导库、排错的完整路径照着跑就能用起来。2. 先建模再入库把梦境条目拆成四张表2.1 单表宽表为何是坑一条“梦蛇”对应七种解释范式拆表才查得动周公解梦的原始数据常见有两种形态一种是纯文本每行一条“梦见某物某解法”一种是 CSV 或 Excel左列梦境、右列解法。无论哪种都有一个共性同一梦境内容在不同来源里对应多条解法而且彼此语义冲突。比如“梦见蛇”在古本里解作“主财”在现代网络版里可能解作“防小人”甚至同一个来源里两条记录都叫“梦见蛇”解法却完全不同。如果按直觉做成一张宽表dreams id | content | interpretation那“梦见蛇”这一行就得重复两三遍后续要加“来源版本号”“吉凶倾向”字段时表会越改越歪。更麻烦的是如果你想按“动物”“器物”“自然现象”做主题聚合宽表只能在 content 上做 LIKE 硬刷查一次慢一次。所以我一般拆成四张表梦境主表存“梦见蛇”这种原文解释表存解法文本关键词表做主题分类再加一张关联表把梦境和关键词做成多对多。这样一条梦挂多条解释、一个关键词挂多组梦都只需要加行不需要改表结构。方案主要字段适合场景典型问题单表宽表content, interpretation一次性查看一条梦多解法没法表达重复存储单表加扩展列content, interp1, interp2解法固定两条条目之间列数不齐难扩展四表范式dreams, keywords, relations, interpretations检索、接口、分类统计写入逻辑略复杂但查询利索2.2 字段选型utf8mb4、TEXT 与 content_hash 的取舍建表前最容易翻车的不是主键是字符集。MySQL 里utf8其实不是完整 UTF-8它叫 utf8mb3存不了 Emoji 和部分生僻字。解梦数据里“魇”“龋”“殍”这类字出现频率不低所以字符集直接上utf8mb4排序规则我选utf8mb4_unicode_ci对中文这种多音字场景比 general_ci 稳一点。字段长短也有讲究。梦境内容大多是短句二三十字封顶VARCHAR(500)够用解法文本就不一定了有的条目会带一整段“宜忌”“方位”“时辰”得用TEXT。真正容易被忽略的是去重content最长 500 字符没法直接加唯一索引我习惯加一列content_hash用 MD5 生成列把它设成唯一键。这个字段后面导入清洗时会反复用到。2.3 建表 SQL直接可跑的 MySQL 8.0 DDL脚本-- 梦境主表一条“梦见蛇”只在这里出现一次 CREATE TABLE dreams ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content VARCHAR(500) NOT NULL COMMENT 梦境内容如“梦见蛇”, content_hash CHAR(32) GENERATED ALWAYS AS (MD5(content)) STORED, source_version VARCHAR(50) NULL COMMENT 数据来源版本方便追溯, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_content_hash (content_hash) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 关键词表用来做主题分类比如 动物/器物/自然 CREATE TABLE keywords ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, keyword VARCHAR(100) NOT NULL COMMENT 关键词如“蛇”, category VARCHAR(50) NULL COMMENT 主题分类, UNIQUE KEY uk_keyword (keyword) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 梦境与关键词关联表 CREATE TABLE dream_keywords ( dream_id BIGINT UNSIGNED NOT NULL, keyword_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (dream_id, keyword_id), KEY idx_keyword_id (keyword_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 解释表一条梦可以挂多条解法 CREATE TABLE interpretations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dream_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL COMMENT 解法原文, fortune_level TINYINT NULL COMMENT 吉凶倾向1吉 2平 3凶, source_version VARCHAR(50) NULL COMMENT 来源版本, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这段 DDL 里最值得说的是content_hash它用的是 MySQL 8.0 的生成列MD5(content)一旦写入就不会因为后续修改 content 而失配。唯一索引加在这一列上既避免了 TEXT 字段加索引的长度限制又让重复检查从万行级全表扫描变成一次索引查找。主键用BIGINT UNSIGNED而不是INT看起来杀鸡用牛刀但数据合并、多实例同步时不用再回头改主键类型。如果你后续要接人大金仓、TiDB 这类兼容 MySQL 的数据库这套 DDL 改动也很小。3. 数据导入把原始解梦文本清洗成 CSV再灌进 MySQL3.1 预处理用正则把“梦见蛇主财”拆成结构化字段拿到原始数据常见格式是每行一条梦见蛇主财宜远行 梦见掉牙:主亲属有难 梦见大水主财源滚滚注意分隔符并不统一全角冒号、半角冒号、逗号都有。我一般先做一步归一化把所有分隔符换成统一标记再拆字段。这里直接给一段可跑的 Python 脚本import re, csv src_path 周公解梦原始.txt rows [] with open(src_path, encodingutf-8) as f: for line in f: line line.strip() if not line: continue # 把全角冒号统一成半角避免正则写两遍 line line.replace(, :) # 只在第一个冒号处拆分防止解法文本里再出现冒号 parts line.split(:, 1) if len(parts) 2: continue content parts[0].strip() interp parts[1].strip() rows.append([content, interp]) with open(dreams_clean.csv, w, encodingutf-8, newline) as f: writer csv.writer(f) writer.writerow([content, interpretation]) writer.writerows(rows) print(f清洗完成共 {len(rows)} 条)这段代码的关键是split(:, 1)限制只拆第一次出现的位置否则像“梦见蛇主财宜远行”这种条目会被拆成三段。如果你的原始文件是 Excel 导出的先另存为 CSV 并选 UTF-8 编码别直接读 .xlsx否则后面连接数据库时十有八九会撞上编码问题。另外要检查原始文件是不是用 Tab 分隔的line.split(\t, 1)就得替换掉冒号拆分逻辑。3.2 批量写入executemany 与连接参数怎么设清洗完只是第一步真正容易出问题的是写入。数据量通常不大但重复执行导入脚本会插入重复行所以我先建立关键词映射再按行写库import pymysql, csv conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedream_db, charsetutf8mb4, # 连接字符集必须显式指定 autocommitFalse, # 关闭自动提交统一管理事务 ) cur conn.cursor() # 把已有的关键词加载进内存避免逐条查库 cur.execute(SELECT keyword, id FROM keywords) kw_map {row[0]: row[1] for row in cur.fetchall()} with open(dreams_clean.csv, encodingutf-8) as f: reader csv.reader(f) next(reader) # 跳过表头 batch [] for content, interp in reader: # 用 content_hash 快速查重避免重复插入 cur.execute(SELECT id FROM dreams WHERE content_hash MD5(%s), (content,)) row cur.fetchone() if row: dream_id row[0] else: cur.execute(INSERT INTO dreams (content, source_version) VALUES (%s, v1), (content,)) dream_id cur.lastrowid cur.execute( INSERT INTO interpretations (dream_id, content) VALUES (%s, %s), (dream_id, interp), ) batch.append(dream_id) # 每 200 条提交一次避免长事务锁表 if len(batch) 200: conn.commit() batch.clear() conn.commit() cur.close() conn.close() print(导入完成)参数说明charsetutf8mb4必须写漏掉它即使表结构是对的Python 客户端读出来也可能乱码。autocommitFalse配合每 200 条一次commit()是为了防止在中途出错时留下半个事务的脏数据如果你用 Navicat、dbx 这类数据库工具做导入工具内部也是同样的批处理逻辑。这里没做关键词关联真正落地时还要把每行文本拆词后写入dream_keywords拆词可以用 jieba也可以直接从interpretations的文本里按规则抽但别小看这一步它是后续按主题统计的基础。3.3 导入后校验行数、重复率、空字段三个指标导入完别急着写接口先用三条 SQL 做体检SELECT COUNT(*) AS total_dreams FROM dreams; SELECT COUNT(*) AS total_interpretations FROM interpretations; -- 重复率正常应为 0 SELECT content_hash, COUNT(*) FROM dreams GROUP BY content_hash HAVING COUNT(*) 1; -- 空字段检查两条结果都应为 0 SELECT COUNT(*) FROM dreams WHERE content IS NULL OR content ; SELECT COUNT(*) FROM interpretations WHERE content IS NULL OR content ;行数对不上原始文件是最常见的情况原因通常是预处理阶段的正则漏掉了分隔符不标准的那几行。我习惯在清洗脚本里加个计数再和原始总行数对比如果差得不多直接用 grep 把漏掉的行捞出来手工修。4. 把数据用起来增删改查、全文检索与统计4.1 按关键词查解法等值查询与 JOIN 的正确姿势四表结构的好处在这时候才体现出来。想知道“梦见蛇”的所有解法一条 JOIN 就能拉出来SELECT d.content AS dream_text, i.content AS interpret_text, k.category FROM dreams d JOIN interpretations i ON i.dream_id d.id LEFT JOIN dream_keywords dk ON dk.dream_id d.id LEFT JOIN keywords k ON k.id dk.keyword_id WHERE k.keyword 蛇;这里用LEFT JOIN而不是INNER JOIN是因为有些梦还没拆出关键词避免漏掉解法记录。WHERE k.keyword 蛇走的是uk_keyword唯一索引数据量上万也是毫秒级返回。刚入门的人最容易在这个阶段想着“顺便”加一句AND d.content LIKE %蛇%这一加就把整条 SQL 的索引优势抹掉了改成全表扫描。4.2 中文全文检索ngram 解析器与两个必调参数真正需要模糊搜的是用户输入“梦见掉了一颗牙”而库里存的是“掉牙”。这种场景LIKE %牙%能出结果但LIKE %掉牙%就会漏。MySQL 从 5.7 起内置了全文索引但对中文默认分词并不友好必须显式指定 ngram 解析器ALTER TABLE dreams ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT id, content FROM dreams WHERE MATCH(content) AGAINST(掉牙 IN NATURAL LANGUAGE MODE);ngram_token_size要提前在 my.cnf 里配好我设的是 2也就是按双字切词。这个参数在 MySQL 8.0 里属于只读参数改完必须重启实例而且已经建好的全文索引要重建才能生效。如果表不大ALTER TABLE dreams DROP INDEX ft_content, ADD FULLTEXT INDEX ... WITH PARSER ngram;直接重建也行。注意IN NATURAL LANGUAGE MODE对短词召回少想扩大召回可以换IN BOOLEAN MODE代价是结果里噪音会多一些。4.3 统计聚合按梦境主题看数据分布运营或产品经常要看“动物类梦有多少条”“哪类解法最多”这类需求直接 GROUP BY 就行SELECT k.category, COUNT(*) AS cnt FROM dream_keywords dk JOIN keywords k ON k.id dk.keyword_id GROUP BY k.category ORDER BY cnt DESC;如果keywords.category还没填可以先跑一条 UPDATE 按关键词粗略归类比如UPDATE keywords SET category动物 WHERE keyword IN (蛇,狗,猫,鱼)。这一步其实也是数据清洗的一部分不是靠数据库自动完成的。4.4 增删改查与事务回滚数据库常用命令的边界增删改查是基本功但解梦数据有个特殊场景删除一条梦时必须把它在interpretations和dream_keywords里的关联数据也清掉否则就成了孤儿数据。我把这三条操作放进一个事务START TRANSACTION; DELETE FROM interpretations WHERE dream_id 100; DELETE FROM dream_keywords WHERE dream_id 100; DELETE FROM dreams WHERE id 100; -- 如果第二步报错回滚到事务开始前 ROLLBACK; -- 确认无误后提交 COMMIT;日常维护常用的命令就这几个没必要全背命令作用SHOW TABLES;查看所有表DESC dreams;查看表结构SHOW INDEX FROM dreams;查看索引状态EXPLAIN SELECT ...查看执行计划判断是否走了索引SHOW PROCESSLIST;查看是否有卡住的连接EXPLAIN是最值得养成的习惯。我每次写新查询只要结果集超过几百行就会先EXPLAIN看一眼key列如果显示NULL说明在扫全表得马上回头改 SQL。5. 常见问题与避坑乱码、重复、慢查询与来源偏差5.1 全库都设了 utf8mb4客户端读出还是乱码现象表结构DEFAULT CHARSETutf8mb4用命令行查没问题但 Python 读出来全是????。原因连接层字符集没对齐。MySQL 的字符集分三层库表结构、连接、客户端。表结构对不代表连接对Python 的 pymysql 如果不显式传charset默认可能落到latin1。解决连接串里显式加上charsetutf8mb4命令行就执行SET NAMES utf8mb4;。如果已经有乱码数据先备份再执行ALTER TABLE dreams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意CONVERT会重写整表几万行没事几百万行要挑业务低峰期做。5.2 导入脚本跑两遍数据多出一倍现象SELECT COUNT(*) FROM dreams的结果比原始文件行数还多用content_hash一查重复率不是 0。原因清洗脚本没有幂等性。第一遍导入成功第二遍跑的时候旧数据没删新数据直接追加进去。我最早也踩过这个坑后来才想起在content_hash上加唯一索引。解决ALTER TABLE dreams ADD UNIQUE KEY uk_content_hash (content_hash);然后把 INSERT 改成INSERT IGNORE INTO dreams ...或者干脆在导入脚本开头先TRUNCATE TABLE interpretations; TRUNCATE TABLE dream_keywords; TRUNCATE TABLE dreams;保证每次导入都是干净的。5.3 用 LIKE 搜“梦见”越来越慢现象几千条数据时WHERE content LIKE %梦见%毫秒级返回数据量过万后明显变慢EXPLAIN 显示typeALL全表扫描。原因前导%让 B 树索引失效。MySQL 的普通索引只能优化LIKE 梦见%这种前缀匹配对包含匹配无能为力。解决如果只是按关键词找走dream_keywords表做等值查询如果必须全文模糊搜用第 4 节提到的 ngram 全文索引。还有一个备选是把内容倒排到 ES 或向量数据库但对这份数据量级属于过度设计MySQL 全文索引已经够用。5.4 解法文本和关键词对不上查出来“张冠李戴”现象搜“蛇”出来的解法跟原始文本里写的不是同一句甚至出现“梦见蛇解作大吉”和“梦见蛇解作大凶”并存。原因数据源本身版本混杂。周公解梦在不同刻本、网络转载里口径差异很大有的条目连断句都不一样如果清洗时没保留来源标识后面合并版本就会互相覆盖。解决在dreams和interpretations表里维护source_version字段导入时按原始文件填好查询时如果需要唯一口径就加WHERE source_version v1。另外入库前做一次文本 diff把相同content但不同解法的条目捞出来人工看一遍。这份数据本质是历史文本不是标准答案别期望它内部自洽。6. 把数据做成可调用的解梦服务视图封装、接口与检索验证前面四张表用着顺手但每次查询都要写四表 JOIN接口层会烦。我先包装成一个视图把常用查询固定下来CREATE VIEW v_dream_interpret AS SELECT d.content AS dream_text, i.content AS interpret_text, k.keyword, k.category FROM dreams d JOIN interpretations i ON i.dream_id d.id LEFT JOIN dream_keywords dk ON dk.dream_id d.id LEFT JOIN keywords k ON k.id dk.keyword_id;然后是最小可用的 FastAPI 接口查询直接打在视图上from fastapi import FastAPI import pymysql app FastAPI() def get_conn(): return pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedream_db, charsetutf8mb4, ) app.get(/api/dream) def dream(keyword: str): conn get_conn() with conn.cursor() as cur: cur.execute( SELECT dream_text, interpret_text, category FROM v_dream_interpret WHERE keyword %s, (keyword,), ) rows cur.fetchall() conn.close() return {keyword: keyword, count: len(rows), results: rows}验证方式很直接curl http://127.0.0.1:8000/api/dream?keyword蛇看返回 JSON 的count和results是否符合预期。还要顺手测一下耗时time curl能看出接口有没有走索引如果count正常但耗时超过 100ms回头EXPLAIN看视图内部的 JOIN 是否命中了索引。别小看这几步视图只是把查询藏起来索引问题并不会消失。这套结构跑通以后我遇到过的最大的错觉是“要不要上向量数据库做语义推荐”。说实话几千条结构化文本MySQL 的等值查询加全文检索已经覆盖九成需求真有语义相似推荐那天再考虑 embedding 加向量库也不迟别一开始就给自己加复杂度。这是我在做数据服务时最深刻的教训把查询、索引和接口做好比设备先进重要得多。希望帮到你。本文还有配套的精品资源点击获取
简介周公解梦数据集将传统梦境文化与数据库技术结合收录约7261条梦境解析记录适合数据分析师、传统文化研究者、心理学爱好者以及前端/后端开发者使用可用于梦境心理学研究、文化数据挖掘或搭建趣味查询应用。资源共4个文件压缩包仅3.84MB分别提供JSON、SQL、CSV、XLSX四种格式JSON适合Web服务与前后端交互SQL可通过标准查询实现主题筛选和统计CSV便于跨工具导入导出XLSX则支持排序、过滤与图表可视化。目前已有612人浏览学习。借助这套数据读者既能按关键词快速检索梦境释义又能开展梦境主题频次与情感倾向的关联分析若需构建解梦查询系统可直接导入SQL或JSON文件同样适合在教学演示或文化类应用中作为示例数据是一份小巧实用的中文数据集。1. 数据库-周公解梦数据集从一张梦单到可检索的中文文本库「数据库-周公解梦数据集」听起来像一张“梦见什么、解成什么”的对照表但真拿到手你会发现它是半结构化的中文文本语料条目里有“梦见蛇主财”这样的短句也有“梦见掉牙主亲属有难”这类带情感倾向的长描述。我把它整理成 MySQL 表之后最大的价值不是拿去做玄学解读而是让检索、接口调用、小程序搜索都能直接拿数据说话。这篇笔记写给两类人做内容站、闲聊机器人、运势类 App 的开发以及刚入门想把一份现成数据变成关系型库的从业者。下面是建表、清洗、导库、排错的完整路径照着跑就能用起来。2. 先建模再入库把梦境条目拆成四张表2.1 单表宽表为何是坑一条“梦蛇”对应七种解释范式拆表才查得动周公解梦的原始数据常见有两种形态一种是纯文本每行一条“梦见某物某解法”一种是 CSV 或 Excel左列梦境、右列解法。无论哪种都有一个共性同一梦境内容在不同来源里对应多条解法而且彼此语义冲突。比如“梦见蛇”在古本里解作“主财”在现代网络版里可能解作“防小人”甚至同一个来源里两条记录都叫“梦见蛇”解法却完全不同。如果按直觉做成一张宽表dreams id | content | interpretation那“梦见蛇”这一行就得重复两三遍后续要加“来源版本号”“吉凶倾向”字段时表会越改越歪。更麻烦的是如果你想按“动物”“器物”“自然现象”做主题聚合宽表只能在 content 上做 LIKE 硬刷查一次慢一次。所以我一般拆成四张表梦境主表存“梦见蛇”这种原文解释表存解法文本关键词表做主题分类再加一张关联表把梦境和关键词做成多对多。这样一条梦挂多条解释、一个关键词挂多组梦都只需要加行不需要改表结构。方案主要字段适合场景典型问题单表宽表content, interpretation一次性查看一条梦多解法没法表达重复存储单表加扩展列content, interp1, interp2解法固定两条条目之间列数不齐难扩展四表范式dreams, keywords, relations, interpretations检索、接口、分类统计写入逻辑略复杂但查询利索2.2 字段选型utf8mb4、TEXT 与 content_hash 的取舍建表前最容易翻车的不是主键是字符集。MySQL 里utf8其实不是完整 UTF-8它叫 utf8mb3存不了 Emoji 和部分生僻字。解梦数据里“魇”“龋”“殍”这类字出现频率不低所以字符集直接上utf8mb4排序规则我选utf8mb4_unicode_ci对中文这种多音字场景比 general_ci 稳一点。字段长短也有讲究。梦境内容大多是短句二三十字封顶VARCHAR(500)够用解法文本就不一定了有的条目会带一整段“宜忌”“方位”“时辰”得用TEXT。真正容易被忽略的是去重content最长 500 字符没法直接加唯一索引我习惯加一列content_hash用 MD5 生成列把它设成唯一键。这个字段后面导入清洗时会反复用到。2.3 建表 SQL直接可跑的 MySQL 8.0 DDL脚本-- 梦境主表一条“梦见蛇”只在这里出现一次 CREATE TABLE dreams ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content VARCHAR(500) NOT NULL COMMENT 梦境内容如“梦见蛇”, content_hash CHAR(32) GENERATED ALWAYS AS (MD5(content)) STORED, source_version VARCHAR(50) NULL COMMENT 数据来源版本方便追溯, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_content_hash (content_hash) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 关键词表用来做主题分类比如 动物/器物/自然 CREATE TABLE keywords ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, keyword VARCHAR(100) NOT NULL COMMENT 关键词如“蛇”, category VARCHAR(50) NULL COMMENT 主题分类, UNIQUE KEY uk_keyword (keyword) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 梦境与关键词关联表 CREATE TABLE dream_keywords ( dream_id BIGINT UNSIGNED NOT NULL, keyword_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (dream_id, keyword_id), KEY idx_keyword_id (keyword_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 解释表一条梦可以挂多条解法 CREATE TABLE interpretations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dream_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL COMMENT 解法原文, fortune_level TINYINT NULL COMMENT 吉凶倾向1吉 2平 3凶, source_version VARCHAR(50) NULL COMMENT 来源版本, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这段 DDL 里最值得说的是content_hash它用的是 MySQL 8.0 的生成列MD5(content)一旦写入就不会因为后续修改 content 而失配。唯一索引加在这一列上既避免了 TEXT 字段加索引的长度限制又让重复检查从万行级全表扫描变成一次索引查找。主键用BIGINT UNSIGNED而不是INT看起来杀鸡用牛刀但数据合并、多实例同步时不用再回头改主键类型。如果你后续要接人大金仓、TiDB 这类兼容 MySQL 的数据库这套 DDL 改动也很小。3. 数据导入把原始解梦文本清洗成 CSV再灌进 MySQL3.1 预处理用正则把“梦见蛇主财”拆成结构化字段拿到原始数据常见格式是每行一条梦见蛇主财宜远行 梦见掉牙:主亲属有难 梦见大水主财源滚滚注意分隔符并不统一全角冒号、半角冒号、逗号都有。我一般先做一步归一化把所有分隔符换成统一标记再拆字段。这里直接给一段可跑的 Python 脚本import re, csv src_path 周公解梦原始.txt rows [] with open(src_path, encodingutf-8) as f: for line in f: line line.strip() if not line: continue # 把全角冒号统一成半角避免正则写两遍 line line.replace(, :) # 只在第一个冒号处拆分防止解法文本里再出现冒号 parts line.split(:, 1) if len(parts) 2: continue content parts[0].strip() interp parts[1].strip() rows.append([content, interp]) with open(dreams_clean.csv, w, encodingutf-8, newline) as f: writer csv.writer(f) writer.writerow([content, interpretation]) writer.writerows(rows) print(f清洗完成共 {len(rows)} 条)这段代码的关键是split(:, 1)限制只拆第一次出现的位置否则像“梦见蛇主财宜远行”这种条目会被拆成三段。如果你的原始文件是 Excel 导出的先另存为 CSV 并选 UTF-8 编码别直接读 .xlsx否则后面连接数据库时十有八九会撞上编码问题。另外要检查原始文件是不是用 Tab 分隔的line.split(\t, 1)就得替换掉冒号拆分逻辑。3.2 批量写入executemany 与连接参数怎么设清洗完只是第一步真正容易出问题的是写入。数据量通常不大但重复执行导入脚本会插入重复行所以我先建立关键词映射再按行写库import pymysql, csv conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedream_db, charsetutf8mb4, # 连接字符集必须显式指定 autocommitFalse, # 关闭自动提交统一管理事务 ) cur conn.cursor() # 把已有的关键词加载进内存避免逐条查库 cur.execute(SELECT keyword, id FROM keywords) kw_map {row[0]: row[1] for row in cur.fetchall()} with open(dreams_clean.csv, encodingutf-8) as f: reader csv.reader(f) next(reader) # 跳过表头 batch [] for content, interp in reader: # 用 content_hash 快速查重避免重复插入 cur.execute(SELECT id FROM dreams WHERE content_hash MD5(%s), (content,)) row cur.fetchone() if row: dream_id row[0] else: cur.execute(INSERT INTO dreams (content, source_version) VALUES (%s, v1), (content,)) dream_id cur.lastrowid cur.execute( INSERT INTO interpretations (dream_id, content) VALUES (%s, %s), (dream_id, interp), ) batch.append(dream_id) # 每 200 条提交一次避免长事务锁表 if len(batch) 200: conn.commit() batch.clear() conn.commit() cur.close() conn.close() print(导入完成)参数说明charsetutf8mb4必须写漏掉它即使表结构是对的Python 客户端读出来也可能乱码。autocommitFalse配合每 200 条一次commit()是为了防止在中途出错时留下半个事务的脏数据如果你用 Navicat、dbx 这类数据库工具做导入工具内部也是同样的批处理逻辑。这里没做关键词关联真正落地时还要把每行文本拆词后写入dream_keywords拆词可以用 jieba也可以直接从interpretations的文本里按规则抽但别小看这一步它是后续按主题统计的基础。3.3 导入后校验行数、重复率、空字段三个指标导入完别急着写接口先用三条 SQL 做体检SELECT COUNT(*) AS total_dreams FROM dreams; SELECT COUNT(*) AS total_interpretations FROM interpretations; -- 重复率正常应为 0 SELECT content_hash, COUNT(*) FROM dreams GROUP BY content_hash HAVING COUNT(*) 1; -- 空字段检查两条结果都应为 0 SELECT COUNT(*) FROM dreams WHERE content IS NULL OR content ; SELECT COUNT(*) FROM interpretations WHERE content IS NULL OR content ;行数对不上原始文件是最常见的情况原因通常是预处理阶段的正则漏掉了分隔符不标准的那几行。我习惯在清洗脚本里加个计数再和原始总行数对比如果差得不多直接用 grep 把漏掉的行捞出来手工修。4. 把数据用起来增删改查、全文检索与统计4.1 按关键词查解法等值查询与 JOIN 的正确姿势四表结构的好处在这时候才体现出来。想知道“梦见蛇”的所有解法一条 JOIN 就能拉出来SELECT d.content AS dream_text, i.content AS interpret_text, k.category FROM dreams d JOIN interpretations i ON i.dream_id d.id LEFT JOIN dream_keywords dk ON dk.dream_id d.id LEFT JOIN keywords k ON k.id dk.keyword_id WHERE k.keyword 蛇;这里用LEFT JOIN而不是INNER JOIN是因为有些梦还没拆出关键词避免漏掉解法记录。WHERE k.keyword 蛇走的是uk_keyword唯一索引数据量上万也是毫秒级返回。刚入门的人最容易在这个阶段想着“顺便”加一句AND d.content LIKE %蛇%这一加就把整条 SQL 的索引优势抹掉了改成全表扫描。4.2 中文全文检索ngram 解析器与两个必调参数真正需要模糊搜的是用户输入“梦见掉了一颗牙”而库里存的是“掉牙”。这种场景LIKE %牙%能出结果但LIKE %掉牙%就会漏。MySQL 从 5.7 起内置了全文索引但对中文默认分词并不友好必须显式指定 ngram 解析器ALTER TABLE dreams ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT id, content FROM dreams WHERE MATCH(content) AGAINST(掉牙 IN NATURAL LANGUAGE MODE);ngram_token_size要提前在 my.cnf 里配好我设的是 2也就是按双字切词。这个参数在 MySQL 8.0 里属于只读参数改完必须重启实例而且已经建好的全文索引要重建才能生效。如果表不大ALTER TABLE dreams DROP INDEX ft_content, ADD FULLTEXT INDEX ... WITH PARSER ngram;直接重建也行。注意IN NATURAL LANGUAGE MODE对短词召回少想扩大召回可以换IN BOOLEAN MODE代价是结果里噪音会多一些。4.3 统计聚合按梦境主题看数据分布运营或产品经常要看“动物类梦有多少条”“哪类解法最多”这类需求直接 GROUP BY 就行SELECT k.category, COUNT(*) AS cnt FROM dream_keywords dk JOIN keywords k ON k.id dk.keyword_id GROUP BY k.category ORDER BY cnt DESC;如果keywords.category还没填可以先跑一条 UPDATE 按关键词粗略归类比如UPDATE keywords SET category动物 WHERE keyword IN (蛇,狗,猫,鱼)。这一步其实也是数据清洗的一部分不是靠数据库自动完成的。4.4 增删改查与事务回滚数据库常用命令的边界增删改查是基本功但解梦数据有个特殊场景删除一条梦时必须把它在interpretations和dream_keywords里的关联数据也清掉否则就成了孤儿数据。我把这三条操作放进一个事务START TRANSACTION; DELETE FROM interpretations WHERE dream_id 100; DELETE FROM dream_keywords WHERE dream_id 100; DELETE FROM dreams WHERE id 100; -- 如果第二步报错回滚到事务开始前 ROLLBACK; -- 确认无误后提交 COMMIT;日常维护常用的命令就这几个没必要全背命令作用SHOW TABLES;查看所有表DESC dreams;查看表结构SHOW INDEX FROM dreams;查看索引状态EXPLAIN SELECT ...查看执行计划判断是否走了索引SHOW PROCESSLIST;查看是否有卡住的连接EXPLAIN是最值得养成的习惯。我每次写新查询只要结果集超过几百行就会先EXPLAIN看一眼key列如果显示NULL说明在扫全表得马上回头改 SQL。5. 常见问题与避坑乱码、重复、慢查询与来源偏差5.1 全库都设了 utf8mb4客户端读出还是乱码现象表结构DEFAULT CHARSETutf8mb4用命令行查没问题但 Python 读出来全是????。原因连接层字符集没对齐。MySQL 的字符集分三层库表结构、连接、客户端。表结构对不代表连接对Python 的 pymysql 如果不显式传charset默认可能落到latin1。解决连接串里显式加上charsetutf8mb4命令行就执行SET NAMES utf8mb4;。如果已经有乱码数据先备份再执行ALTER TABLE dreams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意CONVERT会重写整表几万行没事几百万行要挑业务低峰期做。5.2 导入脚本跑两遍数据多出一倍现象SELECT COUNT(*) FROM dreams的结果比原始文件行数还多用content_hash一查重复率不是 0。原因清洗脚本没有幂等性。第一遍导入成功第二遍跑的时候旧数据没删新数据直接追加进去。我最早也踩过这个坑后来才想起在content_hash上加唯一索引。解决ALTER TABLE dreams ADD UNIQUE KEY uk_content_hash (content_hash);然后把 INSERT 改成INSERT IGNORE INTO dreams ...或者干脆在导入脚本开头先TRUNCATE TABLE interpretations; TRUNCATE TABLE dream_keywords; TRUNCATE TABLE dreams;保证每次导入都是干净的。5.3 用 LIKE 搜“梦见”越来越慢现象几千条数据时WHERE content LIKE %梦见%毫秒级返回数据量过万后明显变慢EXPLAIN 显示typeALL全表扫描。原因前导%让 B 树索引失效。MySQL 的普通索引只能优化LIKE 梦见%这种前缀匹配对包含匹配无能为力。解决如果只是按关键词找走dream_keywords表做等值查询如果必须全文模糊搜用第 4 节提到的 ngram 全文索引。还有一个备选是把内容倒排到 ES 或向量数据库但对这份数据量级属于过度设计MySQL 全文索引已经够用。5.4 解法文本和关键词对不上查出来“张冠李戴”现象搜“蛇”出来的解法跟原始文本里写的不是同一句甚至出现“梦见蛇解作大吉”和“梦见蛇解作大凶”并存。原因数据源本身版本混杂。周公解梦在不同刻本、网络转载里口径差异很大有的条目连断句都不一样如果清洗时没保留来源标识后面合并版本就会互相覆盖。解决在dreams和interpretations表里维护source_version字段导入时按原始文件填好查询时如果需要唯一口径就加WHERE source_version v1。另外入库前做一次文本 diff把相同content但不同解法的条目捞出来人工看一遍。这份数据本质是历史文本不是标准答案别期望它内部自洽。6. 把数据做成可调用的解梦服务视图封装、接口与检索验证前面四张表用着顺手但每次查询都要写四表 JOIN接口层会烦。我先包装成一个视图把常用查询固定下来CREATE VIEW v_dream_interpret AS SELECT d.content AS dream_text, i.content AS interpret_text, k.keyword, k.category FROM dreams d JOIN interpretations i ON i.dream_id d.id LEFT JOIN dream_keywords dk ON dk.dream_id d.id LEFT JOIN keywords k ON k.id dk.keyword_id;然后是最小可用的 FastAPI 接口查询直接打在视图上from fastapi import FastAPI import pymysql app FastAPI() def get_conn(): return pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedream_db, charsetutf8mb4, ) app.get(/api/dream) def dream(keyword: str): conn get_conn() with conn.cursor() as cur: cur.execute( SELECT dream_text, interpret_text, category FROM v_dream_interpret WHERE keyword %s, (keyword,), ) rows cur.fetchall() conn.close() return {keyword: keyword, count: len(rows), results: rows}验证方式很直接curl http://127.0.0.1:8000/api/dream?keyword蛇看返回 JSON 的count和results是否符合预期。还要顺手测一下耗时time curl能看出接口有没有走索引如果count正常但耗时超过 100ms回头EXPLAIN看视图内部的 JOIN 是否命中了索引。别小看这几步视图只是把查询藏起来索引问题并不会消失。这套结构跑通以后我遇到过的最大的错觉是“要不要上向量数据库做语义推荐”。说实话几千条结构化文本MySQL 的等值查询加全文检索已经覆盖九成需求真有语义相似推荐那天再考虑 embedding 加向量库也不迟别一开始就给自己加复杂度。这是我在做数据服务时最深刻的教训把查询、索引和接口做好比设备先进重要得多。希望帮到你。本文还有配套的精品资源点击获取
