简介这份资源是一套覆盖十万量级的英语单词翻译数据库面向需要批量查询、离线检索或自建词典应用的开发者与英语学习者。库表结构包含英文单词、中文翻译、词性及多种词义可直接执行SQL语句生成数据表兼容SQL Server、MySQL等多种数据库也支持通过phpMyAdmin直接导入省去手工整理词条的繁琐过程。压缩包共8个文件以sql导入脚本、csv数据文件和png预览图为主另有md说明文档整体约4.63MB体积轻便却承载了十万余条词条记录。目前已有1610人学习下载说明其在词典类数据需求中具有一定参考价值。读者拿到后既能快速搭建本地词库用于背单词工具、翻译查询或数据挖掘练习也能借助csv与Excel格式灵活筛选、二次加工适合作为英语学习辅助或数据库入门练手素材。1. 十万词条翻译库落地为什么我选 SQL CSV Excel 三件套手上接到一个需求给一批英文技术文档做本地化预处理需要一份能离线查询、能批量替换、还能给非技术同事直接翻看的英汉词条库。市面上下载的词典要么是加密的 mdx要么是几百兆的二进制文件想按词频筛选、想批量导出、想接进自己的脚本处处卡壳。折腾一圈之后我决定自己攒一份干净的词条数据规模定在十万级——103976 个英文单词配上中文释义同时输出 SQL、CSV、Excel 三种格式。这个量级很微妙。再小Excel 直接打开就能用没必要上数据库再大比如百万级Excel 就彻底歇菜只能走 SQL。十万条刚好卡在中间SQL 能秒查CSV 能喂给脚本Excel 能给人看。三种格式各管一段谁也别想替代谁。这篇笔记就把我踩过的路讲清楚——数据从哪来、三种格式怎么生成、导入时哪些参数必须调、以及那些让我加班到凌晨的坑。如果你也在做词库、术语表、多语言对照表这类东西下面的内容可以直接抄。2. 词条数据从哪来源文件清洗与字段设计2.1 先想清楚字段再动手清洗拿到原始词条时最常见的形态是一行一条的纯文本英文和中文之间用制表符或连续空格分隔。但真实数据往往更脏有的行混进了音标有的释义里带了例句有的单词重复出现但释义不同。我一般先做一轮字段设计把表结构定死再倒推清洗规则。核心字段就四个id、word、meaning、frequency。id用自增整数方便后续做关联和去重word存英文原词统一转小写并去掉首尾空白meaning存中文释义保留原始分隔符但去掉多余空格frequency是词频用来排序和筛选高频词。如果原始数据没有词频可以用公开的英文词频表做一次左连接补上没有匹配到的填 0。字段定好之后清洗规则也就清楚了。下面这段 Python 是我常用的清洗骨架处理十万行大概十几秒import re import csv def clean_line(raw): # 去掉首尾空白和不可见字符 line raw.strip().replace(\u200b, ) if not line: return None # 按制表符或两个以上空格切分只取前两段 parts re.split(r\t|\s{2,}, line, maxsplit1) if len(parts) 2: return None word parts[0].strip().lower() meaning parts[1].strip() # 过滤掉纯符号或纯数字的“单词” if not re.search(r[a-z], word): return None return word, meaning with open(raw_dict.txt, r, encodingutf-8) as fin, \ open(clean_dict.csv, w, encodingutf-8, newline) as fout: writer csv.writer(fout) writer.writerow([word, meaning]) seen set() for line in fin: result clean_line(line) if result and result[0] not in seen: seen.add(result[0]) writer.writerow(result)这段代码的关键在re.split那一行。原始数据里英文和中文之间可能是制表符也可能是多个空格用\t|\s{2,}能同时覆盖两种情况。maxsplit1保证只切一刀避免释义里本身带空格时被切碎。seen集合做去重保留第一次出现的释义这是最稳妥的策略——同一单词的多个释义如果都要保留应该改成一对多表结构但十万级词库通常不需要。清洗完先别急着导库用wc -l看一眼行数再用head和tail抽查首尾确认没有乱码和截断。这一步花两分钟能省后面两小时。2.2 词频字段怎么补补不上怎么办词频不是必须的但有了它查询和排序会方便很多。我一般用两个来源补一是原始数据自带的频次标记二是公开的英文词频表。补的时候用word做左连接SQL 里就是LEFT JOINPython 里就是字典映射。# 假设 freq_map 是从词频表读进来的 {word: frequency} import csv freq_map {} with open(freq_table.csv, r, encodingutf-8) as f: reader csv.reader(f) for row in reader: if len(row) 2: freq_map[row[0].lower()] int(row[1]) with open(clean_dict.csv, r, encodingutf-8) as fin, \ open(final_dict.csv, w, encodingutf-8, newline) as fout: reader csv.reader(fin) writer csv.writer(fout) header next(reader) writer.writerow(header [frequency]) for row in reader: word row[0] freq freq_map.get(word, 0) writer.writerow(row [freq])补不上的词频填 0不要填 NULL。填 0 在后续排序时默认排最后逻辑清晰填 NULL 在 Excel 里会显示成空白在 SQL 里做聚合时还要额外处理纯属给自己找麻烦。十万条里大概有几千条补不上主要是专有名词和缩写这些词本来也不需要按词频筛选。提示词频表的选择会影响排序结果。技术文档场景下通用词频表比口语词频表更合适因为技术词汇的分布和日常对话差别很大。3. 三种格式怎么生成SQL 建表、CSV 导出、Excel 落盘3.1 SQL 版建表语句和批量插入的写法SQL 版我一般给两种一种是.sql脚本文件包含建表和插入语句方便直接导入另一种是已经建好的 SQLite 数据库文件双击就能查。先看建表语句CREATE TABLE dict ( id INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR(64) NOT NULL, meaning VARCHAR(512) NOT NULL, frequency INTEGER DEFAULT 0 ); CREATE INDEX idx_word ON dict(word); CREATE INDEX idx_freq ON dict(frequency DESC);word字段建索引是必须的十万条数据不建索引等值查询也要全表扫描体验很差。frequency建降序索引是为了按词频排序时走索引不然每次ORDER BY frequency DESC都要重新排。两个索引会让插入变慢但查询快很多这个取舍值得。批量插入不要一条一条INSERT十万条能跑到天荒地老。用事务包起来每批一千条import sqlite3 import csv conn sqlite3.connect(dict.db) cur conn.cursor() cur.execute(CREATE TABLE IF NOT EXISTS dict ( id INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR(64) NOT NULL, meaning VARCHAR(512) NOT NULL, frequency INTEGER DEFAULT 0 )) batch [] with open(final_dict.csv, r, encodingutf-8) as f: reader csv.reader(f) next(reader) # 跳过表头 for row in reader: batch.append((row[0], row[1], int(row[2]) if len(row) 2 else 0)) if len(batch) 1000: cur.executemany(INSERT INTO dict (word, meaning, frequency) VALUES (?, ?, ?), batch) batch [] if batch: cur.executemany(INSERT INTO dict (word, meaning, frequency) VALUES (?, ?, ?), batch) conn.commit() cur.execute(CREATE INDEX IF NOT EXISTS idx_word ON dict(word)) cur.execute(CREATE INDEX IF NOT EXISTS idx_freq ON dict(frequency DESC)) conn.commit() conn.close()executemany比循环execute快一个数量级配合每批一千条的节奏十万条大概两三秒。索引放在插入之后建比插入之前建快很多因为插入时不用维护索引树。这个顺序别搞反我见过先建索引再插数据的脚本跑了半小时还没完。3.2 CSV 版编码、分隔符和换行符的三个坑CSV 看起来简单但跨平台交换时翻车最多。三个参数必须显式指定encodingutf-8、newline、delimiter,。少一个都可能出问题。import csv with open(final_dict.csv, r, encodingutf-8) as fin, \ open(dict_export.csv, w, encodingutf-8-sig, newline) as fout: reader csv.reader(fin) writer csv.writer(fout, delimiter,, quotingcsv.QUOTE_MINIMAL) for row in reader: writer.writerow(row)utf-8-sig是给 Excel 看的。Excel 打开普通 UTF-8 CSV 时中文经常乱码因为 Excel 默认按本地编码解析。加上 BOM 头也就是utf-8-sigExcel 就能正确识别。但注意如果这个 CSV 是喂给脚本或数据库的用普通utf-8就行BOM 头反而会污染第一个字段。我一般导出两份一份带 BOM 给人看一份不带 BOM 给机器读。newline是防止 Windows 下换行符被转成\r\n\r\n。不加这个参数CSV 里会多出空行导入数据库时多出一堆空记录。这个坑我踩过不止一次后来养成习惯写 CSV 必加newline。3.3 Excel 版十万行怎么不卡死Excel 单表能放一百多万行十万行理论上没问题但打开和筛选的速度取决于列数和公式。我的做法是只保留必要列不加任何公式不加条件格式不加数据验证。纯数据纯文本。import csv from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(dict) with open(final_dict.csv, r, encodingutf-8) as f: reader csv.reader(f) for row in reader: ws.append(row) wb.save(dict.xlsx)write_onlyTrue是关键。普通模式会把所有单元格对象留在内存里十万行直接吃掉几个 G。只写模式边写边释放内存占用稳定在几十兆。代价是写完之前不能读但导出场景本来就不需要读。如果同事需要在 Excel 里做筛选和排序我会额外加一步把word列设成文本格式避免 Excel 把某些词自动转成日期或科学计数法。比如march会被转成日期1e5会被转成科学计数。处理方式是在写入前给单元格设number_format但write_only模式下不能单独设格式所以要么改用普通模式内存换安全要么在 CSV 阶段就把这类词加上前缀。我一般选后者在清洗阶段就把纯字母词里可能被误判的加上单引号前缀导入 Excel 后再批量去掉。注意Excel 打开十万行 CSV 时如果直接双击默认只加载前几万行做预览滚动到后面会卡顿。正确做法是用「数据」→「从文本/CSV」导入让 Excel 一次性加载完。4. 导入与查询避坑那些让我加班到凌晨的报错4.1 避坑一SQL Server 导入 CSV 中文变问号现象用 SQL Server 的导入向导把 CSV 导进去中文释义全变成???。原因导入向导默认把VARCHAR当成非 Unicode 编码中文超出 ASCII 范围就丢了。CSV 文件本身是 UTF-8但向导按本地代码页解析。解决建表时把meaning字段改成NVARCHAR(512)导入时在「高级」里把OutputColumnWidth调大并且显式指定代码页为 65001UTF-8。如果还是不行用BULK INSERT代替向导BULK INSERT dict FROM D:\data\final_dict.csv WITH ( FORMAT CSV, FIRSTROW 2, CODEPAGE 65001, FIELDTERMINATOR ,, ROWTERMINATOR \n );CODEPAGE65001是核心FIRSTROW2跳过表头。注意ROWTERMINATOR在 Windows 下可能是\r\n如果导入报错说行尾不对换成0x0a试试。4.2 避坑二Navicat 导入时字段错位现象用 Navicat 导入 CSV明明列对得上导进去之后word和meaning串位了。原因CSV 里某个释义本身包含逗号而导入时没有启用文本限定符逗号被当成了字段分隔符。解决导入时勾选「文本限定符」为双引号并且确保 CSV 生成时用了QUOTE_MINIMAL或QUOTE_ALL。如果 CSV 已经生成好了没法改用 Python 重新写一遍把包含逗号的字段用双引号包起来import csv with open(bad.csv, r, encodingutf-8) as fin, \ open(fixed.csv, w, encodingutf-8, newline) as fout: reader csv.reader(fin) writer csv.writer(fout, quotingcsv.QUOTE_ALL) for row in reader: writer.writerow(row)QUOTE_ALL把所有字段都加引号虽然文件大一点但绝对不会错位。十万条数据也就多几百 KB不值得省。4.3 避坑三Excel 打开 CSV 后前导零丢失现象词条里有007这样的编号Excel 打开后变成7。原因Excel 自动把数字字符串转成数值前导零被吃掉。解决在 CSV 里给这类字段加007的写法或者导入时把该列设为文本。我一般在前端清洗阶段就判断如果word是纯数字或数字开头统一加单引号前缀导入后再用 SQL 的REPLACE去掉。4.4 避坑四SQLite 插入速度突然变慢现象前五万条插得飞快后面越来越慢最后几乎卡住。原因每插一条都自动提交事务每次提交都要写磁盘。数据量大了之后磁盘 I/O 成为瓶颈。解决把conn.commit()从循环里拿出来每批一千条提交一次或者干脆全部插完再提交。SQLite 默认每条语句一个事务用executemany配合手动提交速度能差几十倍。4.5 避坑五CSV 换行符导致行数对不上现象wc -l数出来十万行导入数据库后只有九万八。原因某些释义里本身包含换行符CSV 写入时没有转义导入时被当成了新行。解决写入前把释义里的\n和\r替换成空格或者用QUOTE_ALL让 csv 模块自动处理。我一般选前者因为释义里的换行本来就没有意义替换掉最干净meaning meaning.replace(\n, ).replace(\r, )5. 进阶技巧用 SQL 窗口函数做词频排名和去重数据导进去之后真正的价值在查询。十万条词库如果只是SELECT * FROM dict WHERE word algorithm那和记事本搜索没区别。我常用的两个进阶操作一个是词频排名一个是按词根去重。词频排名用窗口函数SQLite 3.25 以上和 SQL Server 2012 以上都支持SELECT word, meaning, frequency, RANK() OVER (ORDER BY frequency DESC) AS freq_rank, NTILE(10) OVER (ORDER BY frequency DESC) AS freq_decile FROM dict WHERE frequency 0 ORDER BY frequency DESC LIMIT 100;RANK()给出词频排名NTILE(10)把词分成十档方便做分层抽样。比如要取最高频的百分之十做重点翻译直接WHERE freq_decile 1就行。这个写法比子查询干净得多而且走索引。按词根去重稍微复杂一点。英文单词有大量派生形式run、running、runs其实是同一个词根。如果只想保留每个词根的最高频形式可以用ROW_NUMBER()配合LIKE做近似匹配SELECT word, meaning, frequency FROM ( SELECT word, meaning, frequency, ROW_NUMBER() OVER ( PARTITION BY SUBSTR(word, 1, 4) ORDER BY frequency DESC ) AS rn FROM dict WHERE LENGTH(word) 4 ) t WHERE rn 1 ORDER BY frequency DESC;SUBSTR(word, 1, 4)取前四个字母做分区键每个分区里按词频降序排rn 1就是最高频的那个。这个方法很粗糙run和runs前四个字母都是runs能分到一组但run和running前四个字母不同分不到一组。更精确的做法是用词干提取算法比如 Porter Stemmer先算词根再按词根分区。Python 里用nltk的PorterStemmer预处理一遍把词根写进单独字段SQL 里直接按词根分区就行。from nltk.stem import PorterStemmer import csv stemmer PorterStemmer() with open(final_dict.csv, r, encodingutf-8) as fin, \ open(dict_with_stem.csv, w, encodingutf-8, newline) as fout: reader csv.reader(fin) writer csv.writer(fout) header next(reader) writer.writerow(header [stem]) for row in reader: stem stemmer.stem(row[0]) writer.writerow(row [stem])有了stem字段SQL 里PARTITION BY stem就精确多了。这个字段占不了多少空间但能让去重和分组查询准确率提升一大截。最后一个技巧是关于导出的。如果同事要 Excel 版但只关心高频词不要直接给全量十万条。用 SQL 筛出前两万条再导出 Excel打开速度和筛选体验都好很多。全量数据留在 SQL 和 CSV 里给需要的人自己查。这个分工我用了很久比强行把十万条塞进 Excel 让所有人一起卡要明智。我自己现在维护词库的习惯是源文件永远保留一份纯文本清洗脚本单独存一个文件每次更新数据都从源文件重新跑一遍不直接改 SQL 或 Excel。这样出了问题能回滚也能追溯是哪一步引入的脏数据。希望帮到你。本文还有配套的精品资源点击获取