简介这份资源面向从事大数据清洗与行业维度标准化工作的技术人员提供2002、2011、2017三个年度国民经济行业分类与代码的MySQL数据文件对应GB/T4754-2002、GB/T4754-2011、GB/T4754-2017三版国家标准。每个代码均按“门类·大类·中类·小类”四级结构组织例如“A0111”对应“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”便于直接入库做行业映射与口径对齐。压缩包共3个文件均为sql脚本整体约44KB体量轻便导入即可使用。资源已有3034人学习下载适合需要跨年度行业代码对照、数据标准化或维度建模的开发者参考可省去手工整理分类层级与代码映射的时间。1. 从一份 rar 说起2002/2011/2017 三版国民经济行业分类怎么落进 MySQL手里拿到一个叫2002_2011_2017国民经济行业分类与代码mysql数据四级分类文件.rar的压缩包第一反应往往不是解压而是犯嘀咕三个年份的国标分类为什么有人要打包成一份 MySQL 数据答案藏在业务里。做企业征信、税务开票、统计上报、供应链主数据治理的人都知道行业分类代码是绕不开的字典表而 2002、2011、2017 这三版恰好是近二十年影响最广的三次修订——2011 版把门类从 20 个压到 20 个但大类结构大改2017 版又新增了若干新兴服务业大类。历史数据用老版、新系统用新版做数据对齐时就得把三套码表同时装进库里做映射。这份 rar 的价值就在这它把三个年份的四级分类门类、大类、中类、小类整理成可直接导入 MySQL 的结构化数据省去你从 PDF 或 Excel 里手工扒的功夫。这篇笔记就按「拿到 rar 之后怎么建库、怎么导、怎么查、怎么避坑」的顺序讲清楚适合做数据中台、主数据、报表开发的同行照着复现。2. 先搞懂四级分类的编码结构再决定表怎么建2.1 国标编码的层级规则与长度差异国民经济行业分类采用层次码门类用一位大写字母表示如 A 农林牧渔业、C 制造业大类用两位数字中类用三位数字小类用四位数字。2017 版的完整小类代码形如C3912其中 C 是门类、39 是大类、391 是中类、3912 是小类。2002 和 2011 版结构一致但具体码值有增删改。这里有个容易翻车的点门类是字母其余层级是数字如果建表时把code字段统一设成CHAR(5)没问题但如果设成INT门类那一位字母直接存不进去导入时要么报错要么被截断成 0。我一般会把代码字段定为VARCHAR(10)留出余量同时单独存一个level字段标记层级避免每次查询都靠LENGTH(code)去猜。2.2 三版并存时的表结构设计取舍三版数据放一张表还是三张表这是建库前必须拍板的。放一张表加version字段好处是跨版本映射查询方便坏处是索引选择性下降且三版同名代码含义可能不同容易误关联。分三张表如industry_2002、industry_2011、industry_2017结构清晰但做版本对照时要写 UNION。我的做法是主表按版本分三张另建一张industry_mapping映射表专门存「2011 码 → 2017 码」的对应关系。下面给出建表语句字段设计兼顾四级层级和版本隔离。-- 单版本行业分类表三版各建一张此处以 2017 为例 CREATE TABLE industry_2017 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, code VARCHAR(10) NOT NULL COMMENT 行业代码门类为字母其余为数字, name VARCHAR(120) NOT NULL COMMENT 行业名称, parent_code VARCHAR(10) DEFAULT NULL COMMENT 父级代码门类为 NULL, level TINYINT NOT NULL COMMENT 层级1门类 2大类 3中类 4小类, version SMALLINT NOT NULL DEFAULT 2017 COMMENT 版本年份, is_active TINYINT NOT NULL DEFAULT 1 COMMENT 是否现行有效, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent (parent_code), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2017版国民经济行业分类四级码表;这段建表语句里code用VARCHAR(10)而不是CHAR(5)是因为部分历史版本存在带后缀的临时码parent_code允许 NULL 是为了让门类记录能独立存在level单独冗余存储查询「所有大类」时直接WHERE level2即可比字符串截取快得多。utf8mb4字符集是必须的行业名称里偶尔出现生僻字用utf8三字节会在导入时报Incorrect string value。索引方面uk_code保证同版本内代码唯一idx_parent支撑树形递归查询idx_level支撑按层级筛选。2.3 从 rar 到 SQL解压后先看清文件格式解压 rar 后常见的内容形态有三种.sql转储文件、.csv/.xlsx数据文件、或者按年份分目录的多个文件。先别急着导入用file命令或直接打开看头部几行确认编码是 UTF-8 还是 GBK。国标数据从官方 Excel 转出来经常是 GBK直接LOAD DATA会乱码。如果是.sql文件检查它是否包含CREATE DATABASE和USE语句避免导到错误的库。下面这段命令用于快速探查文件情况。# 查看解压后目录结构 ls -lh ./国民经济行业分类/ # 探查文件编码gb2312 或 utf-8 会直接显示 file -i ./国民经济行业分类/industry_2017.csv # 预览前 5 行确认分隔符和表头 head -n 5 ./国民经济行业分类/industry_2017.csvfile -i输出的charset字段是关键如果是iso-8859-1或gb2312导入前必须用iconv转成 UTF-8否则中文名称全是问号。head看的是分隔符国标导出常用逗号或制表符如果字段里本身含逗号比如「农、林、牧、渔专业及辅助性活动」就得用制表符或引号包裹这直接决定后面LOAD DATA的FIELDS TERMINATED BY怎么写。3. 把三版码表导进 MySQLLOAD DATA 与批量 INSERT 两条路3.1 用 LOAD DATA LOCAL INFILE 快速导入 CSV数据量大时三版合计约 4000 多条小类加上中间层级近万行逐条 INSERT 太慢首选LOAD DATA LOCAL INFILE。前提是 MySQL 客户端和服务端都开启了local_infile。先确认参数再执行导入。-- 确认 local_infile 是否开启ON 才能用 LOCAL 导入 SHOW VARIABLES LIKE local_infile; -- 若为 OFF在会话级临时开启需服务端也允许 SET GLOBAL local_infile 1;服务端开启需要改my.cnf里的local_infile1并重启或者用mysql --local-infile1启动客户端。导入语句如下注意字段顺序要和 CSV 列对齐。LOAD DATA LOCAL INFILE /path/industry_2017.csv INTO TABLE industry_2017 CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (code, name, parent_code, level, version);CHARACTER SET utf8mb4显式声明源文件编码避免继承库默认值导致乱码OPTIONALLY ENCLOSED BY 处理名称里含逗号的情况IGNORE 1 LINES跳过表头。如果导入后level全是 0说明 CSV 里没有这一列需要导入后用UPDATE根据code长度回填门类LENGTH(code)1为 1两位为 2依此类推。这一步别偷懒层级字段错了后面所有树形查询都废。3.2 用 Python 脚本做清洗与批量 INSERT如果 rar 里给的是 Excel 或格式不规整的文本用 Python 做一层清洗再入库更稳。pandas读 Excelpymysql批量插入executemany比循环单条快一个数量级。import pandas as pd import pymysql # 读取 Excel指定 dtype 防止代码列被识别成数字丢失前导零 df pd.read_excel(./industry_2017.xlsx, dtype{code: str, parent_code: str}) df df.fillna() # 空父级填空串入库时转 None conn pymysql.connect(host127.0.0.1, userroot, passwordyourpass, databaseindustry_db, charsetutf8mb4) cursor conn.cursor() sql INSERT INTO industry_2017 (code, name, parent_code, level, version) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE nameVALUES(name), parent_codeVALUES(parent_code) rows [(r.code, r.name, r.parent_code or None, r.level, 2017) for r in df.itertuples()] cursor.executemany(sql, rows) conn.commit() print(f导入 {cursor.rowcount} 行) cursor.close() conn.close()dtype{code: str}是血泪经验行业代码0131这种pandas 默认读成整数 131前导零没了和父级代码对不上。ON DUPLICATE KEY UPDATE让脚本可重复执行重跑不会因唯一键冲突中断。parent_code or None把空串转成 NULL和建表时的 NULL 语义一致。executemany一次提交几千行秒级完成比逐条execute快得多。3.3 导入后的完整性校验导完别急着用先跑几条校验 SQL。检查各层级数量是否符合预期检查孤儿节点父级代码在表中不存在检查代码长度和层级是否匹配。-- 各层级数量分布 SELECT level, COUNT(*) FROM industry_2017 GROUP BY level; -- 孤儿节点父级代码找不到对应记录 SELECT c.code, c.name, c.parent_code FROM industry_2017 c LEFT JOIN industry_2017 p ON c.parent_code p.code WHERE c.parent_code IS NOT NULL AND p.code IS NULL; -- 层级与代码长度不匹配的异常记录 SELECT code, name, level FROM industry_2017 WHERE (level1 AND LENGTH(code)1) OR (level2 AND LENGTH(code)2) OR (level3 AND LENGTH(code)3) OR (level4 AND LENGTH(code)4);第一条看分布2017 版门类 20 个、大类约 97 个、中类约 473 个、小类约 1380 个数量对不上说明导入漏行。第二条查孤儿正常情况应为空有结果说明父级代码写错或缺失。第三条查层级错位是导入时列错位最常见的症状。这三条跑完没问题码表才算真正可用。4. 四级分类的树形查询与跨版本映射怎么写4.1 用自连接查某大类下的全部小类业务里最常见的需求是「给一个门类或大类列出其下所有小类」。四级结构用三次自连接就能拉平比递归 CTE 兼容性好MySQL 5.7 不支持 CTE。-- 查询 C 制造业下所有四级小类 SELECT l4.code AS small_code, l4.name AS small_name, l3.name AS mid_name, l2.name AS big_name FROM industry_2017 l4 JOIN industry_2017 l3 ON l4.parent_code l3.code JOIN industry_2017 l2 ON l3.parent_code l2.code WHERE l2.parent_code C AND l4.level 4 ORDER BY l4.code;l4.parent_code l3.code把中类和小类关联l3.parent_code l2.code把大类和中类关联l2.parent_code C锁定门类。这种写法在parent_code和code都有索引时性能很好万行级表毫秒返回。如果只要某一层直接WHERE level4 AND code LIKE C39%更快但前缀匹配依赖代码规则跨版本时规则可能变自连接更稳。4.2 2011 与 2017 的映射表怎么建和怎么查跨版本映射是这份数据最核心的价值。2017 版修订时部分 2011 小类被拆分、合并或改名映射关系有一对一、一对多、多对一三种。映射表设计要能表达这三种关系。CREATE TABLE industry_mapping ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code_2011 VARCHAR(10) NOT NULL COMMENT 2011版代码, code_2017 VARCHAR(10) NOT NULL COMMENT 2017版代码, map_type TINYINT NOT NULL COMMENT 1一对一 2一对多 3多对一, remark VARCHAR(255) DEFAULT NULL COMMENT 拆分合并说明, PRIMARY KEY (id), KEY idx_2011 (code_2011), KEY idx_2017 (code_2017) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2011到2017行业代码映射;map_type字段让查询方能判断是否需要聚合。查一个 2011 代码对应哪些 2017 代码SELECT m.code_2011, m.code_2017, n.name AS name_2017, m.map_type FROM industry_mapping m JOIN industry_2017 n ON m.code_2017 n.code WHERE m.code_2011 3912;如果返回多行且map_type2说明该行业在 2017 版被拆成多个小类做统计口径转换时要把金额按规则分摊或整体归入某一类这个业务规则得和业务方确认不能拍脑袋。反向查 2017 对应 2011 同理把WHERE条件换成code_2017即可。4.3 用递归 CTE 查任意节点的完整路径MySQL 8.0 支持递归 CTE查某个小类的完整四级路径比自连接灵活不用预先知道层级数。WITH RECURSIVE path AS ( SELECT code, name, parent_code, level, CAST(name AS CHAR(500)) AS full_path FROM industry_2017 WHERE code C3912 UNION ALL SELECT p.code, p.name, p.parent_code, p.level, CONCAT(p.name, , path.full_path) FROM industry_2017 p JOIN path ON p.code path.parent_code ) SELECT full_path FROM path WHERE parent_code IS NULL;CAST(name AS CHAR(500))是必须的递归 CTE 里字符串拼接会不断增长不显式声明长度会报Data too long。UNION ALL从叶子往上找父级最后parent_code IS NULL的那行就是门类full_path即完整路径。这个查询在给报表加「行业全称」列时特别有用一次查出「制造业 金属制品业 结构性金属制品制造 金属门窗制造」这样的路径省去应用层多次查询。5. 导入和使用中最容易翻车的五个坑5.1 中文乱码现象是名称显示问号原因是字符集链路不一致现象导入后name字段全是???或乱码方块。原因CSV 文件是 GBK而表是 utf8mb4LOAD DATA没指定源字符集MySQL 按默认字符集解析。解决导入前用iconv -f GBK -t UTF-8 source.csv target.csv转码或在LOAD DATA里加CHARACTER SET gbk。建库建表统一用utf8mb4连接串也指定charsetutf8mb4整条链路一致才不会翻车。5.2 前导零丢失现象是代码 0131 变成 131原因是字段类型或读取方式把代码当数字现象导入后两位、三位代码的前导零没了和父级代码对不上树形结构断裂。原因CSV 被 Excel 打开过并保存或 Python 读取时未指定dtypestr或建表时code用了INT。解决建表用VARCHARPython 读取显式dtype{code: str}CSV 导入时确认源文件未被 Excel 二次保存。已经丢零的用LPAD(code, 期望长度, 0)回补但前提是知道每行该有的长度。5.3 孤儿节点现象是树形查询查不到子级原因是父级代码缺失或版本混用现象查某大类下的小类返回空或完整性校验报出大量孤儿。原因三版数据混在一张表里2011 的父级代码在 2017 数据里不存在或导入时漏了中间层级。解决分版本建表跨版本查询走映射表导入后必跑孤儿校验 SQL发现孤儿先查是漏行还是版本混用漏行补导混用则隔离。5.4 LOAD DATA 权限报错现象是 ERROR 1148原因是 local_infile 未开或 secure_file_priv 限制现象执行LOAD DATA LOCAL INFILE报ERROR 1148 (42000): The used command is not allowed。原因客户端或服务端local_infile为 OFF或 MySQL 8.0 默认禁用 LOCAL。解决客户端启动加--local-infile1服务端my.cnf设local_infile1并重启。若用非 LOCAL 的LOAD DATA INFILE文件必须放在secure_file_priv指定目录下用SHOW VARIABLES LIKE secure_file_priv查看路径。5.5 版本字段写死现象是同一代码在不同年份含义不同却查出错数据原因是查询没带版本条件现象查代码3912返回的名称和预期不符。原因三版数据同表时没加WHERE version2017命中了 2011 版的同名代码。解决分表方案天然隔离同表方案则所有查询必须带version条件或在应用层封装查询方法强制传版本参数。这是最隐蔽的坑数据看着对口径已经错了。6. 把码表用活一个跨版本口径对齐的实战技巧码表导入只是起点真正体现价值的是跨版本统计口径对齐。我做过一个企业营收按行业汇总的报表历史数据用 2011 码新数据用 2017 码直接合并会漏统或重统。我的做法是在映射表基础上建一个「口径桥接视图」把 2011 小类统一折算到 2017 小类一对多的按预设比例分摊多对一的直接合并。下面这个视图把映射逻辑固化应用层查询不用再关心版本。CREATE VIEW v_industry_bridge AS SELECT m.code_2011, m.code_2017, n.name AS name_2017, m.map_type, CASE WHEN m.map_type 1 THEN 1.0 WHEN m.map_type 2 THEN 1.0 / (SELECT COUNT(*) FROM industry_mapping x WHERE x.code_2011 m.code_2011) ELSE 1.0 END AS split_ratio FROM industry_mapping m JOIN industry_2017 n ON m.code_2017 n.code;split_ratio字段是一对多时的分摊系数一对多拆成 N 个新类每个分1/N。这个系数是简化处理实际业务里拆分比例可能按营收分布定那就把比例存进映射表的remark或单独字段视图里读出来即可。多对一的情况split_ratio为 1因为多个老类合并进一个新类各自全额归入。用这个视图做汇总SELECT b.code_2017, b.name_2017, SUM(f.amount * b.split_ratio) AS amount_2017 FROM fact_revenue f JOIN v_industry_bridge b ON f.industry_code b.code_2011 GROUP BY b.code_2017, b.name_2017;这样历史数据自动折算到 2017 口径和新数据 UNION 后口径一致。验证方法是拿几个已知拆分案例手工核对金额比如某老类拆成三个新类三个新类金额之和应等于老类原值浮点误差除外。我一般会跑一个总量校验折算前后总金额差异应小于 0.01%超过就说明映射表有遗漏或比例写错。最后说个习惯每次拿到新的国标码表我都会先建一个_staging临时表导入原始数据跑完层级、孤儿、长度三项校验确认无误再INSERT INTO ... SELECT进正式表。多这一步比事后修数据省心得多。希望帮到你。本文还有配套的精品资源点击获取