简介驾考科目一、科目四题库数据包覆盖小车、客车、货车、摩托车四类车型同时提供数据库表结构与JSON两种存储形式既适合学员刷题复习也适合开发者将数据接入考试应用或教学平台。题量方面客车科目一两千一百五十四题、科目四两千一百二十六题小车科目一一千六百题、科目四一千三百题摩托车科目一四百四十六题、科目四三百八十三题货车科目一两千一百六十二题、科目四一千二百零六题各科目题量充足便于分类强化训练。压缩包共包含两千个文件以一千九百九十五个webp图片为主体另有SQL数据库脚本、JSON数据文件和一张GIF示意素材整体大小约一百零三兆。图片素材可辅助图文和情景类试题呈现SQL与JSON数据能直接导入数据库或供前端解析节省数据整理时间。目前该题库已有一千零六人学习下载按车型与科目分模块组织结构清晰适合快速搭建刷题环境或进行试题结构分析。1. 驾照题库离线化为什么 SQL 表数据和 JSON 格式能成为科目一科目四的标配做驾考培训系统和模拟考试 App 的同学应该都经历过这种场景甲方一个月后要上线科目一题库却还在一个 Excel 里来回传图片素材用微信发。等到联调的时候发现题目在 SQL Server 里答案在 JSON 文件里图片在另一个服务器上三套数据对不上。这套「驾照考试科目一科目四题库 SQL 表数据 JSON 格式 图片素材」的离线题库方案本质上是把题库做成了三层解耦的结构题目和答案进关系型数据库用于按车型小车、客车、货车、摩托车做随机组卷和正确率统计JSON 文件负责给前端和接口层做缓存和热更新图片素材单独挂载走 CDN 或本地静态目录。为什么要用 SQL 表数据而不是纯 JSON?因为科目一科目四的题目类型不是只有单选还有判断、多选且不同车型的题目范围有交集也有差异。纯 JSON 做静态展示还行一旦要按用户答题记录算通过率、按章节出题、做错题本SQL 的关联查询和索引优势就出来了。而 JSON 格式的价值在于它能把 SQL 查出来的结果序列化成前端直接能用的结构尤其是小程序和 H5 端根本不需要后端二次加工。图片素材单独拎出来是因为一张科目一图片题可能 200KB 到 1MB如果直接塞进 SQL Server 的 varbinary 字段备份和查询都会变慢。这套方案的适用人群很明确驾考培训机构的自研系统、驾培 SaaS 平台的题库模块、以及想离线部署到驾校机房的教学软件。关键点是「离线」——很多驾校的网络环境并不稳定考场和训练场甚至没有外网所以题库必须能整体导出、导入、增量更新。后面我会按「建表 → 导 JSON → 配图片 → 排错」的顺序把这套骨架完整落地一遍。2. 建一张能扛住四种车型的科目一科目四题库表表结构设计与数据组织2.1 先定数据边界为什么科目一科目四要分开但又要能合并查科目一考的是道路交通安全法规和交通信号科目四考的是安全文明驾驶常识题面不一样但底层字段几乎一致。所以在设计 SQL 表时常见做法不是建两张独立表而是一张题表单加一个 exam_type 字段区分科目。这样做的收益在后续开发里非常明显错题本、答题记录、收藏夹都可以只关联 question_id不用关心它属于哪个科目组卷时再用 exam_type 做过滤条件。如果一开始建了两张表后面每个功能都要写 UNION 查询SQL 复杂度和出错概率都会上升。车型维度小车、客车、货车、摩托车则是典型的多对多关系。一道题可能同时适用于小车和货车比如「驾驶机动车在高速公路上发生故障时应在车后多少米设置警告标志」小车和货车都考但摩托车题库里没有这道题。所以不能把车型做成题表里的一个字段而是要单独建一张车型关联表或者用 JSON 数组字段保存适用车型。考虑到要支持 SQL 查询比如「查所有摩托车题」我建议建一张中间表 question_vehicle而不是在题表里存逗号分隔的字符串。2.2 核心建表语句把图片路径变成冗余字段而不是存二进制下面是一份可直接复用的 MySQL 建表脚本SQL Server 的语序稍有不同但字段设计可以直接平移。如果项目用的是 SQL Server把 AUTO_INCREMENT 换成 IDENTITY(1,1)把 TINYINT 换成 SMALLINT 就行。为什么不用 MongoDB因为题目之间有章节、车型、考试类型三种维度关系型数据库的索引和 JOIN 在这个场景下仍然是最省心的。CREATE TABLE question ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 题目ID, exam_type TINYINT NOT NULL COMMENT 1科目一 4科目四, question_type TINYINT NOT NULL COMMENT 1判断题 2单选题 3多选题, content TEXT NOT NULL COMMENT 题干内容不含图片, option_a VARCHAR(255) DEFAULT NULL COMMENT 选项A判断题可为空, option_b VARCHAR(255) DEFAULT NULL COMMENT 选项B, option_c VARCHAR(255) DEFAULT NULL COMMENT 选项C, option_d VARCHAR(255) DEFAULT NULL COMMENT 选项D, answer VARCHAR(10) NOT NULL COMMENT 正确答案多选用逗号分隔如 A,C, image_url VARCHAR(500) DEFAULT NULL COMMENT 图片相对路径或URL无图为NULL, chapter_id INT DEFAULT NULL COMMENT 章节ID用于按章节练习, difficulty TINYINT DEFAULT 1 COMMENT 1易 2中 3难, sort_order INT DEFAULT 0 COMMENT 同章节内排序号, status TINYINT DEFAULT 1 COMMENT 1启用 0停用, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_exam_type (exam_type), KEY idx_chapter_id (chapter_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT驾照科目一科目四题库主表; CREATE TABLE question_vehicle ( question_id BIGINT UNSIGNED NOT NULL, vehicle_type TINYINT NOT NULL COMMENT 1小车 2客车 3货车 4摩托车, PRIMARY KEY (question_id, vehicle_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT题目车型关联表;这段建表逻辑里有几个地方是实际项目里踩过坑后总结出来的。首先是 image_url 字段用相对路径而不是完整 URL——离线环境下服务器的域名或 IP 可能随时变存完整 URL 会导致图片全部失效。其次是 answer 字段用字符串而不是单独的答案表科目一科目四的答案本质上就是「A」「B」「C」「D」「对」「错」这几种多选答案用逗号拼接查询时配合程序端拆解都很快不需要再建一张答案表增加 JOIN 复杂度。2.3 批量导入的正确姿势不要一条条 INSERT要拼批量语句拿到一份 Excel 或 CSV 格式的原始题库后第一件事不是写导入程序而是先做数据清洗。常见的脏数据有题号列混入「第1题」这样的中文、答案列写成「正确」「错误」而不是「A」「B」、图片列写的是「图1.jpg」但实际素材文件叫「IMG_20230101_1.jpg」。清洗完再导入推荐用 LOAD DATA 或者构造批量 INSERT,别用循环单条 INSERT,不然一万道题可能要跑十几分钟。LOAD DATA LOCAL INFILE /data/question_clean.csv INTO TABLE question CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, exam_type, question_type, content, option_a, option_b, option_c, option_d, answer, image_url, chapter_id, difficulty) SET exam_type IF(exam_type , 1, exam_type), question_type IF(question_type , 1, question_type), content NULLIF(TRIM(content), ), image_url IF(image_url , NULL, image_url);这里有几个参数需要说明。FIELDS TERMINATED BY , 表示 CSV 的列分隔符是英文逗号如果原文件是分号分隔或 Tab 分隔要对应改成 ; 或 \t。OPTIONALLY ENCLOSED BY 是为了处理内容里含逗号的题干——题干可能写成「驾驶机动车时不可以以下哪项是错误的」如果不用引号包围LOAD DATA 会把一个字段拆成多列。IGNORE 1 LINES 跳过表头。SET 子句里的 IF 和 NULLIF 是在导入过程中做轻度清洗空字符串转 NULL、前后空格去掉、空值给默认科目类型。2.4 车型关联数据的补齐用一条 SQL 处理多对多关系题目主表导入完成后接着处理 question_vehicle 表。原始题库里车型信息的表现形式五花八门有的在题号前带「C1」前缀有的在 Excel 里单独一列写「小车/客车」还有的只在小车题库里出现、没有标注。这里我一般用一个两步走的方法先按文件名或 Excel 列把批量关联关系整理成一张临时表再用一条 INSERT ... SELECT 语句把临时表数据刷进 question_vehicle。CREATE TEMPORARY TABLE tmp_vehicle_map ( question_id BIGINT NOT NULL, vehicle_type TINYINT NOT NULL ); INSERT INTO question_vehicle (question_id, vehicle_type) SELECT t.question_id, t.vehicle_type FROM tmp_vehicle_map t; -- 校验查一下有多少题没有任何车型关联 SELECT COUNT(*) FROM question q LEFT JOIN question_vehicle qv ON q.id qv.question_id WHERE qv.question_id IS NULL;上面最后一条校验 SQL 务必执行这是最容易翻车的地方。如果某道题没有关联任何车型那么在小程序端无论选小车还是货车这道题都不会出现在练习列表里用户会反馈「题库少了题」但实际上题在库里只是关联表漏了。出现这种情况的原因通常是导入车型关联临时表的时候Excel 里的题号跟主表导入后的自增 ID 对不上——原始题号是 1 到 9999但 MySQL 自增 ID 可能从 10001 开始这时候需要在临时表里先做题号到 ID 的映射转换不能直接把原始题号当 ID 用。3. 把 SQL 表数据导出成 JSON 格式从小程序适配到增量更新3.1 为什么不在接口层实时查库而要先导出一份 JSON科目一科目四的题库有一个明显特点读多写少。一份题库数据可能在几个月内都不会变化但会被几千个用户同时刷题。如果每次刷题都实时查 MySQL数据库的 QPS 会很高而且题库数据的价值密度低——用户做一道题只需要题干、选项、答案、图片 URL 四个字段没必要每次都把整行数据拖出来。常见做法是管理后台更新题库后把 SQL 表数据导出成 JSON 文件推送到对象存储或服务器静态目录前端直接请求 JSON 文件解析不进数据库。这个方案对小程序的收益尤其明显。小程序包体有限制但远程 JSON 可以随便做大而且 JSON 结构天然契合小程序 setData 的数据格式。另一个收益是离线把 JSON 文件打包进 App 资源目录后用户在无网环境下也能刷题这也是这个标题里「离线题库」的核心实现点。相比接口实时返回 JSON,静态 JSON 还能省掉一层后端渲染和序列化的耗时模拟考试场景下题目加载速度更快。3.2 用 Python 脚本一次导出多车型 JSON结构设计是关键导出 JSON 不能简单地把每一行 SQL 查询结果塞进数组要按前端渲染的需求设计嵌套结构。科目一题目通常带图片科目四可能带视频或情景题前端需要一个统一的题目对象协议。下面是我常用的导出脚本核心片段用 PyMySQL 查库、用 json 模块落盘依赖少任何一台机器装了 Python 就能跑。import pymysql import json from collections import defaultdict def export_questions_by_vehicle(vehicle_type1, output_pathquestions_car.json): conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedrive_test, charsetutf8mb4 ) sql SELECT q.id, q.exam_type, q.question_type, q.content, q.option_a, q.option_b, q.option_c, q.option_d, q.answer, q.image_url, q.chapter_id, q.difficulty FROM question q INNER JOIN question_vehicle qv ON q.id qv.question_id WHERE qv.vehicle_type %s AND q.status 1 ORDER BY q.exam_type, q.chapter_id, q.sort_order with conn.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, (vehicle_type,)) rows cursor.fetchall() questions_by_exam defaultdict(list) for row in rows: # 把None值转成空字符串避免JSON里出现null导致前端报错 item { id: row[id], examType: row[exam_type], questionType: row[question_type], content: row[content] or , options: { A: row[option_a] or , B: row[option_b] or , C: row[option_c] or , D: row[option_d] or }, answer: row[answer], imageUrl: row[image_url] or , chapterId: row[chapter_id], difficulty: row[difficulty] } questions_by_exam[row[exam_type]].append(item) result { vehicleType: vehicle_type, updateTime: 2025-01-15 10:00:00, total: len(rows), data: { subject1: questions_by_exam.get(1, []), subject4: questions_by_exam.get(4, []) } } with open(output_path, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2) print(fexported {len(rows)} questions to {output_path})这个脚本里的几个参数值得细说。cursor 使用 DictCursor这样 row 就是一个字典可以直接用字段名取值比元组下标清晰得多。ensure_asciiFalse 必须加不然中文题干会被转成 \uXXXX 转义序列JSON 文件体积会膨胀 40% 左右而且肉眼没法排查数据。indent2 是给开发阶段调试用的方便直接打开 JSON 文件检查题目内容正式上线时如果对体积敏感可以改成 indentNone 并用 separators(,, :) 压缩输出一万道题的 JSON 大概能从 5MB 压到 3.5MB。另外注意我在查询 SQL 里用了 INNER JOIN question_vehicle这意味着如果某道题在关联表里缺失它不会出现在任何车型的 JSON 里。这和 2.4 节里的校验 SQL 是呼应的——导 JSON 时如果发现 total 数量明显比题库总数少优先排查关联表。还有一个容易忽略的点ORDER BY q.exam_type, q.chapter_id, q.sort_order 保证同一科目、同一章节内的题目顺序稳定这样前端缓存题目的 hash 不会因为顺序抖动失效。3.3 拆文件还是合成一个大 JSON按使用场景决定这里有一个实际项目里反复权衡过的问题JSON 文件是拆成科目一、科目四两个文件还是四个车型各自一个文件还是所有数据合成一个我给三个方案按不同部署环境选。方案 A按车型拆。小车一个 JSON客车一个 JSON货车一个 JSON摩托车一个 JSON。App 首次启动时按用户选择的准驾车型拉取对应文件省流量且每个文件都在 3MB 以内解析耗时低。方案 B按科目拆。所有车型的科目一合成一个文件科目四合成一个文件。适合 Web 端一次性加载前端做客户端过滤避免频繁切换题型文件。方案 C全量一个文件。只适合局域网模拟考试系统因为考场 PC 硬盘大、网络稳定一次加载进去后随机组卷完全走内存。我一般推荐方案 A。表面上看四个文件比一个文件管理成本高但实际上增量更新的成本低——某个车型的题库有新增只需要重新导出对应车型的 JSON其他文件不动。而且驾校用户的准驾车型是固定的小车学员大概率一辈子不会点开货车题库按车型拆文件能让首屏加载数据量最小。如果做到按科目再拆一层那就是八个文件管理稍繁琐但流量最省。具体可以根据前端团队习惯来不必过度设计。3.4 JSON 格式的兼容性处理老版本 App 拿新字段怎么办JSON 导出去了但有个隐藏问题正在线上的 App 版本可能只认旧字段而导出的新 JSON 里加了新字段。前端如果直接 res.data.subject1[0].newField老版本会崩溃。所以导出脚本里要做兼容处理常见做法有两种一是保留旧字段的同时新增字段由后端维护两套字段命名二是在 JSON 顶层加 formatVersion 字段前端先判断版本号再决定解析方式。{ formatVersion: 2, vehicleType: 1, updateTime: 2025-01-15 10:00:00, total: 1330, data: { subject1: [], subject4: [] } }这个 formatVersion 字段的价值在于后端可以同时发布 formatVersion 1 和 formatVersion 2 两份文件老版本 App 继续请求 v1 文件新版本请求 v2 文件。如果嫌维护两套文件麻烦最低限度也要做到——前端解析 JSON 时对每个字段做空值兜底比如 content item.content || 题干缺失避免 undefined 渲染导致白屏。这个细节在刷题类 App 的崩溃统计里很常见属于典型的「导出没坑解析有坑」。另一个兼容点是 options 字段早期 JSON 里 options 是数组 [A选项内容,B选项内容]后来改成对象 {A:A选项内容}这种结构变化必须靠 formatVersion 来隔离不能靠前端兜底。4. 图片素材的组织与匹配科目一科目四图片题的存储和路径策略4.1 图片素材该放数据库还是文件系统延迟和备份的取舍科目一科目四的图片题占比不低尤其科目一交通标志、交警手势、仪表盘指示灯这些题目几乎都配图。早期有团队把图片直接存进 MySQL 的 BLOB 字段或 SQL Server 的 varbinary理由是「备份方便、数据一致性好」。实际运行后会发现两个问题一是几百张 1MB 左右的图片全部读进数据库连接再传给前端接口响应时间从 60ms 涨到 800ms二是 MySQL 的 binlog 会因为这些大字段爆炸主从同步也变慢。所以这个标题里提到的「图片素材」放在 SQL 表和 JSON 之外单独管理是正确做法。我一般建议的目录结构是这样的/images /subject1 /small_car /signs 1001.jpg 1002.png /bus /signs 2001.jpg /subject4 /small_car /scenarios 3001.jpg图片文件名和 question 表里的 image_url 字段对应image_url 只存相对路径如「/images/subject1/small_car/signs/1001.jpg」。部署时把整个 images 目录放到 Nginx 或 CDN 的静态根目录图片访问不经过后端应用服务器响应速度最快。如果必须离线部署到驾校机房那么把 images 目录直接拷贝到本地服务器App 的图片 baseURL 配置成局域网地址即可。这个目录结构的另一个好处是按车型和科目分目录后更新某个车型的素材不会影响其他目录配合版本发布时直接用 rsync 增量同步。4.2 图片文件名清洗Excel 里的「图1」怎么映射成实际文件这是整个题库搭建过程中最脏最累但最影响体验的一步。原始题库 Excel 里的图片列可能写的是「图1」「图一」「signs/1001.jpg」而实际素材文件夹里的文件名是「IMG_20230101_1001.JPG」。直接靠肉眼改 Excel 不现实推荐写一段 Python 脚本做模糊匹配提取 Excel 图片列中的数字部分和实际图片文件名中的数字部分做映射然后把图片文件批量重命名成规范格式。import os import re import shutil def extract_number(filename): # 从文件名中提取最后一组连续数字 nums re.findall(r\d, filename) return nums[-1] if nums else None def rename_images(src_dir, dst_dir): os.makedirs(dst_dir, exist_okTrue) mapping {} for fname in os.listdir(src_dir): num extract_number(fname) if num is None: print(f[跳过] 无法提取编号: {fname}) continue ext os.path.splitext(fname)[1].lower() if ext not in [.jpg, .jpeg, .png, .webp]: continue new_name f{num}{ext} src_path os.path.join(src_dir, fname) dst_path os.path.join(dst_dir, new_name) if os.path.exists(dst_path): print(f[跳过] 目标已存在: {new_name}) continue shutil.copy2(src_path, dst_path) mapping[num] new_name print(f{fname} - {new_name}) return mapping这段脚本的 extract_number 用的是 re.findall(r\d, filename) 提取所有数字组并取最后一组是因为很多图片文件名里既有拍摄日期又有题目编号比如「IMG_20230101_1001.JPG」最后一组数字 1001 才是题目编号。但如果原始文件命名规范是「题号1001.JPG」且没有多余数字这个逻辑依然适用。重命名完成后建议生成一份 mapping 字典导出成 CSV方便核对有没有重复编号——这是常见坑两个不同内容的图片文件名里包含相同的数字组重命名后会互相覆盖这时需要人工介入判断哪个是正确配图。另外处理文件名时最好统一转成小写扩展名避免 Windows 和 Linux 下大小写敏感带来的 404 问题。4.3 图片 URL 的动态拼接离线环境下的 baseURL 配置题目 JSON 里的 imageUrl 字段存的是相对路径。前端拿到后需要拼上图片服务器的 baseURL 才能显示。这个 baseURL 在开发环境是 http://localhost:8080测试环境是 http://192.168.1.100:8080生产环境可能是 https://cdn.example.com。如果把这个 baseURL 硬编码在 JSON 文件里换一次环境就要重新导一次 JSON很蠢。正解是前端配置化App 的 config.js 里维护一个 imageBaseURL图片 url 拼接用 imageBaseURL imageUrl。离线部署时只需要改驾校机房服务器的 IP 即可。这个思路在 Web 端也一样页面的 标签的 src 属性用拼接后的完整地址。为了调试方便还可以在 config 里加一个开关isOffline 为 true 时imageBaseURL 默认取 window.location.origin这样部署到任意 IP 的服务器上都不用改配置。注意拼接时处理斜杠imageUrl 以 / 开头imageBaseURL 末尾就不要带 /不然会拼出双斜杠虽然大多数静态服务器能容忍但个别 CDN 会回源失败。5. SQL Server 和 MySQL 下的导入导出差异科目一科目四题库迁移避坑指南5.1 SQL Server 的使用场景为什么驾校机房还大量用 SQL Server这里要回应一下大量出现的 SQL Server 相关搜索。虽然我用 MySQL 举例但驾校老系统里 SQL Server 的出现频率相当高。早年的驾校考试培训系统很多是 .NET 开发的数据库理所当然选了 SQL Server。现在做题库迁移或数据对接时经常要面对「原库是 SQL Server,新库是 MySQL」的局面。两边语法差异不大但有几个坑必须单独讲。驾校机房里的 SQL Server 版本跨度极大从 2008 R2 到 2019 都有。老版本数据库的排序规则可能是 Chinese_PRC_CI_AS导出的中文在 MySQL 里可能出现乱码2008 R2 的STRING_AGG函数不可用只能用FOR XML PATH拼接字符串。所以在写迁移脚本之前先确认 SQL Server 的版本和排序规则能省掉很多莫名其妙的乱码问题。5.2 从 SQL Server 导出 CSV 再导入 MySQL编码和格式的坑从 SQL Server 导出数据图形界面里可以右键数据库 → 任务 → 导出数据选择平面文件目标。但这样导出的 CSV 默认编码可能是 UTF-8也可能带 BOM而且文本限定符跟 MySQL LOAD DATA 的默认配置不一致。带 BOM 的 CSV 会在 MySQL 导入时把第一列的第一个字段吃进一个不可见字符\xEF\xBB\xBF导致第一道题的 ID 变成字符串排序错乱。更稳的做法是用 BCP 命令或 SQL 查询生成标准化 CSV。-- SQL Server 中查询并按固定格式拼接 SELECT CAST(id AS VARCHAR(20)) , CAST(exam_type AS VARCHAR(2)) , CAST(question_type AS VARCHAR(2)) , REPLACE(content, , ) , ISNULL(option_a, ) , ISNULL(option_b, ) , ISNULL(option_c, ) , ISNULL(option_d, ) , answer , ISNULL(image_url, ) , ISNULL(CAST(chapter_id AS VARCHAR(10)), ) FROM question这段 SQL 有几个注意点。content 字段用单引号包围并且把内容里的单引号替换成两个单引号这样导出的 CSV 里即使题干包含逗号和引号导入 MySQL 时配合 OPTIONALLY ENCLOSED BY 也能正确解析。ISNULL 把 NULL 转成空字符串避免导出文件里出现 NULL 文本被 MySQL 误读成字符串。如果 SQL Server 2008 R2 这种老版本不支持某些字符串函数用 CASE WHEN 替代即可。SQL Server 导出 CSV 后我更推荐用 Navicat 或 DBeaver 的导入功能直接灌进 MySQL而不是再用 LOAD DATA——因为图形化工具会自动处理 SQL Server 导出的 NULL 字符串和 Unicode 字符减少一次手工清洗。但无论用哪种方式导入完成后都要执行一遍 2.4 节里的校验 SQL再抽几道带图片的题对比原库确认 content 和 answer 没有错位。我见过一个案例原库的 answer 字段存的是「A, B」带空格导入 MySQL 后 answer 是「A, B」和标准「A,B」格式不一致导致前端判断答案时永远匹配不上。5.3 自增 ID 的差异SQL Server 迁移到 MySQL 时的主键断裂问题SQL Server 的 IDENTITY 列在迁移到 MySQL 时如果原表数据已经有序最好显式保留原有 ID 而不让 MySQL 重新生成。常见做法是在建表时不指定 AUTO_INCREMENT导入数据后再用 ALTER TABLE 设置自增起点。如果直接让 MySQL 的 AUTO_INCREMENT 重新分配 ID会导致两个后果一是题库文件里已有的图片文件名编号和数据库 ID 的映射全部失效因为图片文件名的数字是从原 SQL Server 的 ID 来的二是历史答题记录里的 question_id 全部对不上学员之前做过的错题记录全废了。ALTER TABLE question AUTO_INCREMENT 100000;这条命令的意图是导入完成后把自增起点设置成一个比较大的值比如十万避免新录入的题和导入的旧题 ID 冲突。但要注意这个操作最好在导入后、业务接入前完成一旦有用户开始答题并产生记录再改自增起点就容易引发重复主键。另一个容易忽略的点导入时如果 CSV 里包含 id 列MySQL 的 LOAD DATA 默认会使用文件里的 id 值只有当 id 列不在文件里时才会走自增。所以如果 CSV 有 id 列导入后不需要额外处理自增起点如果 CSV 没导 id 列才需要 ALTER TABLE 设置。5.4 JSON 文件的编码陷阱UTF-8 without BOM 才是标准JSON 文件导出后最常见的线上事故是前端在 Android 手机上解析 JSON 报解析异常但在 Chrome 里预览正常。排查到最后发现是 Python 的 open 函数写文件时默认编码依赖操作系统区域设置在 Windows Server 上可能生成 GBK 编码的 JSON 文件。解决方式是在 open 时显式指定 encodingutf-8并且在写文件时不要加 BOM。Java 和一部分老版本 Android WebView 对带 BOM 的 UTF-8 JSON 解析会出错。with open(output_path, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)这里没有加 encoding 参数的情况下Python 3 在 Linux 上默认 UTF-8但在 Windows 上会使用 ANSI 编码。所以跨平台写文件时encodingutf-8 必须显式写出来。这是新手最容易忽视的一行参数。如果文件已经在 Windows 上生成了 GBK 编码可以用 iconv 命令转码iconv -f GBK -t UTF-8 questions_car.json questions_car_utf8.json。转完再检查一遍中文题干里有没有乱码字符比如「驾驶」变成「驾驶」之类的替换符。6. 科目一科目四题库数据校验与后续演进把校验脚本做成题库发布流水线的一部分6.1 三张表联合校验题目、车型关联、图片素材的一致性题库做完不等于能用。我习惯在每次导出 JSON 后跑一遍校验脚本把「题目主表」「车型关联表」「图片文件列表」三方对齐。校验内容有三项第一每个 question_id 都至少关联一个车型第二每条记录的 image_url 对应的图片文件真实存在于 images 目录第三answer 字段里的选项字母必须存在于 option_a 到 option_d 之中。import os import json def validate_question_bank(json_path, images_root): with open(json_path, encodingutf-8) as f: data json.load(f) errors [] all_count 0 for key in [subject1, subject4]: for q in data[data].get(key, []): all_count 1 # 校验答案字母合法性 answer_letters set(q[answer].replace(,, )) valid_letters {k for k, v in q[options].items() if v} if not answer_letters.issubset(valid_letters): errors.append(f题ID {q[id]} 答案包含无选项内容: {q[answer]}) # 校验图片文件存在 if q[imageUrl]: img_path os.path.join(images_root, q[imageUrl].lstrip(/)) if not os.path.exists(img_path): errors.append(f题ID {q[id]} 图片缺失: {q[imageUrl]}) if errors: print(f共检查 {all_count} 题发现问题 {len(errors)} 条) for e in errors[:10]: print(e) return False else: print(f校验通过共 {all_count} 题) return True这个脚本的校验逻辑不复杂但价值在于每次更新题库后都能自动跑一遍而不是靠人工抽查。在实际项目里我会把它接到 CI 流程上题库维护人员在后台点「发布」按钮时后台自动执行「导出 JSON → 校验 → 推送到 CDN → 刷新缓存」四步。如果校验失败发布流程中断CDN 上保留的还是旧版本题库不会把有问题的数据发出去。校验脚本里还应该加一个选项完整性检查多选题答案如果是「A,C」但选项 C 的内容为空前端渲染时会出现一个空选项按钮用户点了没反应。这种情况在判断题里尤其常见——判断题的 4 个选项只有 A 和 B 有值C 和 D 为空是合法的但多选题如果 C 为空就肯定有问题。所以校验时要把 question_type 字段也读出来判断题只校验前两个选项多选和单选校验四个选项。这里需要把 questionType 放进 JSON 的 item 里第 3 章导出的脚本已经包含了这个字段所以校验脚本可以直接拿到。6.2 增量更新怎么做version 字段和全量兜底题库发布一次全量 JSON 后后续可能每周都有小改动比如新增 20 道摩托车题、修改 3 个错别字。如果每次都重新导出全量 JSON 并覆盖上传文件体积不变但 CDN 的缓存刷新成本高。常见做法是引入增量更新协议JSON 文件里带一个 questionVersion 字段前端保存上次拉取的版本号下次请求时后端返回 version 和当前版本之间的差异题目。但增量更新对前端逻辑的要求更高要合并本地旧题和新增题、要处理题目被删除的情况。科目一科目四的题库变化频率不高我实际项目的经验是优先做全量 JSON 覆盖最多把全量 JSON 按车型拆成四个文件更新时只覆盖有变化的那个文件。只有当题库单文件超过 20MB、移动端拉取耗时超过 3 秒时再考虑做增量协议。增量更新的坑在于如果前端合并逻辑有 bug会导致用户看到的题目既有旧版又有新版、排序混乱而且这种问题很难在测试环境复现——测试环境的网络快拉全量 JSON 也只要 1 秒合并 bug 根本不会被触发。用 version 字段还有一个好处后端可以精确控制前端缓存失效。CDN 的刷新 API 是异步的刷新指令下发后可能还要几十秒才生效如果旧 JSON 还在 CDN 边缘节点上用户拿到的是旧版本。前端用 version 字段做比对发现本地版本落后就重新拉一次比单纯依赖 CDN 的缓存控制更可靠。具体做法是前端每隔 5 分钟调用一次轻量接口获取 latestVersion不一致时重新请求 JSON 文件。这个接口只返回版本号不会造成数据库压力。6.3 组卷功能对题库数据结构的反推为什么底层题库必须带章节和难度模拟考试和章节练习是科目一科目四的最核心功能。模拟考试的组卷规则是科目一 100 题判断题和单选题按比例分配题目范围覆盖所有章节难度系数符合真实考试的分布。如果题库表里没有 chapter_id 和 difficulty 字段组卷就只能纯随机用户刷十次模拟考可能全是同一章节的题练不到薄弱点。所以题目数据在入库时要给每道题打上章节标签。章节结构本身可以建一张独立的 chapter 表也可以直接用一个 INT 字段按「科目*100 章节序号」编码存储。对于标题里强调的货车、客车、摩托车还要注意不同车型的章节权重不一样——小车题库的章节 7 是「违法行为综合判断」但货车题库没有这一章而是多了「货车专用知识」。这意味着车型关联表和章节结构是一起变化的导出 JSON 时不能只按车型过滤题目还要按车型的章节配置重新排序。def build_chapter_layout(vehicle_type): # 不同车型的章节配置不同这里定义一个示例配置 layouts { 1: {subject1: [1,2,3,4,5,6,7], subject4: [1,2,3,4,5,6,7,8]}, 2: {subject1: [1,2,3,4,5,6], subject4: [1,2,3,4,5,6,7]}, 3: {subject1: [1,2,3,4,5,6], subject4: [1,2,3,4,5,6,7]}, 4: {subject1: [1,2,3,4,5], subject4: [1,2,3,4,5,6]}, } return layouts.get(vehicle_type, layouts[1])这个配置字典在导出 JSON 时可以作为章节排序的依据。前端拿到题目后如果自带 chapterId 和 difficulty 两个字段组卷逻辑可以完全放在客户端随机抽题时按权重抽取即可。如果组卷逻辑放在后端则只要 MySQL 的 question 表里有这两个字段一条带 GROUP BY 的查询就能完成抽题统计。所以 low-level 数据结构的完备性决定了上层功能能做成什么样子不要在导入阶段贪快省字段。6.4 离线考试场景的图片预加载策略标题里强调图片素材是独立资源在实际离线部署时要注意一个问题驾校机房如果没外网App 或考试系统首次启动时需要把图片从本地服务器拉下来。图片总量可能 500MB全部加载完毕要几分钟需要做预加载和进度提示而不是等用户切到图片题时再逐个下载。常见做法是启动页进入后后台静默下载图片到本地沙盒存储下载完成后把本地文件路径映射到 JSON 的 imageUrl 上。这里有一个实际教训不要把所有图片放在一个 ZIP 包让客户端解压因为移动端解压大 ZIP 容易因内存不足被杀进程而且校验 ZIP 完整性要额外算 MD5解压失败后用户体验很糟糕。逐张图片同步下载 断点续传的方案虽然慢一点但稳定性明显更好。每一张图片下载成功后写入本地文件同时更新映射表重启 App 后未完成的下载任务继续跑。下载队列的设计可以简单粗暴先读 JSON 里所有 imageUrl 字段去重后形成一个下载列表有网时逐张拉取失败的重试 3 次记录失败清单下次启动再补。7. 科目一科目四题库的进阶玩法把 SQL 表数据变成错题本和智能组卷的底座题库建设最后不能只停在「能刷题」。如果你投入了这套 SQL JSON 图片素材的底座后续最值得做的是错题本和智能组卷。错题本的数据结构很简单一张 user_answer 表记录 user_id、question_id、selected_answer、is_correct、answer_time然后按 is_correct 0 查错题。但注意题目表一旦更新比如改题号错题本关联的 question_id 可能失效所以要保留一份题目快照或者在导出 JSON 时给每道题加一个「原始题号」字段作为跨版本追踪的主键。智能组卷则可以基于 difficulty 字段和用户的答题正确率做权重分配。比如用户在小车科目一的「交通信号」章节正确率只有 60%那么在模拟考试组卷时这个章节的题目占比可以从标准占比提升 20%。这套逻辑不复杂但需要题库的 difficulty 字段有足够的区分度——如果所有题都是 difficulty1组卷策略就失去了意义。真实考试的难度分布大致是基础题 60%、中等难度 30%、难题 10%导入题库时可以按这个比例给批量打标再人工微调。在实现智能组卷时我用的是一个简单但有效的「章节权重表 随机种子」方案每次组卷前根据用户历史答题记录算出每个章节的正确率正确率低的章节权重乘 1.3正确率高的章节权重乘 0.8然后按权重随机抽题。这样用户每次模拟考都会遇到自己薄弱的章节练习效率比纯随机高很多。具体组卷 SQL 可以这样写先按权重条件筛选出候选题目再通过 ORDER BY RAND() 随机取需要数量。-- 按章节权重从 MySQL 随机抽题 SELECT id, content, answer FROM question WHERE exam_type 1 AND question_type IN (1,2) AND chapter_id IN (4,5) AND status 1 ORDER BY RAND() LIMIT 20;ORDER BY RAND() 在小数据量下没问题一万道题以内的题库随机排序性能可以接受。但如果你把题库扩到五万题以上ORDER BY RAND() 会全表扫描加排序比较慢。替代方案是程序端先查 MIN(id) 和 MAX(id)再用随机数生成偏移量分段查性能会好很多。不过在驾考这个场景下单车型题库一般也就一两千道题ORDER BY RAND() 完全够用不需要过度优化。我在做这类项目时的一个习惯是所有发布操作都通过在 CI 上跑导出和校验脚本完成而不是在管理后台手动点导出。手动操作迟早会漏漏一次就会让线上用户拿到残缺题库。这个教训来自一次事故——当时改了一道题的答案直接改了数据库但没重新导出 JSON客户端拿到的 JSON 里还是旧答案导致大量用户同一道题被判错。从那以后我改了「题库数据入库后必须走完整发布流水线」的规矩。把发布动作标准化、自动化是这个方案最值得投入的地方。希望这套题库搭建思路对你有所帮助不管是自建还是二次开发先把数据底座打好后面的功能和对接都会顺利很多。本文还有配套的精品资源点击获取
驾照科目一科目四离线题库方案：SQL+JSON+图片素材全解析
简介驾考科目一、科目四题库数据包覆盖小车、客车、货车、摩托车四类车型同时提供数据库表结构与JSON两种存储形式既适合学员刷题复习也适合开发者将数据接入考试应用或教学平台。题量方面客车科目一两千一百五十四题、科目四两千一百二十六题小车科目一一千六百题、科目四一千三百题摩托车科目一四百四十六题、科目四三百八十三题货车科目一两千一百六十二题、科目四一千二百零六题各科目题量充足便于分类强化训练。压缩包共包含两千个文件以一千九百九十五个webp图片为主体另有SQL数据库脚本、JSON数据文件和一张GIF示意素材整体大小约一百零三兆。图片素材可辅助图文和情景类试题呈现SQL与JSON数据能直接导入数据库或供前端解析节省数据整理时间。目前该题库已有一千零六人学习下载按车型与科目分模块组织结构清晰适合快速搭建刷题环境或进行试题结构分析。1. 驾照题库离线化为什么 SQL 表数据和 JSON 格式能成为科目一科目四的标配做驾考培训系统和模拟考试 App 的同学应该都经历过这种场景甲方一个月后要上线科目一题库却还在一个 Excel 里来回传图片素材用微信发。等到联调的时候发现题目在 SQL Server 里答案在 JSON 文件里图片在另一个服务器上三套数据对不上。这套「驾照考试科目一科目四题库 SQL 表数据 JSON 格式 图片素材」的离线题库方案本质上是把题库做成了三层解耦的结构题目和答案进关系型数据库用于按车型小车、客车、货车、摩托车做随机组卷和正确率统计JSON 文件负责给前端和接口层做缓存和热更新图片素材单独挂载走 CDN 或本地静态目录。为什么要用 SQL 表数据而不是纯 JSON?因为科目一科目四的题目类型不是只有单选还有判断、多选且不同车型的题目范围有交集也有差异。纯 JSON 做静态展示还行一旦要按用户答题记录算通过率、按章节出题、做错题本SQL 的关联查询和索引优势就出来了。而 JSON 格式的价值在于它能把 SQL 查出来的结果序列化成前端直接能用的结构尤其是小程序和 H5 端根本不需要后端二次加工。图片素材单独拎出来是因为一张科目一图片题可能 200KB 到 1MB如果直接塞进 SQL Server 的 varbinary 字段备份和查询都会变慢。这套方案的适用人群很明确驾考培训机构的自研系统、驾培 SaaS 平台的题库模块、以及想离线部署到驾校机房的教学软件。关键点是「离线」——很多驾校的网络环境并不稳定考场和训练场甚至没有外网所以题库必须能整体导出、导入、增量更新。后面我会按「建表 → 导 JSON → 配图片 → 排错」的顺序把这套骨架完整落地一遍。2. 建一张能扛住四种车型的科目一科目四题库表表结构设计与数据组织2.1 先定数据边界为什么科目一科目四要分开但又要能合并查科目一考的是道路交通安全法规和交通信号科目四考的是安全文明驾驶常识题面不一样但底层字段几乎一致。所以在设计 SQL 表时常见做法不是建两张独立表而是一张题表单加一个 exam_type 字段区分科目。这样做的收益在后续开发里非常明显错题本、答题记录、收藏夹都可以只关联 question_id不用关心它属于哪个科目组卷时再用 exam_type 做过滤条件。如果一开始建了两张表后面每个功能都要写 UNION 查询SQL 复杂度和出错概率都会上升。车型维度小车、客车、货车、摩托车则是典型的多对多关系。一道题可能同时适用于小车和货车比如「驾驶机动车在高速公路上发生故障时应在车后多少米设置警告标志」小车和货车都考但摩托车题库里没有这道题。所以不能把车型做成题表里的一个字段而是要单独建一张车型关联表或者用 JSON 数组字段保存适用车型。考虑到要支持 SQL 查询比如「查所有摩托车题」我建议建一张中间表 question_vehicle而不是在题表里存逗号分隔的字符串。2.2 核心建表语句把图片路径变成冗余字段而不是存二进制下面是一份可直接复用的 MySQL 建表脚本SQL Server 的语序稍有不同但字段设计可以直接平移。如果项目用的是 SQL Server把 AUTO_INCREMENT 换成 IDENTITY(1,1)把 TINYINT 换成 SMALLINT 就行。为什么不用 MongoDB因为题目之间有章节、车型、考试类型三种维度关系型数据库的索引和 JOIN 在这个场景下仍然是最省心的。CREATE TABLE question ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 题目ID, exam_type TINYINT NOT NULL COMMENT 1科目一 4科目四, question_type TINYINT NOT NULL COMMENT 1判断题 2单选题 3多选题, content TEXT NOT NULL COMMENT 题干内容不含图片, option_a VARCHAR(255) DEFAULT NULL COMMENT 选项A判断题可为空, option_b VARCHAR(255) DEFAULT NULL COMMENT 选项B, option_c VARCHAR(255) DEFAULT NULL COMMENT 选项C, option_d VARCHAR(255) DEFAULT NULL COMMENT 选项D, answer VARCHAR(10) NOT NULL COMMENT 正确答案多选用逗号分隔如 A,C, image_url VARCHAR(500) DEFAULT NULL COMMENT 图片相对路径或URL无图为NULL, chapter_id INT DEFAULT NULL COMMENT 章节ID用于按章节练习, difficulty TINYINT DEFAULT 1 COMMENT 1易 2中 3难, sort_order INT DEFAULT 0 COMMENT 同章节内排序号, status TINYINT DEFAULT 1 COMMENT 1启用 0停用, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_exam_type (exam_type), KEY idx_chapter_id (chapter_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT驾照科目一科目四题库主表; CREATE TABLE question_vehicle ( question_id BIGINT UNSIGNED NOT NULL, vehicle_type TINYINT NOT NULL COMMENT 1小车 2客车 3货车 4摩托车, PRIMARY KEY (question_id, vehicle_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT题目车型关联表;这段建表逻辑里有几个地方是实际项目里踩过坑后总结出来的。首先是 image_url 字段用相对路径而不是完整 URL——离线环境下服务器的域名或 IP 可能随时变存完整 URL 会导致图片全部失效。其次是 answer 字段用字符串而不是单独的答案表科目一科目四的答案本质上就是「A」「B」「C」「D」「对」「错」这几种多选答案用逗号拼接查询时配合程序端拆解都很快不需要再建一张答案表增加 JOIN 复杂度。2.3 批量导入的正确姿势不要一条条 INSERT要拼批量语句拿到一份 Excel 或 CSV 格式的原始题库后第一件事不是写导入程序而是先做数据清洗。常见的脏数据有题号列混入「第1题」这样的中文、答案列写成「正确」「错误」而不是「A」「B」、图片列写的是「图1.jpg」但实际素材文件叫「IMG_20230101_1.jpg」。清洗完再导入推荐用 LOAD DATA 或者构造批量 INSERT,别用循环单条 INSERT,不然一万道题可能要跑十几分钟。LOAD DATA LOCAL INFILE /data/question_clean.csv INTO TABLE question CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, exam_type, question_type, content, option_a, option_b, option_c, option_d, answer, image_url, chapter_id, difficulty) SET exam_type IF(exam_type , 1, exam_type), question_type IF(question_type , 1, question_type), content NULLIF(TRIM(content), ), image_url IF(image_url , NULL, image_url);这里有几个参数需要说明。FIELDS TERMINATED BY , 表示 CSV 的列分隔符是英文逗号如果原文件是分号分隔或 Tab 分隔要对应改成 ; 或 \t。OPTIONALLY ENCLOSED BY 是为了处理内容里含逗号的题干——题干可能写成「驾驶机动车时不可以以下哪项是错误的」如果不用引号包围LOAD DATA 会把一个字段拆成多列。IGNORE 1 LINES 跳过表头。SET 子句里的 IF 和 NULLIF 是在导入过程中做轻度清洗空字符串转 NULL、前后空格去掉、空值给默认科目类型。2.4 车型关联数据的补齐用一条 SQL 处理多对多关系题目主表导入完成后接着处理 question_vehicle 表。原始题库里车型信息的表现形式五花八门有的在题号前带「C1」前缀有的在 Excel 里单独一列写「小车/客车」还有的只在小车题库里出现、没有标注。这里我一般用一个两步走的方法先按文件名或 Excel 列把批量关联关系整理成一张临时表再用一条 INSERT ... SELECT 语句把临时表数据刷进 question_vehicle。CREATE TEMPORARY TABLE tmp_vehicle_map ( question_id BIGINT NOT NULL, vehicle_type TINYINT NOT NULL ); INSERT INTO question_vehicle (question_id, vehicle_type) SELECT t.question_id, t.vehicle_type FROM tmp_vehicle_map t; -- 校验查一下有多少题没有任何车型关联 SELECT COUNT(*) FROM question q LEFT JOIN question_vehicle qv ON q.id qv.question_id WHERE qv.question_id IS NULL;上面最后一条校验 SQL 务必执行这是最容易翻车的地方。如果某道题没有关联任何车型那么在小程序端无论选小车还是货车这道题都不会出现在练习列表里用户会反馈「题库少了题」但实际上题在库里只是关联表漏了。出现这种情况的原因通常是导入车型关联临时表的时候Excel 里的题号跟主表导入后的自增 ID 对不上——原始题号是 1 到 9999但 MySQL 自增 ID 可能从 10001 开始这时候需要在临时表里先做题号到 ID 的映射转换不能直接把原始题号当 ID 用。3. 把 SQL 表数据导出成 JSON 格式从小程序适配到增量更新3.1 为什么不在接口层实时查库而要先导出一份 JSON科目一科目四的题库有一个明显特点读多写少。一份题库数据可能在几个月内都不会变化但会被几千个用户同时刷题。如果每次刷题都实时查 MySQL数据库的 QPS 会很高而且题库数据的价值密度低——用户做一道题只需要题干、选项、答案、图片 URL 四个字段没必要每次都把整行数据拖出来。常见做法是管理后台更新题库后把 SQL 表数据导出成 JSON 文件推送到对象存储或服务器静态目录前端直接请求 JSON 文件解析不进数据库。这个方案对小程序的收益尤其明显。小程序包体有限制但远程 JSON 可以随便做大而且 JSON 结构天然契合小程序 setData 的数据格式。另一个收益是离线把 JSON 文件打包进 App 资源目录后用户在无网环境下也能刷题这也是这个标题里「离线题库」的核心实现点。相比接口实时返回 JSON,静态 JSON 还能省掉一层后端渲染和序列化的耗时模拟考试场景下题目加载速度更快。3.2 用 Python 脚本一次导出多车型 JSON结构设计是关键导出 JSON 不能简单地把每一行 SQL 查询结果塞进数组要按前端渲染的需求设计嵌套结构。科目一题目通常带图片科目四可能带视频或情景题前端需要一个统一的题目对象协议。下面是我常用的导出脚本核心片段用 PyMySQL 查库、用 json 模块落盘依赖少任何一台机器装了 Python 就能跑。import pymysql import json from collections import defaultdict def export_questions_by_vehicle(vehicle_type1, output_pathquestions_car.json): conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedrive_test, charsetutf8mb4 ) sql SELECT q.id, q.exam_type, q.question_type, q.content, q.option_a, q.option_b, q.option_c, q.option_d, q.answer, q.image_url, q.chapter_id, q.difficulty FROM question q INNER JOIN question_vehicle qv ON q.id qv.question_id WHERE qv.vehicle_type %s AND q.status 1 ORDER BY q.exam_type, q.chapter_id, q.sort_order with conn.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, (vehicle_type,)) rows cursor.fetchall() questions_by_exam defaultdict(list) for row in rows: # 把None值转成空字符串避免JSON里出现null导致前端报错 item { id: row[id], examType: row[exam_type], questionType: row[question_type], content: row[content] or , options: { A: row[option_a] or , B: row[option_b] or , C: row[option_c] or , D: row[option_d] or }, answer: row[answer], imageUrl: row[image_url] or , chapterId: row[chapter_id], difficulty: row[difficulty] } questions_by_exam[row[exam_type]].append(item) result { vehicleType: vehicle_type, updateTime: 2025-01-15 10:00:00, total: len(rows), data: { subject1: questions_by_exam.get(1, []), subject4: questions_by_exam.get(4, []) } } with open(output_path, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2) print(fexported {len(rows)} questions to {output_path})这个脚本里的几个参数值得细说。cursor 使用 DictCursor这样 row 就是一个字典可以直接用字段名取值比元组下标清晰得多。ensure_asciiFalse 必须加不然中文题干会被转成 \uXXXX 转义序列JSON 文件体积会膨胀 40% 左右而且肉眼没法排查数据。indent2 是给开发阶段调试用的方便直接打开 JSON 文件检查题目内容正式上线时如果对体积敏感可以改成 indentNone 并用 separators(,, :) 压缩输出一万道题的 JSON 大概能从 5MB 压到 3.5MB。另外注意我在查询 SQL 里用了 INNER JOIN question_vehicle这意味着如果某道题在关联表里缺失它不会出现在任何车型的 JSON 里。这和 2.4 节里的校验 SQL 是呼应的——导 JSON 时如果发现 total 数量明显比题库总数少优先排查关联表。还有一个容易忽略的点ORDER BY q.exam_type, q.chapter_id, q.sort_order 保证同一科目、同一章节内的题目顺序稳定这样前端缓存题目的 hash 不会因为顺序抖动失效。3.3 拆文件还是合成一个大 JSON按使用场景决定这里有一个实际项目里反复权衡过的问题JSON 文件是拆成科目一、科目四两个文件还是四个车型各自一个文件还是所有数据合成一个我给三个方案按不同部署环境选。方案 A按车型拆。小车一个 JSON客车一个 JSON货车一个 JSON摩托车一个 JSON。App 首次启动时按用户选择的准驾车型拉取对应文件省流量且每个文件都在 3MB 以内解析耗时低。方案 B按科目拆。所有车型的科目一合成一个文件科目四合成一个文件。适合 Web 端一次性加载前端做客户端过滤避免频繁切换题型文件。方案 C全量一个文件。只适合局域网模拟考试系统因为考场 PC 硬盘大、网络稳定一次加载进去后随机组卷完全走内存。我一般推荐方案 A。表面上看四个文件比一个文件管理成本高但实际上增量更新的成本低——某个车型的题库有新增只需要重新导出对应车型的 JSON其他文件不动。而且驾校用户的准驾车型是固定的小车学员大概率一辈子不会点开货车题库按车型拆文件能让首屏加载数据量最小。如果做到按科目再拆一层那就是八个文件管理稍繁琐但流量最省。具体可以根据前端团队习惯来不必过度设计。3.4 JSON 格式的兼容性处理老版本 App 拿新字段怎么办JSON 导出去了但有个隐藏问题正在线上的 App 版本可能只认旧字段而导出的新 JSON 里加了新字段。前端如果直接 res.data.subject1[0].newField老版本会崩溃。所以导出脚本里要做兼容处理常见做法有两种一是保留旧字段的同时新增字段由后端维护两套字段命名二是在 JSON 顶层加 formatVersion 字段前端先判断版本号再决定解析方式。{ formatVersion: 2, vehicleType: 1, updateTime: 2025-01-15 10:00:00, total: 1330, data: { subject1: [], subject4: [] } }这个 formatVersion 字段的价值在于后端可以同时发布 formatVersion 1 和 formatVersion 2 两份文件老版本 App 继续请求 v1 文件新版本请求 v2 文件。如果嫌维护两套文件麻烦最低限度也要做到——前端解析 JSON 时对每个字段做空值兜底比如 content item.content || 题干缺失避免 undefined 渲染导致白屏。这个细节在刷题类 App 的崩溃统计里很常见属于典型的「导出没坑解析有坑」。另一个兼容点是 options 字段早期 JSON 里 options 是数组 [A选项内容,B选项内容]后来改成对象 {A:A选项内容}这种结构变化必须靠 formatVersion 来隔离不能靠前端兜底。4. 图片素材的组织与匹配科目一科目四图片题的存储和路径策略4.1 图片素材该放数据库还是文件系统延迟和备份的取舍科目一科目四的图片题占比不低尤其科目一交通标志、交警手势、仪表盘指示灯这些题目几乎都配图。早期有团队把图片直接存进 MySQL 的 BLOB 字段或 SQL Server 的 varbinary理由是「备份方便、数据一致性好」。实际运行后会发现两个问题一是几百张 1MB 左右的图片全部读进数据库连接再传给前端接口响应时间从 60ms 涨到 800ms二是 MySQL 的 binlog 会因为这些大字段爆炸主从同步也变慢。所以这个标题里提到的「图片素材」放在 SQL 表和 JSON 之外单独管理是正确做法。我一般建议的目录结构是这样的/images /subject1 /small_car /signs 1001.jpg 1002.png /bus /signs 2001.jpg /subject4 /small_car /scenarios 3001.jpg图片文件名和 question 表里的 image_url 字段对应image_url 只存相对路径如「/images/subject1/small_car/signs/1001.jpg」。部署时把整个 images 目录放到 Nginx 或 CDN 的静态根目录图片访问不经过后端应用服务器响应速度最快。如果必须离线部署到驾校机房那么把 images 目录直接拷贝到本地服务器App 的图片 baseURL 配置成局域网地址即可。这个目录结构的另一个好处是按车型和科目分目录后更新某个车型的素材不会影响其他目录配合版本发布时直接用 rsync 增量同步。4.2 图片文件名清洗Excel 里的「图1」怎么映射成实际文件这是整个题库搭建过程中最脏最累但最影响体验的一步。原始题库 Excel 里的图片列可能写的是「图1」「图一」「signs/1001.jpg」而实际素材文件夹里的文件名是「IMG_20230101_1001.JPG」。直接靠肉眼改 Excel 不现实推荐写一段 Python 脚本做模糊匹配提取 Excel 图片列中的数字部分和实际图片文件名中的数字部分做映射然后把图片文件批量重命名成规范格式。import os import re import shutil def extract_number(filename): # 从文件名中提取最后一组连续数字 nums re.findall(r\d, filename) return nums[-1] if nums else None def rename_images(src_dir, dst_dir): os.makedirs(dst_dir, exist_okTrue) mapping {} for fname in os.listdir(src_dir): num extract_number(fname) if num is None: print(f[跳过] 无法提取编号: {fname}) continue ext os.path.splitext(fname)[1].lower() if ext not in [.jpg, .jpeg, .png, .webp]: continue new_name f{num}{ext} src_path os.path.join(src_dir, fname) dst_path os.path.join(dst_dir, new_name) if os.path.exists(dst_path): print(f[跳过] 目标已存在: {new_name}) continue shutil.copy2(src_path, dst_path) mapping[num] new_name print(f{fname} - {new_name}) return mapping这段脚本的 extract_number 用的是 re.findall(r\d, filename) 提取所有数字组并取最后一组是因为很多图片文件名里既有拍摄日期又有题目编号比如「IMG_20230101_1001.JPG」最后一组数字 1001 才是题目编号。但如果原始文件命名规范是「题号1001.JPG」且没有多余数字这个逻辑依然适用。重命名完成后建议生成一份 mapping 字典导出成 CSV方便核对有没有重复编号——这是常见坑两个不同内容的图片文件名里包含相同的数字组重命名后会互相覆盖这时需要人工介入判断哪个是正确配图。另外处理文件名时最好统一转成小写扩展名避免 Windows 和 Linux 下大小写敏感带来的 404 问题。4.3 图片 URL 的动态拼接离线环境下的 baseURL 配置题目 JSON 里的 imageUrl 字段存的是相对路径。前端拿到后需要拼上图片服务器的 baseURL 才能显示。这个 baseURL 在开发环境是 http://localhost:8080测试环境是 http://192.168.1.100:8080生产环境可能是 https://cdn.example.com。如果把这个 baseURL 硬编码在 JSON 文件里换一次环境就要重新导一次 JSON很蠢。正解是前端配置化App 的 config.js 里维护一个 imageBaseURL图片 url 拼接用 imageBaseURL imageUrl。离线部署时只需要改驾校机房服务器的 IP 即可。这个思路在 Web 端也一样页面的 标签的 src 属性用拼接后的完整地址。为了调试方便还可以在 config 里加一个开关isOffline 为 true 时imageBaseURL 默认取 window.location.origin这样部署到任意 IP 的服务器上都不用改配置。注意拼接时处理斜杠imageUrl 以 / 开头imageBaseURL 末尾就不要带 /不然会拼出双斜杠虽然大多数静态服务器能容忍但个别 CDN 会回源失败。5. SQL Server 和 MySQL 下的导入导出差异科目一科目四题库迁移避坑指南5.1 SQL Server 的使用场景为什么驾校机房还大量用 SQL Server这里要回应一下大量出现的 SQL Server 相关搜索。虽然我用 MySQL 举例但驾校老系统里 SQL Server 的出现频率相当高。早年的驾校考试培训系统很多是 .NET 开发的数据库理所当然选了 SQL Server。现在做题库迁移或数据对接时经常要面对「原库是 SQL Server,新库是 MySQL」的局面。两边语法差异不大但有几个坑必须单独讲。驾校机房里的 SQL Server 版本跨度极大从 2008 R2 到 2019 都有。老版本数据库的排序规则可能是 Chinese_PRC_CI_AS导出的中文在 MySQL 里可能出现乱码2008 R2 的STRING_AGG函数不可用只能用FOR XML PATH拼接字符串。所以在写迁移脚本之前先确认 SQL Server 的版本和排序规则能省掉很多莫名其妙的乱码问题。5.2 从 SQL Server 导出 CSV 再导入 MySQL编码和格式的坑从 SQL Server 导出数据图形界面里可以右键数据库 → 任务 → 导出数据选择平面文件目标。但这样导出的 CSV 默认编码可能是 UTF-8也可能带 BOM而且文本限定符跟 MySQL LOAD DATA 的默认配置不一致。带 BOM 的 CSV 会在 MySQL 导入时把第一列的第一个字段吃进一个不可见字符\xEF\xBB\xBF导致第一道题的 ID 变成字符串排序错乱。更稳的做法是用 BCP 命令或 SQL 查询生成标准化 CSV。-- SQL Server 中查询并按固定格式拼接 SELECT CAST(id AS VARCHAR(20)) , CAST(exam_type AS VARCHAR(2)) , CAST(question_type AS VARCHAR(2)) , REPLACE(content, , ) , ISNULL(option_a, ) , ISNULL(option_b, ) , ISNULL(option_c, ) , ISNULL(option_d, ) , answer , ISNULL(image_url, ) , ISNULL(CAST(chapter_id AS VARCHAR(10)), ) FROM question这段 SQL 有几个注意点。content 字段用单引号包围并且把内容里的单引号替换成两个单引号这样导出的 CSV 里即使题干包含逗号和引号导入 MySQL 时配合 OPTIONALLY ENCLOSED BY 也能正确解析。ISNULL 把 NULL 转成空字符串避免导出文件里出现 NULL 文本被 MySQL 误读成字符串。如果 SQL Server 2008 R2 这种老版本不支持某些字符串函数用 CASE WHEN 替代即可。SQL Server 导出 CSV 后我更推荐用 Navicat 或 DBeaver 的导入功能直接灌进 MySQL而不是再用 LOAD DATA——因为图形化工具会自动处理 SQL Server 导出的 NULL 字符串和 Unicode 字符减少一次手工清洗。但无论用哪种方式导入完成后都要执行一遍 2.4 节里的校验 SQL再抽几道带图片的题对比原库确认 content 和 answer 没有错位。我见过一个案例原库的 answer 字段存的是「A, B」带空格导入 MySQL 后 answer 是「A, B」和标准「A,B」格式不一致导致前端判断答案时永远匹配不上。5.3 自增 ID 的差异SQL Server 迁移到 MySQL 时的主键断裂问题SQL Server 的 IDENTITY 列在迁移到 MySQL 时如果原表数据已经有序最好显式保留原有 ID 而不让 MySQL 重新生成。常见做法是在建表时不指定 AUTO_INCREMENT导入数据后再用 ALTER TABLE 设置自增起点。如果直接让 MySQL 的 AUTO_INCREMENT 重新分配 ID会导致两个后果一是题库文件里已有的图片文件名编号和数据库 ID 的映射全部失效因为图片文件名的数字是从原 SQL Server 的 ID 来的二是历史答题记录里的 question_id 全部对不上学员之前做过的错题记录全废了。ALTER TABLE question AUTO_INCREMENT 100000;这条命令的意图是导入完成后把自增起点设置成一个比较大的值比如十万避免新录入的题和导入的旧题 ID 冲突。但要注意这个操作最好在导入后、业务接入前完成一旦有用户开始答题并产生记录再改自增起点就容易引发重复主键。另一个容易忽略的点导入时如果 CSV 里包含 id 列MySQL 的 LOAD DATA 默认会使用文件里的 id 值只有当 id 列不在文件里时才会走自增。所以如果 CSV 有 id 列导入后不需要额外处理自增起点如果 CSV 没导 id 列才需要 ALTER TABLE 设置。5.4 JSON 文件的编码陷阱UTF-8 without BOM 才是标准JSON 文件导出后最常见的线上事故是前端在 Android 手机上解析 JSON 报解析异常但在 Chrome 里预览正常。排查到最后发现是 Python 的 open 函数写文件时默认编码依赖操作系统区域设置在 Windows Server 上可能生成 GBK 编码的 JSON 文件。解决方式是在 open 时显式指定 encodingutf-8并且在写文件时不要加 BOM。Java 和一部分老版本 Android WebView 对带 BOM 的 UTF-8 JSON 解析会出错。with open(output_path, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)这里没有加 encoding 参数的情况下Python 3 在 Linux 上默认 UTF-8但在 Windows 上会使用 ANSI 编码。所以跨平台写文件时encodingutf-8 必须显式写出来。这是新手最容易忽视的一行参数。如果文件已经在 Windows 上生成了 GBK 编码可以用 iconv 命令转码iconv -f GBK -t UTF-8 questions_car.json questions_car_utf8.json。转完再检查一遍中文题干里有没有乱码字符比如「驾驶」变成「驾驶」之类的替换符。6. 科目一科目四题库数据校验与后续演进把校验脚本做成题库发布流水线的一部分6.1 三张表联合校验题目、车型关联、图片素材的一致性题库做完不等于能用。我习惯在每次导出 JSON 后跑一遍校验脚本把「题目主表」「车型关联表」「图片文件列表」三方对齐。校验内容有三项第一每个 question_id 都至少关联一个车型第二每条记录的 image_url 对应的图片文件真实存在于 images 目录第三answer 字段里的选项字母必须存在于 option_a 到 option_d 之中。import os import json def validate_question_bank(json_path, images_root): with open(json_path, encodingutf-8) as f: data json.load(f) errors [] all_count 0 for key in [subject1, subject4]: for q in data[data].get(key, []): all_count 1 # 校验答案字母合法性 answer_letters set(q[answer].replace(,, )) valid_letters {k for k, v in q[options].items() if v} if not answer_letters.issubset(valid_letters): errors.append(f题ID {q[id]} 答案包含无选项内容: {q[answer]}) # 校验图片文件存在 if q[imageUrl]: img_path os.path.join(images_root, q[imageUrl].lstrip(/)) if not os.path.exists(img_path): errors.append(f题ID {q[id]} 图片缺失: {q[imageUrl]}) if errors: print(f共检查 {all_count} 题发现问题 {len(errors)} 条) for e in errors[:10]: print(e) return False else: print(f校验通过共 {all_count} 题) return True这个脚本的校验逻辑不复杂但价值在于每次更新题库后都能自动跑一遍而不是靠人工抽查。在实际项目里我会把它接到 CI 流程上题库维护人员在后台点「发布」按钮时后台自动执行「导出 JSON → 校验 → 推送到 CDN → 刷新缓存」四步。如果校验失败发布流程中断CDN 上保留的还是旧版本题库不会把有问题的数据发出去。校验脚本里还应该加一个选项完整性检查多选题答案如果是「A,C」但选项 C 的内容为空前端渲染时会出现一个空选项按钮用户点了没反应。这种情况在判断题里尤其常见——判断题的 4 个选项只有 A 和 B 有值C 和 D 为空是合法的但多选题如果 C 为空就肯定有问题。所以校验时要把 question_type 字段也读出来判断题只校验前两个选项多选和单选校验四个选项。这里需要把 questionType 放进 JSON 的 item 里第 3 章导出的脚本已经包含了这个字段所以校验脚本可以直接拿到。6.2 增量更新怎么做version 字段和全量兜底题库发布一次全量 JSON 后后续可能每周都有小改动比如新增 20 道摩托车题、修改 3 个错别字。如果每次都重新导出全量 JSON 并覆盖上传文件体积不变但 CDN 的缓存刷新成本高。常见做法是引入增量更新协议JSON 文件里带一个 questionVersion 字段前端保存上次拉取的版本号下次请求时后端返回 version 和当前版本之间的差异题目。但增量更新对前端逻辑的要求更高要合并本地旧题和新增题、要处理题目被删除的情况。科目一科目四的题库变化频率不高我实际项目的经验是优先做全量 JSON 覆盖最多把全量 JSON 按车型拆成四个文件更新时只覆盖有变化的那个文件。只有当题库单文件超过 20MB、移动端拉取耗时超过 3 秒时再考虑做增量协议。增量更新的坑在于如果前端合并逻辑有 bug会导致用户看到的题目既有旧版又有新版、排序混乱而且这种问题很难在测试环境复现——测试环境的网络快拉全量 JSON 也只要 1 秒合并 bug 根本不会被触发。用 version 字段还有一个好处后端可以精确控制前端缓存失效。CDN 的刷新 API 是异步的刷新指令下发后可能还要几十秒才生效如果旧 JSON 还在 CDN 边缘节点上用户拿到的是旧版本。前端用 version 字段做比对发现本地版本落后就重新拉一次比单纯依赖 CDN 的缓存控制更可靠。具体做法是前端每隔 5 分钟调用一次轻量接口获取 latestVersion不一致时重新请求 JSON 文件。这个接口只返回版本号不会造成数据库压力。6.3 组卷功能对题库数据结构的反推为什么底层题库必须带章节和难度模拟考试和章节练习是科目一科目四的最核心功能。模拟考试的组卷规则是科目一 100 题判断题和单选题按比例分配题目范围覆盖所有章节难度系数符合真实考试的分布。如果题库表里没有 chapter_id 和 difficulty 字段组卷就只能纯随机用户刷十次模拟考可能全是同一章节的题练不到薄弱点。所以题目数据在入库时要给每道题打上章节标签。章节结构本身可以建一张独立的 chapter 表也可以直接用一个 INT 字段按「科目*100 章节序号」编码存储。对于标题里强调的货车、客车、摩托车还要注意不同车型的章节权重不一样——小车题库的章节 7 是「违法行为综合判断」但货车题库没有这一章而是多了「货车专用知识」。这意味着车型关联表和章节结构是一起变化的导出 JSON 时不能只按车型过滤题目还要按车型的章节配置重新排序。def build_chapter_layout(vehicle_type): # 不同车型的章节配置不同这里定义一个示例配置 layouts { 1: {subject1: [1,2,3,4,5,6,7], subject4: [1,2,3,4,5,6,7,8]}, 2: {subject1: [1,2,3,4,5,6], subject4: [1,2,3,4,5,6,7]}, 3: {subject1: [1,2,3,4,5,6], subject4: [1,2,3,4,5,6,7]}, 4: {subject1: [1,2,3,4,5], subject4: [1,2,3,4,5,6]}, } return layouts.get(vehicle_type, layouts[1])这个配置字典在导出 JSON 时可以作为章节排序的依据。前端拿到题目后如果自带 chapterId 和 difficulty 两个字段组卷逻辑可以完全放在客户端随机抽题时按权重抽取即可。如果组卷逻辑放在后端则只要 MySQL 的 question 表里有这两个字段一条带 GROUP BY 的查询就能完成抽题统计。所以 low-level 数据结构的完备性决定了上层功能能做成什么样子不要在导入阶段贪快省字段。6.4 离线考试场景的图片预加载策略标题里强调图片素材是独立资源在实际离线部署时要注意一个问题驾校机房如果没外网App 或考试系统首次启动时需要把图片从本地服务器拉下来。图片总量可能 500MB全部加载完毕要几分钟需要做预加载和进度提示而不是等用户切到图片题时再逐个下载。常见做法是启动页进入后后台静默下载图片到本地沙盒存储下载完成后把本地文件路径映射到 JSON 的 imageUrl 上。这里有一个实际教训不要把所有图片放在一个 ZIP 包让客户端解压因为移动端解压大 ZIP 容易因内存不足被杀进程而且校验 ZIP 完整性要额外算 MD5解压失败后用户体验很糟糕。逐张图片同步下载 断点续传的方案虽然慢一点但稳定性明显更好。每一张图片下载成功后写入本地文件同时更新映射表重启 App 后未完成的下载任务继续跑。下载队列的设计可以简单粗暴先读 JSON 里所有 imageUrl 字段去重后形成一个下载列表有网时逐张拉取失败的重试 3 次记录失败清单下次启动再补。7. 科目一科目四题库的进阶玩法把 SQL 表数据变成错题本和智能组卷的底座题库建设最后不能只停在「能刷题」。如果你投入了这套 SQL JSON 图片素材的底座后续最值得做的是错题本和智能组卷。错题本的数据结构很简单一张 user_answer 表记录 user_id、question_id、selected_answer、is_correct、answer_time然后按 is_correct 0 查错题。但注意题目表一旦更新比如改题号错题本关联的 question_id 可能失效所以要保留一份题目快照或者在导出 JSON 时给每道题加一个「原始题号」字段作为跨版本追踪的主键。智能组卷则可以基于 difficulty 字段和用户的答题正确率做权重分配。比如用户在小车科目一的「交通信号」章节正确率只有 60%那么在模拟考试组卷时这个章节的题目占比可以从标准占比提升 20%。这套逻辑不复杂但需要题库的 difficulty 字段有足够的区分度——如果所有题都是 difficulty1组卷策略就失去了意义。真实考试的难度分布大致是基础题 60%、中等难度 30%、难题 10%导入题库时可以按这个比例给批量打标再人工微调。在实现智能组卷时我用的是一个简单但有效的「章节权重表 随机种子」方案每次组卷前根据用户历史答题记录算出每个章节的正确率正确率低的章节权重乘 1.3正确率高的章节权重乘 0.8然后按权重随机抽题。这样用户每次模拟考都会遇到自己薄弱的章节练习效率比纯随机高很多。具体组卷 SQL 可以这样写先按权重条件筛选出候选题目再通过 ORDER BY RAND() 随机取需要数量。-- 按章节权重从 MySQL 随机抽题 SELECT id, content, answer FROM question WHERE exam_type 1 AND question_type IN (1,2) AND chapter_id IN (4,5) AND status 1 ORDER BY RAND() LIMIT 20;ORDER BY RAND() 在小数据量下没问题一万道题以内的题库随机排序性能可以接受。但如果你把题库扩到五万题以上ORDER BY RAND() 会全表扫描加排序比较慢。替代方案是程序端先查 MIN(id) 和 MAX(id)再用随机数生成偏移量分段查性能会好很多。不过在驾考这个场景下单车型题库一般也就一两千道题ORDER BY RAND() 完全够用不需要过度优化。我在做这类项目时的一个习惯是所有发布操作都通过在 CI 上跑导出和校验脚本完成而不是在管理后台手动点导出。手动操作迟早会漏漏一次就会让线上用户拿到残缺题库。这个教训来自一次事故——当时改了一道题的答案直接改了数据库但没重新导出 JSON客户端拿到的 JSON 里还是旧答案导致大量用户同一道题被判错。从那以后我改了「题库数据入库后必须走完整发布流水线」的规矩。把发布动作标准化、自动化是这个方案最值得投入的地方。希望这套题库搭建思路对你有所帮助不管是自建还是二次开发先把数据底座打好后面的功能和对接都会顺利很多。本文还有配套的精品资源点击获取
