三版国民经济行业分类SQL文件：导入、跨版本映射与ETL清洗实战

简介本资源面向从事大数据清洗与行业维度标准化的技术人员提供2002、2011、2017三个年度的国民经济行业分类与代码MySQL数据文件对应GB/T4754-2002、GB/T4754-2011、GB/T4754-2017三版国家标准。每个代码均按“门类·大类·中类·小类”四级结构组织例如“A0111”对应“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”便于直接入库做行业映射与口径统一。压缩包共3个文件均为sql脚本整体约44KB体积轻便导入后即可用于数据仓库维表构建、行业代码转换与历史版本对照。已有3034人学习下载适合需要跨年度行业分类对齐、做数据标准化与清洗的工程师参考使用。1. 三套国标行业代码躺在三个 SQL 文件里到底怎么用做数据清洗的人多半遇到过这种场面业务方丢来一张企业表行业字段写的是「农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植」这种长串中文或者干脆是A0111这种带字母前缀的编码让你按行业维度做聚合。你手上没有权威的对照表只能靠LIKE硬匹配结果「农业」和「农、林、牧、渔业」混在一起统计口径直接崩掉。这份2002_2011_2017国民经济行业分类与代码mysql数据四级分类文件.rar解决的就是这件事——它把 GB/T4754 三个版本2002、2011、2017的国民经济行业分类与代码整理成了可直接导入 MySQL 的 SQL 文件每个版本一个std_code_xxxx.sql代码按「门类·大类·中类·小类」四级结构组织第一列是形如A0111的编码第二列是对应的四级中文路径。适合做行业维度清洗、标准化、映射对齐的大数据开发和数据治理人员尤其是需要跨年份做口径统一的场景。2. 拆开压缩包三个 SQL 文件的结构与字段设计2.1 四级分类的编码逻辑国民经济行业分类采用层级编码GB/T4754 的编码规则是「门类用一位字母大类用两位数字中类用三位数字小类用四位数字」。以摘要里给的例子拆解2002 0111 A0111 农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植2002版本年份标识这条记录属于哪一版国标0111纯数字编码四位对应小类层级A0111带门类字母前缀的完整编码A是门类农、林、牧、渔业0111是往下三级的数字最后一列用·分隔的四级中文路径依次是门类、大类、中类、小类这种设计的好处是你既能用A0111做精确匹配也能用0111做跨版本的数字对齐还能把中文路径SPLIT开分别拿到门类名、大类名、中类名、小类名。三个文件结构一致意味着你可以把三张表UNION起来做跨年份分析。2.2 导入前的表结构确认SQL 文件是纯INSERT还是带CREATE TABLE直接决定你怎么导入。常见做法是先看文件头部# 看 SQL 文件开头 20 行确认有没有建表语句 head -n 20 std_code_2017.sql # 统计 INSERT 行数估算数据量 grep -c INSERT INTO std_code_2017.sql如果文件里已经带了CREATE TABLE直接source导入即可如果只有INSERT你得先自己建表。我一般会按下面这个结构建字段类型留足余量CREATE TABLE std_code_2017 ( id INT AUTO_INCREMENT PRIMARY KEY, year SMALLINT NOT NULL COMMENT 版本年份, code_num VARCHAR(8) NOT NULL COMMENT 纯数字编码如0111, code_full VARCHAR(16) NOT NULL COMMENT 带门类前缀编码如A0111, name_path VARCHAR(255) NOT NULL COMMENT 四级中文路径·分隔, KEY idx_code_full (code_full), KEY idx_code_num (code_num) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2017版国民经济行业分类;code_full和code_num都建索引因为实际查询里这两种匹配方式都会用到。name_path给到 255 是因为四级中文路径最长可能超过 100 字符留足空间避免截断。字符集用utf8mb4中文路径里偶尔会有生僻字utf8三字节可能不够。2.3 导入命令与验证建好表之后导入mysql -u root -p your_database std_code_2017.sql导入完必须验证行数和层级完整性不然数据缺了你都不知道-- 看总行数 SELECT COUNT(*) FROM std_code_2017; -- 看门类有几个正常应该是 A~T 共 20 个左右 SELECT LEFT(code_full, 1) AS menlei, COUNT(*) FROM std_code_2017 GROUP BY menlei ORDER BY menlei; -- 抽查一条四级路径是否完整按·分隔应该有4段 SELECT code_full, name_path, LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 AS seg_count FROM std_code_2017 WHERE LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 4 LIMIT 10;最后那条查询是关键如果seg_count不等于 4说明这条记录的中文路径层级不完整可能是数据本身的问题也可能是导入时被截断了。正常情况应该返回空结果。这一步能帮你提前发现脏数据别等到业务查询出错了才回头查。3. 跨版本映射2002 到 2017 的行业代码怎么对齐3.1 为什么不能直接 JOIN三个版本的行业分类不是简单的一对一关系。2011 版对 2002 版做了大量拆分和合并2017 版又对 2011 版做了调整。比如某个行业在 2002 版是一个小类到 2011 版被拆成两个到 2017 版又合并回来。直接JOIN ON code_num会丢数据或者产生笛卡尔积。常见做法是建一张映射表人工维护版本间的对应关系。但在这份资源的基础上你可以先用中文路径做模糊对齐把能自动匹配的先匹配上剩下的再人工处理-- 用中文路径精确匹配找出三个版本都存在的行业 SELECT a.code_full AS code_2002, b.code_full AS code_2011, c.code_full AS code_2017, a.name_path FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;这条查询能跑出多少条取决于三个版本之间有多少行业名称完全没变。实际跑下来通常只有一部分能精确匹配剩下的需要按门类或大类做降级匹配。3.2 降级匹配策略当小类对不上时退到大类或中类层级做匹配。用SUBSTRING截取编码前缀-- 按大类前3位数字匹配找出2002和2017在同一大类下的行业 SELECT a.code_full AS code_2002, a.name_path AS name_2002, c.code_full AS code_2017, c.name_path AS name_2017 FROM std_code_2002 a JOIN std_code_2017 c ON SUBSTRING(a.code_num, 1, 3) SUBSTRING(c.code_num, 1, 3) WHERE a.name_path c.name_path LIMIT 50;SUBSTRING(code_num, 1, 3)取的是中类层级。这样匹配出来的结果会有多对多的情况需要业务方确认口径。我一般会把匹配结果导出成 CSV让业务方标注哪些是可以接受的映射哪些需要人工指定。3.3 用临时表固化映射关系匹配结果确认后建一张映射表存下来后续 ETL 直接查这张表CREATE TABLE industry_mapping ( id INT AUTO_INCREMENT PRIMARY KEY, code_2002 VARCHAR(16), code_2011 VARCHAR(16), code_2017 VARCHAR(16), match_level TINYINT COMMENT 1小类精确 2中类 3大类, remark VARCHAR(255) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 把精确匹配的结果灌进去 INSERT INTO industry_mapping (code_2002, code_2011, code_2017, match_level) SELECT a.code_full, b.code_full, c.code_full, 1 FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;match_level字段很重要它告诉你这条映射的可靠程度。做报表时如果只想要高置信度的数据加个WHERE match_level 1就行。remark留给人工补充说明比如「2011版拆分自2002版XX行业」这种备注。注意跨版本映射没有百分之百自动化的方案国标调整本身就有业务判断成分。这份资源提供的是标准代码底座映射逻辑得结合你的业务场景来定。4. 避坑与排查导入和查询时最容易翻车的几个点4.1 中文乱码现象是查询出来全是问号现象导入后SELECT出来的中文路径显示为????或者乱码字符。原因SQL 文件的编码和数据库、连接字符集不一致。常见情况是文件是utf8编码但 MySQL 服务端默认latin1或者客户端连接没指定字符集。解决先确认文件编码file std_code_2017.sql看输出。如果是 UTF-8导入时显式指定mysql -u root -p --default-character-setutf8mb4 your_database std_code_2017.sql建表时也要确认DEFAULT CHARSETutf8mb4。如果已经导入错了TRUNCATE TABLE重来比修数据快。4.2 编码前缀字母大小写不一致现象WHERE code_full a0111查不到数据但WHERE code_full A0111能查到。原因MySQL 默认排序规则utf8mb4_general_ci是大小写不敏感的但如果你建表时用了utf8mb4_bin或者查询时用了BINARY关键字就会变成大小写敏感。另外有些 SQL 文件里门类字母可能混用了大小写。解决统一用大写。建表时排序规则用utf8mb4_general_ci查询时用UPPER(code_full) UPPER(a0111)做兼容。更稳妥的做法是在 ETL 层就把编码统一转成大写再入库。4.3 四级路径分隔符不统一现象按·分隔取第四级时有些记录取出来是空或者多了一段。原因不同年份的文件可能用了不同的分隔符比如 2002 版用·2017 版混用了·和-。或者某些行业名称本身包含·字符。解决导入前先检查分隔符一致性SELECT DISTINCT SUBSTRING(name_path, LOCATE(·, name_path) - 1, 1) AS sep_check FROM std_code_2017 LIMIT 5;如果发现混用在 ETL 层统一替换。行业名称本身含·的情况比较少见但一旦出现就会导致SPLIT结果错位需要人工修正那几条记录。4.4 导入大文件时超时或内存溢出现象source导入时卡住或者报MySQL server has gone away。原因SQL 文件太大单条INSERT语句过长超过了max_allowed_packet限制。解决临时调大参数再导入SET GLOBAL max_allowed_packet 256 * 1024 * 1024; SET GLOBAL net_read_timeout 600;如果文件里是逐条INSERT也可以考虑用mysqlimport或者先把 SQL 拆成多个小文件分批导入。导入完成后把参数改回去别一直开着。4.5 版本年份字段缺失或错误现象三张表UNION之后发现某些记录的年份对不上或者年份字段是空的。原因SQL 文件里可能没有显式的年份列年份信息只体现在文件名上。如果你建表时没加year字段或者导入时没填就会丢版本信息。解决导入时手动补年份。如果文件里没有年份列可以在导入后UPDATEALTER TABLE std_code_2002 ADD COLUMN year SMALLINT DEFAULT 2002; ALTER TABLE std_code_2011 ADD COLUMN year SMALLINT DEFAULT 2011; ALTER TABLE std_code_2017 ADD COLUMN year SMALLINT DEFAULT 2017;或者建表时就带上year字段导入时用LOAD DATA指定。别依赖文件名来区分版本数据落到表里之后文件名就没意义了。5. 把行业代码接进 ETL一个可复用的清洗函数5.1 用存储过程做行业名称标准化实际清洗时业务表里的行业字段往往是自由文本需要映射到标准代码。写一个存储过程输入中文关键词返回最匹配的标准编码DELIMITER // CREATE PROCEDURE match_industry_code( IN input_text VARCHAR(255), IN ver_year SMALLINT, OUT out_code VARCHAR(16), OUT out_path VARCHAR(255) ) BEGIN DECLARE tbl_name VARCHAR(32); SET tbl_name CONCAT(std_code_, ver_year); -- 优先精确匹配完整路径 SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path input_text LIMIT 1; -- 精确匹配不到用 LIKE 做模糊匹配取最长匹配 IF out_code IS NULL THEN SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path LIKE CONCAT(%, input_text, %) ORDER BY LENGTH(name_path) DESC LIMIT 1; END IF; END // DELIMITER ;调用方式CALL match_industry_code(谷物的种植, 2017, code, path); SELECT code, path;这个存储过程的核心逻辑是「先精确后模糊模糊匹配取最长路径」。ORDER BY LENGTH(name_path) DESC保证匹配到的是最具体的那个行业而不是笼统的门类。ver_year参数目前只用来拼表名实际查询里我写死了std_code_2017你可以根据需求改成动态 SQL。5.2 批量清洗的 SQL 写法如果业务表有几千条记录要清洗逐条调存储过程太慢。用JOIN批量做-- 假设 biz_company 是业务表industry_name 是自由文本行业字段 UPDATE biz_company b JOIN std_code_2017 s ON b.industry_name s.name_path SET b.industry_code s.code_full, b.industry_std s.name_path WHERE b.industry_code IS NULL;先跑精确匹配把能对上的更新掉。剩下的用模糊匹配UPDATE biz_company b JOIN ( SELECT b2.id, s.code_full, s.name_path FROM biz_company b2 JOIN std_code_2017 s ON b2.industry_name LIKE CONCAT(%, SUBSTRING_INDEX(s.name_path, ·, -1), %) WHERE b2.industry_code IS NULL ORDER BY LENGTH(s.name_path) DESC ) m ON b.id m.id SET b.industry_code m.code_full, b.industry_std m.name_path;SUBSTRING_INDEX(name_path, ·, -1)取的是四级路径的最后一段也就是小类名称。用业务字段去LIKE匹配小类名比匹配完整路径的命中率高。ORDER BY LENGTH DESC保证优先匹配更具体的小类。5.3 验证清洗覆盖率清洗完必须看覆盖率不然你以为洗完了其实漏了一大半SELECT COUNT(*) AS total, SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) AS matched, ROUND(SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS match_rate FROM biz_company;match_rate低于 80% 就说明匹配逻辑有问题要么是业务字段太脏要么是标准表里没有对应行业。把没匹配上的记录导出来看看SELECT DISTINCT industry_name FROM biz_company WHERE industry_code IS NULL LIMIT 100;这些就是需要人工处理的硬骨头。我一般会把它们丢给业务方确认而不是自己硬猜。提示清洗函数和批量 SQL 建议先在测试库跑一遍确认匹配率和数据质量后再上生产。行业代码一旦写错下游的报表和模型全跟着错。6. 版本差异比对与增量更新技巧三个版本之间的差异不只是代码变了分类的粒度也在变。2017 版相比 2011 版小类数量有增减部分行业被重新归类。做跨年分析时如果直接用 2017 版的分类去套 2002 年的数据口径会对不上。我一般会先跑一个版本差异统计看看三个版本各有多少个门类、大类、中类、小类SELECT 2002 AS ver, COUNT(DISTINCT LEFT(code_full,1)) AS menlei, COUNT(DISTINCT SUBSTRING(code_num,1,2)) AS dalei, COUNT(DISTINCT SUBSTRING(code_num,1,3)) AS zhonglei, COUNT(*) AS xiaolei FROM std_code_2002 UNION ALL SELECT 2011, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2011 UNION ALL SELECT 2017, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2017;这个查询能让你一眼看出三个版本的分类粒度变化。如果 2017 版的小类数量比 2002 版多了不少说明行业细分程度提高了跨年对比时要注意口径膨胀的问题。另一个实用技巧是找出「只在某一个版本里存在」的行业代码-- 找出2017版有但2002版没有的行业 SELECT code_full, name_path FROM std_code_2017 WHERE code_full NOT IN (SELECT code_full FROM std_code_2002) ORDER BY code_full;这些就是新增行业做历史数据回溯时得特别标注。反过来2002 版有但 2017 版没有的就是被合并或删除的行业。把这两个列表导出来就是一份版本迁移的差异清单。增量更新时如果国标出了新版本你只需要按同样的表结构建一张新表导入新 SQL 文件然后在映射表里补充新版本的对应关系。已有的映射记录不用动只加新版本列就行。我习惯在映射表里加一个update_time字段记录每条映射最后确认的时间方便追溯。从那以后我每次拿到新的国标代码文件都会先跑一遍层级完整性校验和版本差异统计确认数据没问题再往生产库导。这个习惯帮我挡掉过好几次因为文件本身缺行导致的口径错误。希望帮到你。本文还有配套的精品资源点击获取