成语库三格式解析：CSV、SQL与TXT的导入和清洗实战

简介中华成语数据库是一份收录31851条成语的综合语料涵盖拼音、释义、出处与示例多数条目附有典故来源适合语言学研究者、语文教师、学生以及中文信息处理开发者在教学、检索和文本挖掘时使用。压缩包共3个文件大小约6.63MB分别提供SQL脚本、CSV表格和TXT纯文本三种存储形态SQL脚本可用于在关系数据库中直接建表与查询CSV便于导入Excel进行多维度筛选统计TXT则适合快速阅读或二次处理。目前已有1058人学习下载适合作为成语知识库建设的底层数据。借助其中的SQL数据可以检索源自《论语》等典籍的成语也可按关键字统计分布CSV与文本文件则降低了使用门槛方便做教学讲义、语料分析乃至文化研究将读音、释义、出处和用例串联成结构化信息是理解汉语典故与传统文化的一块实用基石。 解压这个压缩包后目录里就三个文件cysj.csv、cysj.sql、CYSJ.txt。31851 个成语同一份数据用三种格式各存一遍这不是冗余而是对应三条不同的消费路径CSV 可直接丢进 Excel 和 PandasSQL 脚本灌进 MySQL 后做条件查询和统计TXT 文件则适合当成语料跑分词、全文索引或离线校对。相比很多开源成语库只给“成语解释”两列的做法这份库把拼音、出处、例子都补齐了对正在做语文课程设计、成语接龙小程序、按拼音查词的教学系统或者只是想拿一份带读音词表的人来说能省掉大量人工录入和校对的时间。下面从字段设计和文件内部结构展开。2. 成语库字段设计CSV 分隔、编码与 SQL 建表字符集2.1 六列结构的职责划分先把 cysj.csv 解压出来不要急着导入找任意文本编辑器打开第一行看表头。常见导出结构是六列字段名可能稍有差异但职责是一一对应的字段类型建议是否可空说明示例idINT 自增否唯一主键1chengyuVARCHAR(32)否成语正文画蛇添足pinyinVARCHAR(128)可空带声调全拼字间用空格分隔huà shé tiān zújieshiTEXT可空成语释义比喻做多余的事反而不恰当chuchuVARCHAR(255)可空出处文献《战国策·齐策二》liziTEXT可空应用例句明明已经办妥再改动就是画蛇添足拼音单独拆成一列而不是在查询时临时拼出来这是整个库设计里最关键的决定。按音查字、按韵脚接龙、按声调排序全部直接走这一列不需要引入第三方拼音库。出处和例子允许为空摘要里“大多数还包括出处和例子”已经说明这两列存在 NULL数据处理时按可空设计建表不要加 NOT NULL。2.2 CSV 读取的编码与转义处理CSV 文件最常见的坑集中在两处编码和字段内转义。Excel 直接打开 UTF-8 无 BOM 的 CSV 会中文乱码这是老问题解法是让文件以 UTF-8-SIG 保存也就是带 BOM 的 UTF-8。另一个问题是解释和例句里可能本身包含逗号、引号甚至换行标准的 CSV 会用双引号包裹整个字段内部引号再翻倍转义这时候用split(,)去切必定出错。# 1) 探测编码先读文件头部字节交给 chardet 判断 import chardet with open(cysj.csv, rb) as f: raw f.read(4096) enc chardet.detect(raw)[encoding] print(detected:, enc) # utf-8-sig 或 gbk 都有可能 # 2) 用探测结果读取并固定 id 为 int32 import pandas as pd df pd.read_csv(cysj.csv, encodingenc, dtype{id: int32}) print(df.head(3)) print(df.shape) # 第一个值应等于 31851chardet 是第三方库需要先pip install chardet。它读的是文件前 4KB 字节依靠特征推断编码中文数据下通常能识别出 utf-8-sig 或 gbk如果结果返回 ascii说明样本里恰好没有高位字节以肉眼检查中文行是否乱码为准。dtype{id: int32}防止 pandas 把纯数字主键读成 int64 白白占内存shape的第一个值应该等于 31851如果小于这个数说明有字段里的逗号或换行被错误截断需要检查quotechar参数是否默认的。2.3 SQL 脚本的建表结构字符集选 utf8mb4 的理由cysj.sql 里是建表语句和 INSERT 数据。导入前先看头部常见结构与下面的模板接近实际字段名以脚本为准CREATE TABLE cysj ( id INT NOT NULL AUTO_INCREMENT, chengyu VARCHAR(32) NOT NULL, pinyin VARCHAR(128) DEFAULT NULL, jieshi TEXT, chuchu VARCHAR(255) DEFAULT NULL, lizi TEXT, PRIMARY KEY (id), KEY idx_chengyu (chengyu), KEY idx_pinyin (pinyin(32)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;字符集必须用 utf8mb4这一点不能省。成语库里生僻字多部分字落在 CJK 扩展区老版本的 utf8mb3也就是日常说的 utf8一个字符最多 3 字节存不下扩展区字符导入时会报错或者落成问号。COLLATE 选utf8mb4_unicode_ci让中文按 Unicode 排序而不是按二进制排序拼音列做字典序浏览时更自然。索引方面idx_chengyu覆盖精确匹配idx_pinyin(32)是前缀索引只对拼音列前 32 个字符建索引节省空间的同时能支持LIKE ma%这类前缀查询但没法支持对拼音任意位置的模糊匹配。3. cysj.sql 导入 MySQL命令行与 Navicat 双向验证3.1 导入前的脚本头部检查拿到 SQL 文件直接灌库是新手最容易翻车的地方。先用 head 看脚本前 40 行确认三件事表名是什么、有没有DROP TABLE IF EXISTS语句、字符集有没有SET NAMES。# 先看脚本头部表名、字符集、有没有 DROP TABLE head -n 40 cysj.sql # 建库并指定字符集 mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS chengyu DEFAULT CHARSET utf8mb4; # 带字符集参数导入避免中文变成乱码 mysql -uroot -p --default-character-setutf8mb4 chengyu cysj.sqlhead只是查看不修改文件。如果脚本里有DROP TABLE IF EXISTS cysj执行导入前确认这个库里没有你要保留的同名表或者手动把那一行注释掉。--default-character-setutf8mb4是命令行导入最容易漏的参数不指定时客户端可能按 latin1 传输中文数据进库后全部变成乱码而且这种乱码很难通过后续 ALTER 修复只能删表重导。3.2 Navicat 导入路径与 max_allowed_packet不用命令行的场景Navicat 导入路径是新建查询或者右键数据库选择“运行 SQL 文件”。31851 条 INSERT 的脚本体积不大但 Navicat 默认参数可能挡路失败现象常见原因解决方式提示 DROP 权限不足脚本含 DROP TABLE账号无 DROP 权限用 root 运行或删掉脚本中的 DROP 语句导入成功但中文乱码建库字符集不是 utf8mb4或连接参数未指定编码建库时指定DEFAULT CHARSET utf8mb4连接属性里设置字符集报 max_allowed_packet 超限单条 INSERT 过长超过服务器包上限SET GLOBAL max_allowed_packet64M;后重连max_allowed_packet是 MySQL 服务器允许接收的最大数据包批量 INSERT 一条语句可能包含几百行数据超过默认 4M 就会中断。改成 64M 后要重开连接才生效这个参数在课程设计答辩现场属于高频事故点提前设置好能省不少事。3.3 条数验证与三道课程设计级查询导入完成后第一件事不是查成语而是验证数据完整性。总数必须是 31851少了就是导入过程中有 SQL 报错被跳过空值分布能直接验证摘要里“大多数还包括出处和例子”的说法。USE chengyu; -- 总数和空值分布一条 SQL 同时出结果 SELECT COUNT(*) AS total, SUM(IFNULL(chuchu, ) ) AS no_chuchu, SUM(IFNULL(lizi, ) ) AS no_lizi FROM cysj; -- 找出处带《论语》的条目 SELECT chengyu, pinyin, chuchu FROM cysj WHERE chuchu LIKE %论语% LIMIT 20;SUM(IFNULL(chuchu, ) )利用 MySQL 布尔表达式返回 0 或 1 的特性把空值统计写成聚合函数。IFNULL 先把 NULL 转成空串再和空串比较这样 NULL 和空字符串都被算作缺失。第二句查询里的LIKE %论语%是模糊匹配%放在两侧表示子串命中如果 chuchu 列存在 NULL这一行不会被命中所以前面要先做空值统计心里有数再看结果。再补充两个常用查询。按字数筛选用CHAR_LENGTH(chengyu) 4注意不是LENGTHLENGTH在 utf8mb4 下返回字节数一个汉字占 3 到 4 字节四字成语用LENGTH判断会得到 12 或 16永远是错的。随机抽查用ORDER BY RAND() LIMIT 10数据量只有 3 万多时性能无所谓但到百万级就不能这么写那是另一个话题。3.4 数据备份用 mysqldump 而不是拷贝文件课程设计要做到能跑、能恢复备份这条不能省。直接复制 MySQL 数据目录里的 ibd 文件属于错误操作MySQL 8.0 的表空间和 redo log 是配套的文件级拷贝在服务器重启后经常报损坏。标准做法是mysqldump -uroot -p --default-character-setutf8mb4 chengyu cysj cysj_backup.sqlmysqldump参数第一位是库名第二位是表名导出的文件可以直接用第三章开头的方式重新导入。加上--default-character-setutf8mb4保证导出的 SQL 文件里中文编码正确这个参数在导入导出两侧都要加只加一侧仍然会乱码。4. CSV 与 TXT 交叉校验Pandas 清洗与空值分析4.1 先摸清 CYSJ.txt 的行结构CYSJ.txt 表面上是纯文本但不要上来就写解析正则。先看前几行head -n 5 CYSJ.txt常见形态有两种一种是一行一个成语只包含成语字段另一种是一行一条完整记录各字段用制表符或空格分隔。用 Python 按行读取并用分隔符分割with open(CYSJ.txt, encodingutf-8-sig) as f: for i in range(5): line f.readline().rstrip(\n) print(repr(line)) # repr 能看到隐藏的制表符和尾随空格repr()会把\t显示成转义序列一眼能看出分隔符到底是 tab、逗号还是多个空格。这一步的价值在于确定解析策略和 csv 不同txt 没有标准的引号转义机制字段里如果出现分隔符解析结果就会错位所以先看结构再定方案。4.2 重复成语与 ID 连续性检查CSV 是干净数据的最大头但 31851 条由人工整理的数据里重复和缺漏是必然存在的做好数据质量分析再谈使用。import pandas as pd df pd.read_csv(cysj.csv, encodingutf-8-sig, dtype{id: int32}) # 重复成语检查keepFalse 把重复行全部留下 dup df[df.duplicated(subset[chengyu], keepFalse)] print(重复行数:, len(dup)) # 空值分布 print(df.isna().sum()) df[chuchu] df[chuchu].fillna(未考) df[lizi] df[lizi].fillna() print(df[chengyu].nunique())duplicated(subset[chengyu], keepFalse)检测成语列重复keepFalse意味着所有重复行都保留方便肉眼核对是整行重复还是同一个成语有不同解释。isna().sum()得到每列空值数量直接对应摘要里“大多数还包括出处和例子”的具体比例这个数字能在课程设计报告里当作数据说明。.fillna()处理空值出处填“未考”例子填空串。为什么不用dropna()删行因为出处缺失不代表成语无效删行会白白丢掉可用数据填充占位在展示层更稳妥。4.3 三份文件一致性核对三个文件理论上应该完全同步但实际整理时可能 CSV 更新过、SQL 没重新导出或者反过来。核对方法是从数据库导出成语列和 CSV 里的成语集合做差集mysql -uroot -p -N -e SELECT chengyu FROM chengyu.cysj from_db.txt-N跳过列名输出避免表头混进数据。然后db_set set() with open(from_db.txt, encodingutf-8) as f: for line in f: db_set.add(line.strip()) csv_set set(df[chengyu]) print(CSV 有而数据库无:, len(csv_set - db_set)) print(数据库有而 CSV 无:, len(db_set - csv_set))两个差集同时为 0才说明三份文件一致。只要有差值就要决定以哪份为准一般以 SQL 为准因为它是导入 MySQL 后经过验证的版本CSV 和 TXT 可以用 Python 脚本重新导出避免继续带着脏数据跑。这一步在课程设计的“数据来源与预处理”章节里是非常好的亮点。5. 拼音字段实战成语接龙与检索降级技巧5.1 用尾字拼音做 SQL 自连接接龙拼音列最大的用途就是成语接龙。接龙规则是前一个成语的尾字读音匹配后一个成语的首字读音。用 SQL 自连接可以一次查出所有后继-- 以马到成功为起点找尾字读音接得上的成语 SELECT c2.chengyu, c2.pinyin FROM cysj c1 JOIN cysj c2 ON SUBSTRING_INDEX(c1.pinyin, , -1) SUBSTRING_INDEX(c2.pinyin, , 1) WHERE c1.chengyu 马到成功;SUBSTRING_INDEX(str, , -1)取拼音列按空格分割后的最后一段也就是尾字的拼音SUBSTRING_INDEX(str, , 1)取第一段也就是首字的拼音。两张表通过拼音相等做连接就能得到所有能接上的成语。这条 SQL 在数据集上执行没问题但有一个前置条件拼音列的声调记法必须统一。如果数据里存的是带声调符号的gōng而你的匹配值是不带声调的gong永远不成立。接龙场景里声调无关紧要批量降级即可-- 把常见的带声调字母降级覆盖 ā á ǎ à 四种声调 UPDATE cysj SET pinyin REPLACE(REPLACE(REPLACE(REPLACE(pinyin, ā,a), á,a), ǎ,a), à,a);执行前先确认库里到底是声调符号还是数字声调gong1这类。数字声调不能直接用上面的 SQL要先把数字去掉或转成符号再替换否则gong1和gong2会被当成不同读音。这个 UPTATE 只覆盖a的四种声调实际数据里还有ō ó ǒ ò、ē é ě è按同一模式补全或者用正则函数统一处理。5.2 预计算首尾拼音列检索降级的关键每次都跑SUBSTRING_INDEX不是不行但在应用层做联想输入或接龙接口时反复切字符串不划算。更优的做法是在表里预计算三个派生列派生列类型说明py_headCHAR(1)首字拼音的首字母用于字母表索引first_pyVARCHAR(64)首字完整拼音last_pyVARCHAR(64)尾字完整拼音用一次 UPDATE 填充之后所有查询都走这三列查询语句从字符串切割变成等值连接命中索引后速度提升明显。对 31851 条数据来说这个优化感知不强但同样的表结构放到百万级成语扩展库或者接龙连续递归场景时省下的 CPU 时间非常可观。拿这套词库做联想输入时把chengyu、first_py、last_py直接加载进内存数组按前缀匹配即可数据量再涨再考虑 Redis 的 Sorted Set 做前缀补全那时候 SQL 层的预计算列依然是最稳定的数据源。本文还有配套的精品资源点击获取