MySQL 8.0导入1700万条K12题库：字符集与LaTeX避坑

简介这份中小学题库 MySQL 资源面向在线 K12 教育从业者、题库产品与研发人员聚焦数学、物理、化学等学科试题的存储、章节与知识点组织、题目属性设置以及 LaTeX 公式录入与前端显示等常见难题。压缩包共 583 个文件约 2.03MB包含 574 张 PNG 示意图、2 份 DOCX 结构说明与题库简介文档、HTML 与 JS 页面脚本、1 个 SQL 数据库文件以及少量辅助素材便于对照理解数据表结构、字段关系与公式渲染流程。已有 1109 人学习下载。包内提供样本题库、数据结构图及说明文档可参考章节层级建设、知识点挂接、题目属性设计和公式展示方案了解 MathJax 等组件在题干与选项中的用法对公式录入规范、前后台字段衔接和题库后台搭建也能形成直观认识适合在线教育团队优化题库结构时查漏补缺。1. 先别急着 source1700 万条 K12 题库灌进 MySQL 前要定下的三件事拿到「中小学题库 mysql.zip」的时候很多人的第一反应是解压、找 .sql、source一把灌进去然后在跑到一半时收获一条ERROR 2006或者更糟——全部灌完了打开数学题库一眼扫过去题干里的\frac{1}{2}变成了frac12物理题库的\vec{F}也没了。这类免费 K12 题库资源的特点是体量大、字段长、公式密度高1700 万条记录里数学题库和物理题库占了大头几乎每条题干都夹着 LaTeX。装不好不是少几条数据的事而是整条公式渲染链路从第一步就断了。这里记录的是我完整走过一遍的顺序装库、定字符集、建表、灌数、验证渲染适合准备把这套题库落到自己服务器上做检索或二次开发的人。2. 装 MySQL 8.0版本、字符集和客户端的三处取舍2.1 版本选择8.0 和 5.7 的差别不只在版本号资源包里没写必须用哪个版本那就按数据量反推。1700 万行、单表为主、字段全是长文本这个量级对 InnoDB 来说不算大一台 8G 内存的机器就能跑起来真正决定成败的是字符集和导入参数而不是小版本号。但版本这条线还是要先划清楚。我一般会把底线定在 8.0.30 以上。理由有三条第一8.0 默认字符集就是utf8mb4省掉一堆建库时忘写 charset 的老问题第二8.0 默认排序规则utf8mb4_0900_ai_ci基于 UCA 9.0.0做题库这种带大量中文和符号的模糊匹配时比 5.7 的utf8mb4_general_ci稳定第三8.0.30 之后 redo log 容量改成了innodb_redo_log_capacity动态参数导入大文件时调起来比 5.7 时代改innodb_log_file_size再重启省事得多。维度MySQL 5.7MySQL 8.0默认字符集latin1utf8mb4默认排序规则utf8mb4_general_ciutf8mb4_0900_ai_ci默认认证插件mysql_native_passwordcaching_sha2_passwordredo log 参数innodb_log_file_sizeinnodb_redo_log_capacity8.0.30导入 1700 万行建议 buffer pool2G 起4G 起如果非要留在 5.7也不是不行两个地方必须手动补建库和建表都要显式写CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci因为 5.7 的utf8只认三字节碰上题库里的数学字母符号直接报Incorrect string value。另外 5.7 没有utf8mb4_0900_ai_ci从 8.0 导出的 SQL 拿到 5.7 上执行会直接报Unknown collation这个在跨版本迁移时是经典血泪经验。还有一个版本坑藏在认证插件里。8.0 默认用caching_sha2_password一些老版本的图形客户端和驱动连上去会报Authentication plugin cannot be loaded。真要兼容老工具建账号时指定一下CREATE USER k12% IDENTIFIED WITH mysql_native_password BY 换成你的密码; GRANT ALL PRIVILEGES ON k12_bank.* TO k12%; FLUSH PRIVILEGES;IDENTIFIED WITH mysql_native_password是显式指定认证插件不写就跟着default_authentication_plugin走。这只是为了兼容新项目组里我一般还是让客户端升级到支持 8.0 认证的版本别把一个降级方案当成长期配置。2.2 字符集与排序规则utf8mb4 不是可选项题库类数据最容易在这里翻车。MySQL 里的utf8其实是utf8mb3最大三字节遇到四字节字符就报错。数学公式本身是 ASCII 反斜杠加字母理论上三字节够用但题库里往往混着集合符号、数学字母数字符号、带组合音标的字符这些都在补充平面一个字符占四字节。一旦插入时撞上整条 INSERT 会被拒而且错误信息只会告诉你哪一行有问题1700 万条里定位起来很痛苦。建库这一步把字符集和排序规则一次定死CREATE DATABASE k12_bank DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;CHARACTER SET utf8mb4决定存储编码四字节全收COLLATE utf8mb4_0900_ai_ci决定比较和排序规则ai是不区分重音ci是不区分大小写做题干关键词模糊搜索时行为更符合直觉。如果你的表是从别处 dump 过来的建库语句里的这两个子句不会覆盖表自身的定义还得回头检查表级别有没有被写成CHARSETutf8。装完库之后别急着导入先验一遍链路SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;重点看character_set_server、character_set_database、character_set_client、character_set_connection、character_set_results这五个。前两个由 my.cnf 和建库语句决定后三个跟着客户端连接走。常见情况是服务端已经是 utf8mb4但命令行客户端进来还是utf8mb3导入时中文和符号就被转成了问号等发现的时候几百 G 数据已经灌进去了只能重来。2.3 客户端工具导入走命令行看数据走图形界面工具分工这点小事踩过坑的人都知道要分。1700 万条的 SQL 文件通常有几个 G图形客户端打开「运行 SQL 文件」时很多实现是把整个文件读进内存再切分语句内存一爆就卡死或者超时断开。DBeaver 在这块相对保守Navicat 会好一些但要手动调大连接超时命令行mysql客户端是流式读内存占用恒定最稳。所以我的习惯是导入全程用命令行验证和数据浏览再用图形客户端。命令行里显式带字符集参数别指望默认值mysql --default-character-setutf8mb4 \ -h 127.0.0.1 -P 3306 -u root -p \ k12_bank ./k12db/question.sql--default-character-setutf8mb4会把连接的 client、connection、results 三个字符集一起设成 utf8mb4等价于在会话里执行SET NAMES utf8mb4。-h 127.0.0.1而不是localhost是为了走 TCP避免在某些系统上走 socket 时参数不生效。图形客户端那边JDBC 连接串记得带上useUnicodetruecharacterEncodingutf88.0 驱动下这个utf8会被映射到 utf8mb4老驱动不会所以驱动版本也别太旧。3. 建库建表从 zip 里的 SQL 文件反推表结构与 LaTeX 字段3.1 解压后先清点文件别急着 source中小学题库mysql.zip里可能是一个巨大的.sql也可能是structure.sql加若干个数据分片。先看清单和大小这一步花三十秒能省后面半小时# 不解压先看压缩包里有什么 unzip -l 中小学题库mysql.zip # 解压到独立目录 unzip 中小学题库mysql.zip -d ./k12db # 看每个文件多大判断哪些是结构、哪些是数据 ls -lh ./k12db du -sh ./k12dbunzip -l列出条目名、原始大小、压缩比能一眼看出数据文件是不是被切成了多个分片。du -sh是解压后占用的实际磁盘空间这个数字要拿来跟数据库所在分区剩余空间对比——导入过程中 InnoDB 会同时写数据文件、redo log 和 undo经验上至少留出 SQL 文件体积的 2.5 倍。然后看文件头确认三件事建表语句用的引擎和字符集、INSERT 是不是多行批量、有没有DROP TABLE IF EXISTShead -n 40 ./k12db/*.sql grep -c ^INSERT INTO ./k12db/question.sql grep -m 3 ENGINE ./k12db/structure.sqlhead看头部能看到/*!40101 SET NAMES ... */这类 mysqldump 注释如果写的是utf8而不是utf8mb4导入时我们的命令行参数会覆盖它问题不大但要心里有数。grep -c数 INSERT 语句条数配合文件大小能估算出平均每条 INSERT 带多少行——如果只有几十条 INSERT 但文件好几个 G说明是超长多行插入max_allowed_packet就得往大了调。3.2 建库语句与导入前的会话参数结构文件如果自带CREATE DATABASE直接执行就行如果没有或者写的是 latin1就手写一份。除了字符集导入会话还有四个开关要设这一步是后面性能的分水岭CREATE DATABASE k12_bank DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE k12_bank; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; SET UNIQUE_CHECKS 0; SET autocommit 0;FOREIGN_KEY_CHECKS 0关掉外键检查题库表之间就算有科目、知识点这类关联导入顺序也不用care灌完再打开。UNIQUE_CHECKS 0让 InnoDB 在导入期间先不校验唯一索引把二级索引写合并到后台1700 万行下这一项能省掉相当可观的随机 IO——代价是导完必须自己查一遍重复这个后面验证章会写。autocommit 0把整批 INSERT 包在一个事务里避免每条语句一次提交一次刷盘。注意这三条是会话级参数退出连接就失效只影响本次导入不会污染线上的其他业务。但也正因为是会话级用图形客户端分多次执行时每次新开连接都得重设一遍。3.3 LaTeX 字段该用什么类型TEXT、MEDIUMTEXT 和 ROW_FORMAT题库表的字段命名各家不一样但结构大同小异题干、选项、答案、解析、知识点、科目、年级。LaTeX 公式主要落在前四个字段里。给一份示例结构字段名以你解压后实际的 SQL 为准CREATE TABLE question ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, subject TINYINT UNSIGNED NOT NULL COMMENT 1数学 2物理 3化学 4生物, grade TINYINT UNSIGNED NOT NULL COMMENT 年级1-12, stem TEXT NOT NULL COMMENT 题干含 LaTeX 公式, options TEXT COMMENT 选项JSON 或多行文本, answer TEXT COMMENT 参考答案, analysis TEXT COMMENT 解析含 LaTeX 公式, knowledge VARCHAR(255) DEFAULT NULL COMMENT 知识点标签, PRIMARY KEY (id), KEY idx_subject_grade (subject,grade) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci ROW_FORMATDYNAMIC;TEXT上限 64KB单条题干加解析基本不会超除非解析里嵌了整段推导加图片 base64。真超了就升MEDIUMTEXT16MB但要注意字段一升级行内能放的数据更少更多行会溢出到行外页。ROW_FORMATDYNAMIC是让 TEXT 这类大字段尽量走行外存储主键 B 树里只留指针扫描COUNT(*)和按 id 查详情时快很多。8.0 默认就是 DYNAMIC显式写出来是为了防止某些环境从 5.7 的 COMPACT 迁过来。索引这块有个实操顺序先建表不建二级索引灌完数据再ALTER TABLE加上去。1700 万行边插边维护idx_subject_grade每次插入都要随机写索引页灌完一次性建索引是顺序扫描加排序通常快好几倍。这一步在第 4 章会给出具体命令。最后提醒一句如果结构文件里已经有CREATE TABLE别自己重写用它的只把CHARSET和COLLATE两处改成 utf8mb4 就行。字段类型乱改会导致数据文件对不上导入直接报列数不匹配。4. 导入 1700 万条记录五个服务端参数与分批灌数的完整流程4.1 服务端五个必调参数默认配置是为小数据量 OLTP 准备的直接灌 1700 万行大概率中途断。改 my.cnf改完必须重启 mysqld 才生效[mysqld] character-set-server utf8mb4 collation-server utf8mb4_0900_ai_ci max_allowed_packet 256M innodb_buffer_pool_size 4G innodb_redo_log_capacity 2G innodb_flush_log_at_trx_commit 2 innodb_flush_method O_DIRECT skip-name-resolvemax_allowed_packet 256M是服务端能接收的单条 SQL 或单个网络包上限。多行批量 INSERT 一条语句可能几十兆默认 64M 常常不够报ERROR 1153 Got a packet bigger than max_allowed_packet bytes。这个参数客户端和服务端各有一份两边都要调只调服务端等于没调。innodb_buffer_pool_size 4G给的是缓冲池导入期间索引页和二级索引都在里面暂存机器内存的 50%70% 之间取值比较合适。8G 机器给 4G16G 机器可以给 8G10G别给满系统本身还要留。innodb_redo_log_capacity 2G只在 8.0.30 及以上有效替代了老的innodb_log_file_size。redo 太小会导致导入期间频繁 checkpoint磁盘 IO 打满而 CPU 空转。5.7 或 8.0.30 之前的版本改innodb_log_file_size 1G并且要重启两次才生效。innodb_flush_log_at_trx_commit 2让事务提交时每秒刷一次盘而不是每次导入期间吞吐能翻几倍。这是个用持久性换速度的参数只在导入窗口临时改灌完立刻改回 1别留在生产配置里。skip-name-resolve关掉反向 DNS 解析客户端连进来的握手快很多特别是从别的机器导入时。这个和前面的-h 127.0.0.1是配套的。4.2 命令行导入的完整流程参数调好、服务重启完按顺序执行。先是结构# 1. 导入表结构如果结构文件里已含 CREATE DATABASE 就省掉建库 mysql --default-character-setutf8mb4 -u root -p k12_bank ./k12db/structure.sql然后确认表建出来了、字符集对了再灌数据# 2. 记录开始时间灌数据 time mysql --default-character-setutf8mb4 \ -u root -p \ --max-allowed-packet256M \ --net-buffer-length1M \ k12_bank ./k12db/question.sql--max-allowed-packet256M必须和服务端 my.cnf 里的值一致客户端这边小了照样报 1153。--net-buffer-length1M影响多行 INSERT 拼包的大小默认 16K 会让驱动把一条大 INSERT 切成很多小包发网络往返次数变多调到 1M 后一条 INSERT 基本能一个包发完。time是给自己留个基准。1700 万行、纯文本字段、SSD 环境正常量级在一刻钟到一小时之间具体看单行长度和机器。如果跑了三个小时还没完八成是 buffer pool 太小或者 redo 配置不对别干等SHOW ENGINE INNODB STATUS看Pending writes和Log sequence number是否在动。数据灌完后补二级索引并恢复会话参数-- 3. 补二级索引 ALTER TABLE question ADD KEY idx_subject_grade (subject,grade); -- 4. 把临时关掉的检查打开新连接默认就是开的这里只是显式确认 SET FOREIGN_KEY_CHECKS 1; SET UNIQUE_CHECKS 1; -- 5. 统计信息刷新优化器才知道有 1700 万行 ANALYZE TABLE question;ALTER TABLE ... ADD KEY在 8.0 默认走 ONLINE 模式导入期间表仍然可读但会占用额外的临时空间。ANALYZE TABLE别漏统计信息不准会让后面按科目分页查题时走错索引一页查十秒。4.3 大文件拆分与断点续传如果 SQL 文件单个体积过大或者导入中途失败想续就得拆。拆之前先确认一件事每条 INSERT 是否独占一行。mysqldump 默认的 extended-insert 就是一行一条INSERT INTO ... VALUES (...),(...),...;这种可以直接按行切# 按 30 万行一个分片切-d 让后缀是数字便于按序导入 split -l 300000 -d --additional-suffix.sql ./k12db/question.sql ./k12db/part_ # 按顺序逐个导入中间断了从失败的那个分片接着来 for f in $(ls ./k12db/part_*.sql | sort); do echo importing $f mysql --default-character-setutf8mb4 -u root -p \ --max-allowed-packet256M k12_bank $f || break donesplit -l 300000是每 30 万行切一个文件-d用数字后缀part_00、part_01--additional-suffix补上 .sql 扩展名方便通配。循环里|| break是关键某个分片失败就停下来别继续往后灌否则数据会断档且难以发现。如果 INSERT 不是单行而是跨行的巨型多行插入split会把语句拦腰截断导入时报语法错误。这种情况用 mysqldump 自带的--extended-insert重新导出一份再切或者用 awk 按分号切# 按分号切语句每 20 万条语句一个文件适合语句跨行的情况 awk BEGIN{RS;\n; n1} {printf %s;\n, $0 sprintf(./k12db/stmt_%03d.sql, int((n-1)/200000)); n} ./k12db/question.sql这条 awk 把记录分隔符设成「分号加换行」逐条输出到编号文件。注意它只适合语句以;\n结尾的规范 dump如果文件里字符串内容本身含分号加换行会被误切所以切完务必先拿最小的分片试导入一遍。5. 翻车排查导入大题库时最常见的五类报错与修复5.1 连接断掉与包太大现象导入跑了几分钟突然ERROR 2006 (HY000): MySQL server has gone away客户端直接退出表里只有一部分数据。原因基本是两个一是单条 INSERT 超过max_allowed_packet服务端主动断开连接二是空闲超时wait_timeout默认 8 小时一般够但如果中间在等大事务提交客户端长时间没发包也会被踢。先用SHOW VARIABLES LIKE max_allowed_packet确认服务端值再确认客户端启动参数有没有带同一个值。解决就是把两边都调到 256M客户端用--max-allowed-packet256M服务端写 my.cnf 后重启。改完先别全量重跑拿一个最小分片试。现象ERROR 1153 (08S01): Got a packet bigger than max_allowed_packet bytes。这和上一条是同一个根因的不同表现区别是 1153 明确告诉你包超了。很多人只改了 my.cnf 忘了重启或者只改了客户端参数。注意max_allowed_packet在服务端是只读参数以外的那种需要重启的SET GLOBAL改不了它必须写配置文件重启。如果源文件里存在单条超长 INSERT除了调参数也可以在 dump 时加--net-buffer-length和--skip-extended-insert拆成单行插入代价是导入慢但稳。5.2 字符集、反斜杠与重复数据现象导入成功但查出来的中文全是???或者导入直接报ERROR 1366 (HY000): Incorrect string value: \xF0\x9D\x98\x93...。原因是从客户端到表这条链路上有任一环不是 utf8mb4。常见的断点是建库用了 utf8mb4 但表没继承客户端连接字符集是 utf8mb3SQL 文件头部的SET NAMES utf8覆盖了命令行参数。排查顺序是先在会话里SHOW VARIABLES LIKE character_set%五个变量必须全是 utf8mb4再看SHOW CREATE TABLE question末尾的 CHARSET。解决表级别用ALTER TABLE question CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;转换1700 万行会比较久最好在导入阶段就定对客户端参数照第 4 章那样加全。已经变成问号的数据救不回来只能重灌。现象题干里的\frac{1}{2}存进库里变成frac{1}{2}反斜杠整个消失或者反过来变成\\frac双份。原因是 MySQL 的字符串字面量里反斜杠是转义符\f会被解释成换页符。如果导入时sql_mode里带了NO_BACKSLASH_ESCAPES反斜杠又不再转义源文件里写好的\\frac会原样存成两个反斜杠。这两种都会让前端 MathJax 或 KaTeX 渲染失败——\frac前面的反斜杠没了公式就退化成普通文本。解决分两步先看SELECT sql_mode把NO_BACKSLASH_ESCAPES去掉再抽样确认存储层到底是什么-- 用 HEX 看实际字节反斜杠是 0x5C SELECT id, HEX(SUBSTRING(stem, 1, 8)) AS head_bytes FROM question WHERE stem LIKE %frac% LIMIT 5;0x5C只有一个说明存对了0x5C5C就是双份需要在导出或展示层做一次反转义。这个坑最阴的地方在于肉眼看不出来只有真正渲染的时候才炸。现象第二次执行导入脚本数据量从 1700 万变成 3400 万题目重复出现。原因是 dump 文件里没有DROP TABLE IF EXISTS或者有但你没执行结构文件直接对着已有表INSERT。更隐蔽的一种是表上没有任何唯一约束题库的题干加选项组合本可以建唯一键重复插入时数据库一声不吭。解决导入前先SELECT COUNT(*)记下来导完再数一遍结构文件里确认有没有 DROP如果业务上允许给stem的 MD5 加一列唯一索引用INSERT IGNORE或ON DUPLICATE KEY UPDATE做幂等。已经重复的用去重语句清理-- 保留每个题干里 id 最小的那条其余删掉执行前先备份 DELETE q1 FROM question q1 JOIN question q2 ON q1.stem q2.stem AND q1.id q2.id;q1.id q2.id保证只删重复组里 id 较大的那些。这条语句在 1700 万行上要跑很久建议按 subject 分片执行别一次性全表。现象导入到一半报ERROR 1118 (42000): Row size too large或者磁盘空间突然被吃满。前者是因为表里有太多 TEXT 或 VARCHAR 字段行格式又是 COMPACT单行上限 8126 字节撞上了。解决是把表的ROW_FORMAT改成DYNAMICTEXT 走行外。后者是 InnoDB 的 undo 和 redo 在导入期间膨胀尤其autocommit 0把整批包成一个事务时undo 会一直涨。解决是分批提交每 20 万到 50 万行 commit 一次并在导入前用df -h确认剩余空间至少是 SQL 文件体积的 2.5 倍。6. 装完怎么验证抽样比对与 LaTeX 公式真正跑通装完不等于跑通。行数对上只是第一关LaTeX 能不能原样渲染出来才是这套题库的价值所在。我一般分三步走从粗到细。第一步行数和分布对账。用总量、按科目分组、按年级分组三个查询交叉验证校验项SQL预期总行数SELECT COUNT(*) FROM question;与资源说明的 1700 万量级一致科目分布SELECT subject, COUNT(*) FROM question GROUP BY subject;数学、物理占大头无异常小分组空题干SELECT COUNT(*) FROM question WHERE stem IS NULL OR stem ;接近 0字符集SHOW CREATE TABLE question;末尾是 utf8mb4SELECT subject, COUNT(*) AS cnt FROM question GROUP BY subject ORDER BY cnt DESC;如果总行数对得上但某个科目为 0说明导入时分片顺序乱了或者某个分片失败了这是第 4 章那个循环里|| break的用处——至少你知道停在哪。第二步公式样本抽查。别随机抽要按公式特征抽-- 抽含分数、根号、向量的题目各取几条看原文 SELECT id, subject, LEFT(stem, 100) AS stem_head FROM question WHERE stem LIKE %\\frac% OR stem LIKE %\\sqrt% OR stem LIKE %\\vec% LIMIT 10;LIKE里的\\frac在字符串字面量阶段先被转成\frac再到 LIKE 模式里\f不是通配符所以能精确匹配到反斜杠开头的命令。查出来的stem_head里应该能看到完整的\frac{a}{b}。看不到反斜杠就回第 5 章排查。第三步真渲染一遍。用一段 Python 脚本把样本拉出来套最简单的 KaTeX 或 MathJax 页面肉眼确认公式出来的是分数而不是一行斜杠。脚本只做取数和转义检查重点看两个东西反斜杠是不是奇数个、有没有残留的 HTML 转义实体import pymysql, re conn pymysql.connect( host127.0.0.1, userroot, password你的密码, databasek12_bank, charsetutf8mb4 ) cur conn.cursor() cur.execute(SELECT id, subject, stem FROM question WHERE stem LIKE %s LIMIT 200, (%\\\\frac%,)) bad [] for qid, subject, stem in cur: # 统计反斜杠开头的 LaTeX 命令个数 cmds re.findall(r\\[a-zA-Z], stem) if not cmds: bad.append((qid, no latex command)) elif lt; in stem or amp; in stem: bad.append((qid, html entity leaked)) print(fchecked200 bad{len(bad)}) for item in bad[:5]: print(item)charsetutf8mb4保证驱动层不乱码re.findall(r\\[a-zA-Z], stem)找出所有 LaTeX 命令一个都找不到说明反斜杠丢了lt;、amp;出现说明数据在某个环节被 HTML 转义过一次前端再转就变成死文本。这一步跑完bad0才算真正装通。从那以后我每次把题库类数据落到新库都会强制走一遍「总量对账 公式样本 真实渲染」这三关哪怕时间再紧也不省。因为 1700 万条数据里坏掉的可能只有几千条抽样不刻意的话根本撞不上等用户搜到那道题才发现公式是坏的返工成本比验证高一个数量级。希望帮到你。本文还有配套的精品资源点击获取