简介基于GB/T 4754-2002、2011、2017三版国民经济行业分类标准本数据包提供可直接导入MySQL的行业分类代码表面向需要做行业维度数据清洗与标准化的大数据技术人员。每个表均按门类、大类、中类、小类四级拆分以“A0111”与“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”的形式一一对应并附有2002与2011年的历史代码对照方便跨版本追溯与映射。压缩包内共3个sql文件分别对应2002、2011、2017三个年份文件总大小仅44KB结构简洁导入后即可用于行业字段匹配、归类或统计分析。目前已有3038人学习下载适合需要快速获得标准行业分类数据、减少手工整理成本的数据分析或数据仓库建设人员。1. 这个 RAR 里到底装了什么三版国标分类代码值不值得拿来建库拿到这份2002_2011_2017国民经济行业分类与代码mysql数据四级分类文件.rar先别急着解压去找 DBA 导数据。它装的是三个版本2002、2011、2017的国民经济行业分类四级数据门类、大类、中类、小类每一级都有独立编码版本之间还存在字母漂移和类目拆分。做过企业库、统计报表或行业归集模块的开发者大概率体会过“手里有张表、用起来全是场外规则”的无力感。这套数据的价值恰恰在于把年代口径和层级结构结构化省掉人工查表对码。下面这套流程是我处理同类数据时反复在用的编码规则、建表导入、查询下钻、版本对比和踩坑点读写两端都能直接抄。2. 动手建库之前先把四级编码规则和三版差异看懂四级分类的坑大多数出在“以为看懂了编码”就急着写 SQL。这一章先把编码规则和版本差异讲透再决定存储模型。顺序反了后面所有查询都可能跑在错误假设上。2.1 四级编码规则一个小类码是怎么反推出三层上级的国民经济行业分类的四级结构落到数据上就是四个级别的编码门类用英文大写字母 A 到 T大类是两位数字01 到 97中类是三位数字前两位恒等于所属大类小类是四位数字前三位恒等于所属中类。举一个最常见的例子小类0111是稻谷种植它的中类011是谷物种植大类01是农业门类是 A 农林牧渔业。也就是说任意一个小类码反推三层上级不需要查任何关联表直接按字符串截断就能得到。这个特性是第 4 章所有查询技巧的根基。编码还有两个习惯需要知道。第一带“其他”性质的类目通常以 9 结尾比如0119其他谷物种植第二部分版本在注释里会出现带星号或特殊标记的中类条目导入时不要当脏数据过滤掉。这类条目在官方注释里承担“包含在上述类目中的其余部分”的语义过滤掉会破坏上级类目的完整性。处理这种文件时我会把注释单独存一列不参与业务主键但保留下来因为版本修订时注释是判断类目范围变化的第一手信息。2.2 2002、2011、2017 三版数据差在哪不只是类目数量三版的门类数量都是 20 个字母也都在 A 到 T 之间但字母对应的行业发生过明显漂移。最典型的是 F2002 版里 F 是交通运输、仓储和邮政业到了 2011 版和 2017 版F 变成了批发和零售业。也就是说单独看到 F 这个字母脱离版本号没有任何意义。凡是在业务表里存了“门类字母 数字”这种完整代码的跨版本统计前必须先确认口径否则对比结果会整体错位。类目数量上的变化也值得注意。三个版本的大类、中类、小类数量整体呈增加趋势其中小类数量涨幅最大2017 版的小类数已经超过 1300新增部分集中在信息技术服务、商务服务、社会工作这些新兴领域。这个趋势意味着跨版本做归集时旧数据大概率会落到“找不到对应新码”的境地不是数据错了是标准真的给行业补了新格子。另外2011 版之后该标准还有一份针对部分类目的修改单它不在这份 RAR 里做跨版本迁移的人要留意业务口径有没有把修改单算进去。2.3 存储模型怎么选一张表带版本号还是三张表分开建数据清洗之前先定存储模型常见做法有三种。方案 A 是每个版本建一张表比如industry_2002、industry_2011、industry_2017好处是单版本查询条件干净缺点是跨版本对比时要写三份几乎一样的 SQL。方案 B 是一张表加version_year字段用版本号区分数据行。方案 C 只存最新版适合只做当期统计、不需要追溯历史口径的系统。存储方案适用场景主要代价三张表分版本业务只查单版本跨版本对比 SQL 重复单表多版本需要版本对照、追溯所有查询必须带版本条件单表只存最新当期统计不追溯无法支持历史口径我一般选方案 B。理由是后续所有跨版本对比都离不开它比如扫描 2011 到 2017 哪些类目被删除、更名、拆分一条 SQL 就能把两个版本 JOIN 起来三张表就得为每个对比场景单独写语句。整张表全版本加起来也就几千行完全不存在性能压力“表大了要分表”的顾虑在这里不成立。真正要控制的是约束版本号加上完整编码必须唯一层级字段和编码长度要能被程序校验。约束建好了脏数据在入口就会被拦住。3. 建表与导入把 RAR 里的数据落成 MySQL 可查询的结构存储模型定了接下来就是建表和导入。这个环节最容易翻车的是字符集和编码类型数据没进库之前先把表和约束设计好能省掉后面大量返工。这一章按我实际处理的顺序来先建表再导入最后跑自检。3.1 表结构设计code 为什么用 VARCHAR索引怎么建先说两个容易踩的设计决策。第一code字段必须用 VARCHAR不能用 INT。大类和中类编码有前导零比如01存成 INT 会变成1关联和排序全部乱掉门类本身就是字母更没法用数字类型。第二建议冗余一个full_code字段把“门类字母 数字编码”拼好存进去例如A01。业务侧登记行业代码时习惯带门类字母有了这一列和小类码做等值匹配时直接走联合索引不用每次现拼字符串。CREATE TABLE industry_code ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version_year SMALLINT NOT NULL COMMENT 标准版本2002/2011/2017, level TINYINT NOT NULL COMMENT 级别1门类 2大类 3中类 4小类, code VARCHAR(10) NOT NULL COMMENT 本级编码门类为字母其余为数字, full_code VARCHAR(10) NOT NULL COMMENT 带门类字母的完整编码如 A01, name VARCHAR(100) NOT NULL COMMENT 行业名称, comment_text VARCHAR(500) DEFAULT NULL COMMENT 官方注释或说明, parent_code VARCHAR(10) DEFAULT NULL COMMENT 上级编码导入后按规则回填, PRIMARY KEY (id), UNIQUE KEY uk_version_fullcode (version_year, full_code), KEY idx_version_level (version_year, level), KEY idx_parent (version_year, parent_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT国民经济行业分类四级数据;这段 DDL 里有三个设计点值得说明。UNIQUE KEY uk_version_fullcode确保同一版本里不会出现重复编码这是数据入口最关键的约束idx_version_level支撑按版本按级别过滤第 4 章的统计查询基本都走它parent_code允许为空先不手工填导入后统一回填。为什么这么设计门类本身没有上级它的 parent_code 本来就是空其余级别的上级都可以通过编码字符串推导手工维护必然出错。这个坑在第 5 章会单独展开。3.2 解压、转码、LOAD DATA一条完整导入路径解压后常见的是按版本组织的目录或三组 CSV每组里通常带着分类表和说明文件。处理的第一步是先head -5看一眼列结构确认列顺序再决定 LOAD DATA 的字段列表。这里最经典的翻车现场是乱码直接的 Excel 另存为 CSV 时一般会给 UTF-8但如果拿到的 CSV 内容在编辑器里是一片乱码大概率是 GBK 编码命令行转码是最稳的iconv -f GBK -t UTF-8 industry_2017.csv industry_2017_utf8.csv注意iconv遇到无法映射的字符会报错退出加-c可以跳过非法字符但我不建议直接加。跳过的字符可能是类目名称里的特殊符号宁可让它停下根据报错位置回去查原始文件。编码处理完用mysql客户端带--local-infile参数进入再执行 LOAD DATALOAD DATA LOCAL INFILE /tmp/industry_2017_utf8.csv INTO TABLE industry_code CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (version_year, level, code, full_code, name, comment_text);IGNORE 1 LINES跳过表头字段列表必须和 CSV 的实际列顺序一致缺列就让 parent_code 走默认值。如果 CSV 里只有 code 和 name 没有 full_code就先导入这两个字段导入后补 full_code 和 parent_code。补 full_code 的常见做法是门类直接复制 code大类及以下用“门类字母 code”拼接前提是导入数据里每一行都能找到所属门类。拿到手的数据如果本身结构完整这一步通常几分钟就能跑完。导入完成后回填 parent_code这是整张表后续做树查询的前提UPDATE industry_code SET parent_code NULL; UPDATE industry_code SET parent_code CASE level WHEN 2 THEN LEFT(full_code, 1) WHEN 3 THEN LEFT(code, 2) WHEN 4 THEN LEFT(code, 3) ELSE NULL END WHERE level 2;这段回填规则的逻辑是大类的上级是门类直接取 full_code 的第一个字母中类的上级是大类取三位码的前两位小类的上级是中类取四位码的前三位。整套规则只对标准编码成立所以导入前必须确认 code 是纯数字、长度分别是 2、3、4。只要有数据行带字母或长度异常回填出来的 parent_code 一定对不上后续查询就会挂出孤儿节点。注意MySQL 8.0 使用 LOAD DATA LOCAL 时服务端需要开启 local_infile 参数否则即使客户端加了--local-infile1也会被拒绝先SHOW VARIABLES LIKE local_infile;确认一下。3.3 导入后的自检 SQL行数、断档和父子关系一次查清导入完不要急着写业务查询先跑一组自检 SQL。第一件事是看每个版本每个级别的行数是否合理SELECT version_year, level, COUNT(*) AS cnt FROM industry_code GROUP BY version_year, level ORDER BY version_year, level;对照标准公开的类目数量多出来的行基本就是重复表头或者注释条目一眼能看出来。第二件事是查重复编码和编码长度异常SELECT version_year, level, code, COUNT(*) FROM industry_code GROUP BY version_year, level, code HAVING COUNT(*) 1; SELECT version_year, level, code FROM industry_code WHERE (level 2 AND CHAR_LENGTH(code) 2) OR (level 3 AND CHAR_LENGTH(code) 3) OR (level 4 AND CHAR_LENGTH(code) 4);第一段查重复第二段查长度。正常情况下这两条都返回空集。第三件事是检查孤儿节点这是树结构最容易出错的地方SELECT child.version_year, child.level, child.code, child.name FROM industry_code child LEFT JOIN industry_code parent ON child.version_year parent.version_year AND child.parent_code parent.code WHERE child.level 1 AND parent.code IS NULL;门类的 parent_code 为空是正常的所以查询里用child.level 1把它排除掉。如果大类、中类、小类查出孤儿说明回填逻辑或者 CSV 里的编码本身有出入这时候先别继续往下做业务把来源行的原始编码打印出来核对。自检通过后我一般会再抽三个类目分别反推上级路径和官方原文比对一下双校验能防住上面绝大多数问题。4. 四级分类查询常用下钻、统计与代码校验怎么写数据进库只是开始真正高频的是三类查询根据一个四位小类码反查完整路径、按门类或大类做行业分布统计、校验业务表里的行业代码是否合规。这三个场景的写法和适用版本各有差别这一章分别给出可复用的 SQL。4.1 三个必碰场景路径反查、门类统计、业务表校验场景一最容易理解业务表里存了企业登记的行业小类码0111页面要展示“A 农林牧渔业 01 农业 011 谷物种植 0111 稻谷种植”这样一长串路径。场景二是统计口径想数一下制造业门类下各中类分别覆盖了多少企业需要先按小类归集到中类再做分组计数。场景三是字典校验业务表里存的是A01这种带门类字母的完整代码要判断它是不是 2017 版合法编码。查询目标推荐写法适用版本反查上级路径编码截断MySQL 5.7 与 8.0下钻统计LIKE levelMySQL 5.7 与 8.0通用树遍历WITH RECURSIVEMySQL 8.0这三个场景里前两个本质是“固定四层树的上下钻”第三个是字典校验用 JOIN 业务表加存在性判断就能完成。下面重点说树遍历因为这是新手最容易写出慢查询的地方。4.2 用 WITH RECURSIVE 做通用下钻MySQL 8.0如果业务里层级不固定或者未来可能升级到更多层级用 MySQL 8.0 的递归 CTE 是最通用的写法。下面这段查的是 2017 版 A 门类下所有小类的完整路径WITH RECURSIVE tree AS ( SELECT id, version_year, level, code, name, parent_code, full_code, CAST(name AS CHAR(500)) AS path, 0 AS depth FROM industry_code WHERE version_year 2017 AND level 1 AND code A UNION ALL SELECT child.id, child.version_year, child.level, child.code, child.name, child.parent_code, child.full_code, CONCAT(parent.path, , child.name), parent.depth 1 FROM industry_code child JOIN tree parent ON child.version_year parent.version_year AND child.parent_code parent.code ) SELECT code, name, depth, path FROM tree WHERE depth 3 LIMIT 20;这段 SQL 的关键点有三个。起始集合是 2017 版的 A 门类UNION ALL递归把所有下级挂进来CAST(name AS CHAR(500))必须写否则递归字段会按 name 的 VARCHAR(100) 截断路径超过 100 字符就丢内容每次递归都带version_year条件防止 2011 版的 A 门类子节点串进 2017 版的结果里。depth从 0 开始计门类是 0大类是 1中类是 2小类是 3所以查小类用WHERE depth 3。如果只想查某个大类底下的树把起始条件换成level 2 AND code 01depth 就从大类开始起算。递归写法灵活但有一个副作用每次都从根节点开始向下扫描对这张几千行的表无所谓如果以后挂了上百万条业务关联数据递归开销会明显变大。业务场景固定四级时我更推荐下面这种编码截断方案。4.3 兼容 MySQL 5.7 的编码前缀写法四级固定、编码自带层级这意味着完全可以用截断代替递归。给定小类码0111向上反查中类和大类SELECT * FROM industry_code WHERE version_year 2017 AND level 3 AND code LEFT(0111, 3) UNION ALL SELECT * FROM industry_code WHERE version_year 2017 AND level 2 AND code LEFT(0111, 2);LEFT(0111, 3)得到011正好是中类编码LEFT(0111, 2)得到01正好是大类编码。这个写法不需要 parent_code也不需要递归在 MySQL 5.7 甚至 5.6 上都能跑。向下钻取也一样给一个大类码01查它下面所有中类和小类SELECT code, name, level FROM industry_code WHERE version_year 2017 AND code LIKE 01% AND level IN (3, 4) ORDER BY code;注意LIKE 01%会把 level2 的01自己也匹配到所以必须加level IN (3, 4)把大类本身排除。这套方案在业务固定四级时比递归 CTE 直观也是我日常优先用的写法。它的前提是编码规则严格成立一旦出现位数异常或者带星号的特殊条目就会漏数但这些情况在 3.3 的自检 SQL 里已经拦住了。递归方案留给层级不可控的通用场景两者各有边界。5. 避坑清单五条能让你白干一晚上的真实坑这一章列五条我实际踩过的坑全部属于“看着数据没问题、跑起来全错”的类型。每条按现象、原因、解决三步说清前两条甚至不写代码很难发现。5.1 导入后中文全是乱码第一反应别去改字段现象LOAD DATA 执行成功SELECT出来中文全变成“锟斤拷”或者一堆问号第一反应是表字符集建错了。原因RAR 里解压出来的 CSV 是 GBK 编码而 LOAD DATA 默认按客户端字符集解析客户端又是 utf8mb4字符映射自然错位。这不是表结构问题改任何字段类型都解决不了。解决先转码再导入iconv -f GBK -t UTF-8转完确认内容正常执行 LOAD DATA 时指定CHARACTER SET utf8mb4。如果线上环境不允许额外转码这一步也可以在 LOAD DATA 语句里写CHARACTER SET gbk让 MySQL 在导入时做一次转换。两条路都能走但转码后文件落盘后续排查有据可查我一般选前者。5.2 小类编码断档不是数据缺失是标准预留了位置现象校验发现小类码0114后面直接跳到0119以为是漏导了两行数据翻原始文件核对发现官方就是这个编码。原因国标编码本来就不连续。标准修订时新增类目要尽量不动既有编码只能在空位插入所以断档是设计使然不是数据问题。如果把“编码连续”当成校验规则会误报一堆假异常。解决断档校验只做两类检查同一版本同一 level 内有没有重复编码以及编码长度是否合规。不要拿“相邻编码差值为 1”当预期。真正需要警惕的是重复和长度异常这两个才是结构性问题。另外业务表里关联行业代码时永远用 code 字段关联不要用自增 ID 或行号当业务编码。5.3 门类字母在版本间“漂移”不带版本号查询必然错位现象某次统计里 F 门类下企业数量暴增核对发现把 2002 版和 2017 版的数据混在同一个统计口径里了。2002 版的 F 是交通运输、仓储和邮政业2011 版和 2017 版的 F 变成了批发和零售业。原因2002 版到 2011 版之间门类字母做过整体换位同一个字母在不同版本里代表完全不同的行业大类。这不是脏数据是版本演化的正常结果。解决所有查询强制带version_year条件这是单表多版本存储模型的使用底线。跨版本做对比时不要用门类字母做关联键要用四位小类码加名称联合判断。如果业务系统里上游传过来的行业代码只有字母加数字这种完整码先在入口处解析出版本号再进查询逻辑否则后面所有统计都不可信。5.4 同一个四位码跨版本可能不再是同一个行业现象做 2011 到 2017 的迁移映射时发现某个四位码在两个版本里名称一模一样程序自动判断为“未变化”结果业务侧反馈这个行业归属的大类变了历史数据归到了错误的门类下。原因标准修订时部分类目被合并或拆分后复用了原有码位甚至出现“名字没变、上级变了”的情况。只比 code 和 name 两个字段看不到这层变化。解决跨版本对比时至少同时比对 code、name、parent_code 三项。凡是名称相同但上级不同的一律标记为“需人工确认”不要自动沿用旧映射。在映射表里加一个 change_type 字段专门记录这种情况后续审计也有据可查。5.5 parent_code 手工维护必翻车不如用编码规则回填现象同事实在 Excel 里手工给几百行数据填 parent_code填到一半换了人结果树查询挂出一批孤儿节点中类挂到了错误的大类下面。原因人工填写无法保证规则一致性而且标准里存在大量名称相近的“其他”类目肉眼极易看错。凡是能由规则推导的字段都不该让人手参与。解决parent_code 不由人工维护。导入时先置 NULL导入后按第 3.2 节的 CASE 规则统一回填。如果数据来源 CSV 里本身有 parent_code 列导入后也要重新执行一次回填 SQL 覆盖掉保证数据源只有一个规则出口。另一个更省事的方案是查询端完全不依赖 parent_code直接用LEFT(code, 长度-1)做层级截断这样连回填步骤都可以省略。6. 进阶用 SQL 做版本差异扫描给迁移铺好底版本切换时最怕的是不知道新版标准动了哪些类目。与其拿着两本分类目录人工比对不如直接用单表多版本这层结构跑差异扫描。先看新增和删除SELECT 新增 AS change_type, code, name FROM industry_code WHERE version_year 2017 AND level 4 AND code NOT IN (SELECT code FROM industry_code WHERE version_year 2011 AND level 4) UNION ALL SELECT 删除, code, name FROM industry_code WHERE version_year 2011 AND level 4 AND code NOT IN (SELECT code FROM industry_code WHERE version_year 2017 AND level 4);再看同样的小类码是否改了名称SELECT a.code, a.name AS name_2011, b.name AS name_2017 FROM industry_code a JOIN industry_code b ON a.version_year 2011 AND b.version_year 2017 AND a.code b.code AND a.level 4 AND b.level 4 WHERE a.name b.name;把这三段结果落到一张映射表里结构上我一般这样设计old_code、new_code、old_name、new_name、mapping_type完全对应、仅更名、一对多、多对一、需人工确认再加处理时间和备注。一对多和多对一坚决不做程序自动合并按 old_code 关联会重复计数按 new_code 反向关联会漏数据这两种只能人工确认。完全对应不上的记录落到新版“其他”类目兜底但映射表里必须留下审计痕迹。我现在的习惯是每次标准切换先把差异扫描 SQL 跑一遍导出结果让业务确认再动映射表这套流程已经在两次版本更新里帮我少走了很多弯路。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/9 21:15:06 · 阅读 1.2k 行业分类代码入MySQL：建表、导入、查询与版本迁移实践
简介基于GB/T 4754-2002、2011、2017三版国民经济行业分类标准本数据包提供可直接导入MySQL的行业分类代码表面向需要做行业维度数据清洗与标准化的大数据技术人员。每个表均按门类、大类、中类、小类四级拆分以“A0111”与“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”的形式一一对应并附有2002与2011年的历史代码对照方便跨版本追溯与映射。压缩包内共3个sql文件分别对应2002、2011、2017三个年份文件总大小仅44KB结构简洁导入后即可用于行业字段匹配、归类或统计分析。目前已有3038人学习下载适合需要快速获得标准行业分类数据、减少手工整理成本的数据分析或数据仓库建设人员。1. 这个 RAR 里到底装了什么三版国标分类代码值不值得拿来建库拿到这份2002_2011_2017国民经济行业分类与代码mysql数据四级分类文件.rar先别急着解压去找 DBA 导数据。它装的是三个版本2002、2011、2017的国民经济行业分类四级数据门类、大类、中类、小类每一级都有独立编码版本之间还存在字母漂移和类目拆分。做过企业库、统计报表或行业归集模块的开发者大概率体会过“手里有张表、用起来全是场外规则”的无力感。这套数据的价值恰恰在于把年代口径和层级结构结构化省掉人工查表对码。下面这套流程是我处理同类数据时反复在用的编码规则、建表导入、查询下钻、版本对比和踩坑点读写两端都能直接抄。2. 动手建库之前先把四级编码规则和三版差异看懂四级分类的坑大多数出在“以为看懂了编码”就急着写 SQL。这一章先把编码规则和版本差异讲透再决定存储模型。顺序反了后面所有查询都可能跑在错误假设上。2.1 四级编码规则一个小类码是怎么反推出三层上级的国民经济行业分类的四级结构落到数据上就是四个级别的编码门类用英文大写字母 A 到 T大类是两位数字01 到 97中类是三位数字前两位恒等于所属大类小类是四位数字前三位恒等于所属中类。举一个最常见的例子小类0111是稻谷种植它的中类011是谷物种植大类01是农业门类是 A 农林牧渔业。也就是说任意一个小类码反推三层上级不需要查任何关联表直接按字符串截断就能得到。这个特性是第 4 章所有查询技巧的根基。编码还有两个习惯需要知道。第一带“其他”性质的类目通常以 9 结尾比如0119其他谷物种植第二部分版本在注释里会出现带星号或特殊标记的中类条目导入时不要当脏数据过滤掉。这类条目在官方注释里承担“包含在上述类目中的其余部分”的语义过滤掉会破坏上级类目的完整性。处理这种文件时我会把注释单独存一列不参与业务主键但保留下来因为版本修订时注释是判断类目范围变化的第一手信息。2.2 2002、2011、2017 三版数据差在哪不只是类目数量三版的门类数量都是 20 个字母也都在 A 到 T 之间但字母对应的行业发生过明显漂移。最典型的是 F2002 版里 F 是交通运输、仓储和邮政业到了 2011 版和 2017 版F 变成了批发和零售业。也就是说单独看到 F 这个字母脱离版本号没有任何意义。凡是在业务表里存了“门类字母 数字”这种完整代码的跨版本统计前必须先确认口径否则对比结果会整体错位。类目数量上的变化也值得注意。三个版本的大类、中类、小类数量整体呈增加趋势其中小类数量涨幅最大2017 版的小类数已经超过 1300新增部分集中在信息技术服务、商务服务、社会工作这些新兴领域。这个趋势意味着跨版本做归集时旧数据大概率会落到“找不到对应新码”的境地不是数据错了是标准真的给行业补了新格子。另外2011 版之后该标准还有一份针对部分类目的修改单它不在这份 RAR 里做跨版本迁移的人要留意业务口径有没有把修改单算进去。2.3 存储模型怎么选一张表带版本号还是三张表分开建数据清洗之前先定存储模型常见做法有三种。方案 A 是每个版本建一张表比如industry_2002、industry_2011、industry_2017好处是单版本查询条件干净缺点是跨版本对比时要写三份几乎一样的 SQL。方案 B 是一张表加version_year字段用版本号区分数据行。方案 C 只存最新版适合只做当期统计、不需要追溯历史口径的系统。存储方案适用场景主要代价三张表分版本业务只查单版本跨版本对比 SQL 重复单表多版本需要版本对照、追溯所有查询必须带版本条件单表只存最新当期统计不追溯无法支持历史口径我一般选方案 B。理由是后续所有跨版本对比都离不开它比如扫描 2011 到 2017 哪些类目被删除、更名、拆分一条 SQL 就能把两个版本 JOIN 起来三张表就得为每个对比场景单独写语句。整张表全版本加起来也就几千行完全不存在性能压力“表大了要分表”的顾虑在这里不成立。真正要控制的是约束版本号加上完整编码必须唯一层级字段和编码长度要能被程序校验。约束建好了脏数据在入口就会被拦住。3. 建表与导入把 RAR 里的数据落成 MySQL 可查询的结构存储模型定了接下来就是建表和导入。这个环节最容易翻车的是字符集和编码类型数据没进库之前先把表和约束设计好能省掉后面大量返工。这一章按我实际处理的顺序来先建表再导入最后跑自检。3.1 表结构设计code 为什么用 VARCHAR索引怎么建先说两个容易踩的设计决策。第一code字段必须用 VARCHAR不能用 INT。大类和中类编码有前导零比如01存成 INT 会变成1关联和排序全部乱掉门类本身就是字母更没法用数字类型。第二建议冗余一个full_code字段把“门类字母 数字编码”拼好存进去例如A01。业务侧登记行业代码时习惯带门类字母有了这一列和小类码做等值匹配时直接走联合索引不用每次现拼字符串。CREATE TABLE industry_code ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version_year SMALLINT NOT NULL COMMENT 标准版本2002/2011/2017, level TINYINT NOT NULL COMMENT 级别1门类 2大类 3中类 4小类, code VARCHAR(10) NOT NULL COMMENT 本级编码门类为字母其余为数字, full_code VARCHAR(10) NOT NULL COMMENT 带门类字母的完整编码如 A01, name VARCHAR(100) NOT NULL COMMENT 行业名称, comment_text VARCHAR(500) DEFAULT NULL COMMENT 官方注释或说明, parent_code VARCHAR(10) DEFAULT NULL COMMENT 上级编码导入后按规则回填, PRIMARY KEY (id), UNIQUE KEY uk_version_fullcode (version_year, full_code), KEY idx_version_level (version_year, level), KEY idx_parent (version_year, parent_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT国民经济行业分类四级数据;这段 DDL 里有三个设计点值得说明。UNIQUE KEY uk_version_fullcode确保同一版本里不会出现重复编码这是数据入口最关键的约束idx_version_level支撑按版本按级别过滤第 4 章的统计查询基本都走它parent_code允许为空先不手工填导入后统一回填。为什么这么设计门类本身没有上级它的 parent_code 本来就是空其余级别的上级都可以通过编码字符串推导手工维护必然出错。这个坑在第 5 章会单独展开。3.2 解压、转码、LOAD DATA一条完整导入路径解压后常见的是按版本组织的目录或三组 CSV每组里通常带着分类表和说明文件。处理的第一步是先head -5看一眼列结构确认列顺序再决定 LOAD DATA 的字段列表。这里最经典的翻车现场是乱码直接的 Excel 另存为 CSV 时一般会给 UTF-8但如果拿到的 CSV 内容在编辑器里是一片乱码大概率是 GBK 编码命令行转码是最稳的iconv -f GBK -t UTF-8 industry_2017.csv industry_2017_utf8.csv注意iconv遇到无法映射的字符会报错退出加-c可以跳过非法字符但我不建议直接加。跳过的字符可能是类目名称里的特殊符号宁可让它停下根据报错位置回去查原始文件。编码处理完用mysql客户端带--local-infile参数进入再执行 LOAD DATALOAD DATA LOCAL INFILE /tmp/industry_2017_utf8.csv INTO TABLE industry_code CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (version_year, level, code, full_code, name, comment_text);IGNORE 1 LINES跳过表头字段列表必须和 CSV 的实际列顺序一致缺列就让 parent_code 走默认值。如果 CSV 里只有 code 和 name 没有 full_code就先导入这两个字段导入后补 full_code 和 parent_code。补 full_code 的常见做法是门类直接复制 code大类及以下用“门类字母 code”拼接前提是导入数据里每一行都能找到所属门类。拿到手的数据如果本身结构完整这一步通常几分钟就能跑完。导入完成后回填 parent_code这是整张表后续做树查询的前提UPDATE industry_code SET parent_code NULL; UPDATE industry_code SET parent_code CASE level WHEN 2 THEN LEFT(full_code, 1) WHEN 3 THEN LEFT(code, 2) WHEN 4 THEN LEFT(code, 3) ELSE NULL END WHERE level 2;这段回填规则的逻辑是大类的上级是门类直接取 full_code 的第一个字母中类的上级是大类取三位码的前两位小类的上级是中类取四位码的前三位。整套规则只对标准编码成立所以导入前必须确认 code 是纯数字、长度分别是 2、3、4。只要有数据行带字母或长度异常回填出来的 parent_code 一定对不上后续查询就会挂出孤儿节点。注意MySQL 8.0 使用 LOAD DATA LOCAL 时服务端需要开启 local_infile 参数否则即使客户端加了--local-infile1也会被拒绝先SHOW VARIABLES LIKE local_infile;确认一下。3.3 导入后的自检 SQL行数、断档和父子关系一次查清导入完不要急着写业务查询先跑一组自检 SQL。第一件事是看每个版本每个级别的行数是否合理SELECT version_year, level, COUNT(*) AS cnt FROM industry_code GROUP BY version_year, level ORDER BY version_year, level;对照标准公开的类目数量多出来的行基本就是重复表头或者注释条目一眼能看出来。第二件事是查重复编码和编码长度异常SELECT version_year, level, code, COUNT(*) FROM industry_code GROUP BY version_year, level, code HAVING COUNT(*) 1; SELECT version_year, level, code FROM industry_code WHERE (level 2 AND CHAR_LENGTH(code) 2) OR (level 3 AND CHAR_LENGTH(code) 3) OR (level 4 AND CHAR_LENGTH(code) 4);第一段查重复第二段查长度。正常情况下这两条都返回空集。第三件事是检查孤儿节点这是树结构最容易出错的地方SELECT child.version_year, child.level, child.code, child.name FROM industry_code child LEFT JOIN industry_code parent ON child.version_year parent.version_year AND child.parent_code parent.code WHERE child.level 1 AND parent.code IS NULL;门类的 parent_code 为空是正常的所以查询里用child.level 1把它排除掉。如果大类、中类、小类查出孤儿说明回填逻辑或者 CSV 里的编码本身有出入这时候先别继续往下做业务把来源行的原始编码打印出来核对。自检通过后我一般会再抽三个类目分别反推上级路径和官方原文比对一下双校验能防住上面绝大多数问题。4. 四级分类查询常用下钻、统计与代码校验怎么写数据进库只是开始真正高频的是三类查询根据一个四位小类码反查完整路径、按门类或大类做行业分布统计、校验业务表里的行业代码是否合规。这三个场景的写法和适用版本各有差别这一章分别给出可复用的 SQL。4.1 三个必碰场景路径反查、门类统计、业务表校验场景一最容易理解业务表里存了企业登记的行业小类码0111页面要展示“A 农林牧渔业 01 农业 011 谷物种植 0111 稻谷种植”这样一长串路径。场景二是统计口径想数一下制造业门类下各中类分别覆盖了多少企业需要先按小类归集到中类再做分组计数。场景三是字典校验业务表里存的是A01这种带门类字母的完整代码要判断它是不是 2017 版合法编码。查询目标推荐写法适用版本反查上级路径编码截断MySQL 5.7 与 8.0下钻统计LIKE levelMySQL 5.7 与 8.0通用树遍历WITH RECURSIVEMySQL 8.0这三个场景里前两个本质是“固定四层树的上下钻”第三个是字典校验用 JOIN 业务表加存在性判断就能完成。下面重点说树遍历因为这是新手最容易写出慢查询的地方。4.2 用 WITH RECURSIVE 做通用下钻MySQL 8.0如果业务里层级不固定或者未来可能升级到更多层级用 MySQL 8.0 的递归 CTE 是最通用的写法。下面这段查的是 2017 版 A 门类下所有小类的完整路径WITH RECURSIVE tree AS ( SELECT id, version_year, level, code, name, parent_code, full_code, CAST(name AS CHAR(500)) AS path, 0 AS depth FROM industry_code WHERE version_year 2017 AND level 1 AND code A UNION ALL SELECT child.id, child.version_year, child.level, child.code, child.name, child.parent_code, child.full_code, CONCAT(parent.path, , child.name), parent.depth 1 FROM industry_code child JOIN tree parent ON child.version_year parent.version_year AND child.parent_code parent.code ) SELECT code, name, depth, path FROM tree WHERE depth 3 LIMIT 20;这段 SQL 的关键点有三个。起始集合是 2017 版的 A 门类UNION ALL递归把所有下级挂进来CAST(name AS CHAR(500))必须写否则递归字段会按 name 的 VARCHAR(100) 截断路径超过 100 字符就丢内容每次递归都带version_year条件防止 2011 版的 A 门类子节点串进 2017 版的结果里。depth从 0 开始计门类是 0大类是 1中类是 2小类是 3所以查小类用WHERE depth 3。如果只想查某个大类底下的树把起始条件换成level 2 AND code 01depth 就从大类开始起算。递归写法灵活但有一个副作用每次都从根节点开始向下扫描对这张几千行的表无所谓如果以后挂了上百万条业务关联数据递归开销会明显变大。业务场景固定四级时我更推荐下面这种编码截断方案。4.3 兼容 MySQL 5.7 的编码前缀写法四级固定、编码自带层级这意味着完全可以用截断代替递归。给定小类码0111向上反查中类和大类SELECT * FROM industry_code WHERE version_year 2017 AND level 3 AND code LEFT(0111, 3) UNION ALL SELECT * FROM industry_code WHERE version_year 2017 AND level 2 AND code LEFT(0111, 2);LEFT(0111, 3)得到011正好是中类编码LEFT(0111, 2)得到01正好是大类编码。这个写法不需要 parent_code也不需要递归在 MySQL 5.7 甚至 5.6 上都能跑。向下钻取也一样给一个大类码01查它下面所有中类和小类SELECT code, name, level FROM industry_code WHERE version_year 2017 AND code LIKE 01% AND level IN (3, 4) ORDER BY code;注意LIKE 01%会把 level2 的01自己也匹配到所以必须加level IN (3, 4)把大类本身排除。这套方案在业务固定四级时比递归 CTE 直观也是我日常优先用的写法。它的前提是编码规则严格成立一旦出现位数异常或者带星号的特殊条目就会漏数但这些情况在 3.3 的自检 SQL 里已经拦住了。递归方案留给层级不可控的通用场景两者各有边界。5. 避坑清单五条能让你白干一晚上的真实坑这一章列五条我实际踩过的坑全部属于“看着数据没问题、跑起来全错”的类型。每条按现象、原因、解决三步说清前两条甚至不写代码很难发现。5.1 导入后中文全是乱码第一反应别去改字段现象LOAD DATA 执行成功SELECT出来中文全变成“锟斤拷”或者一堆问号第一反应是表字符集建错了。原因RAR 里解压出来的 CSV 是 GBK 编码而 LOAD DATA 默认按客户端字符集解析客户端又是 utf8mb4字符映射自然错位。这不是表结构问题改任何字段类型都解决不了。解决先转码再导入iconv -f GBK -t UTF-8转完确认内容正常执行 LOAD DATA 时指定CHARACTER SET utf8mb4。如果线上环境不允许额外转码这一步也可以在 LOAD DATA 语句里写CHARACTER SET gbk让 MySQL 在导入时做一次转换。两条路都能走但转码后文件落盘后续排查有据可查我一般选前者。5.2 小类编码断档不是数据缺失是标准预留了位置现象校验发现小类码0114后面直接跳到0119以为是漏导了两行数据翻原始文件核对发现官方就是这个编码。原因国标编码本来就不连续。标准修订时新增类目要尽量不动既有编码只能在空位插入所以断档是设计使然不是数据问题。如果把“编码连续”当成校验规则会误报一堆假异常。解决断档校验只做两类检查同一版本同一 level 内有没有重复编码以及编码长度是否合规。不要拿“相邻编码差值为 1”当预期。真正需要警惕的是重复和长度异常这两个才是结构性问题。另外业务表里关联行业代码时永远用 code 字段关联不要用自增 ID 或行号当业务编码。5.3 门类字母在版本间“漂移”不带版本号查询必然错位现象某次统计里 F 门类下企业数量暴增核对发现把 2002 版和 2017 版的数据混在同一个统计口径里了。2002 版的 F 是交通运输、仓储和邮政业2011 版和 2017 版的 F 变成了批发和零售业。原因2002 版到 2011 版之间门类字母做过整体换位同一个字母在不同版本里代表完全不同的行业大类。这不是脏数据是版本演化的正常结果。解决所有查询强制带version_year条件这是单表多版本存储模型的使用底线。跨版本做对比时不要用门类字母做关联键要用四位小类码加名称联合判断。如果业务系统里上游传过来的行业代码只有字母加数字这种完整码先在入口处解析出版本号再进查询逻辑否则后面所有统计都不可信。5.4 同一个四位码跨版本可能不再是同一个行业现象做 2011 到 2017 的迁移映射时发现某个四位码在两个版本里名称一模一样程序自动判断为“未变化”结果业务侧反馈这个行业归属的大类变了历史数据归到了错误的门类下。原因标准修订时部分类目被合并或拆分后复用了原有码位甚至出现“名字没变、上级变了”的情况。只比 code 和 name 两个字段看不到这层变化。解决跨版本对比时至少同时比对 code、name、parent_code 三项。凡是名称相同但上级不同的一律标记为“需人工确认”不要自动沿用旧映射。在映射表里加一个 change_type 字段专门记录这种情况后续审计也有据可查。5.5 parent_code 手工维护必翻车不如用编码规则回填现象同事实在 Excel 里手工给几百行数据填 parent_code填到一半换了人结果树查询挂出一批孤儿节点中类挂到了错误的大类下面。原因人工填写无法保证规则一致性而且标准里存在大量名称相近的“其他”类目肉眼极易看错。凡是能由规则推导的字段都不该让人手参与。解决parent_code 不由人工维护。导入时先置 NULL导入后按第 3.2 节的 CASE 规则统一回填。如果数据来源 CSV 里本身有 parent_code 列导入后也要重新执行一次回填 SQL 覆盖掉保证数据源只有一个规则出口。另一个更省事的方案是查询端完全不依赖 parent_code直接用LEFT(code, 长度-1)做层级截断这样连回填步骤都可以省略。6. 进阶用 SQL 做版本差异扫描给迁移铺好底版本切换时最怕的是不知道新版标准动了哪些类目。与其拿着两本分类目录人工比对不如直接用单表多版本这层结构跑差异扫描。先看新增和删除SELECT 新增 AS change_type, code, name FROM industry_code WHERE version_year 2017 AND level 4 AND code NOT IN (SELECT code FROM industry_code WHERE version_year 2011 AND level 4) UNION ALL SELECT 删除, code, name FROM industry_code WHERE version_year 2011 AND level 4 AND code NOT IN (SELECT code FROM industry_code WHERE version_year 2017 AND level 4);再看同样的小类码是否改了名称SELECT a.code, a.name AS name_2011, b.name AS name_2017 FROM industry_code a JOIN industry_code b ON a.version_year 2011 AND b.version_year 2017 AND a.code b.code AND a.level 4 AND b.level 4 WHERE a.name b.name;把这三段结果落到一张映射表里结构上我一般这样设计old_code、new_code、old_name、new_name、mapping_type完全对应、仅更名、一对多、多对一、需人工确认再加处理时间和备注。一对多和多对一坚决不做程序自动合并按 old_code 关联会重复计数按 new_code 反向关联会漏数据这两种只能人工确认。完全对应不上的记录落到新版“其他”类目兜底但映射表里必须留下审计痕迹。我现在的习惯是每次标准切换先把差异扫描 SQL 跑一遍导出结果让业务确认再动映射表这套流程已经在两次版本更新里帮我少走了很多弯路。希望帮到你。本文还有配套的精品资源点击获取