★ FEATURED ARTICLE 简介淘宝全类目加属性SQL文件是一份电商数据库建设与数据查询的实用资源适合后端开发、数据分析师及电商运营人员使用。压缩包内仅含一个SQL脚本共353KB却整合了淘宝平台全部商品的分类、属性及属性值数据。其中分类采用层级结构精选一级至三级类目属性涵盖颜色、尺寸、品牌等维度每个属性均配置对应属性值结构清晰导入MySQL等关系型数据库后即可直接使用。借助该脚本读者可以快速完成类目树构建、熟悉电商SKU字段设计或按属性值进行筛选查询例如提取‘女装’类目下‘红色’商品的ID为商品推荐、竞品分析、市场趋势研究等场景提供便利。目前已有294人学习适合需要批量获取官方类目映射、减少重复抓取工作的开发者。这份数据相当于一份可直接运行的电商类目字典能显著缩短项目前期数据准备时间。1. 全类目加属性SQL一份能让基础数据跑起来的类目树资料做电商数据开发或者商品爬虫的人几乎都被类目数据折腾过。一般遇到的情况是接口拉下来几千个类目父子关系层层嵌套属性有一部分挂在叶子类目上有一部分全局共用手动拼SQL能把人拼到怀疑人生。这份“全类目加属性SQL”不是某个单一查询脚本而是一套完整的类目树数据包包含建表语句、全量类目数据、类目属性数据以及配套的校验SQL。它的核心价值在于把通常需要花费很多天去抓取清洗的类目与属性关系压缩成可以直接导入MySQL的脚本。适合数据开发、后端工程师和电商数据分析师能省掉最枯燥的原始数据整理阶段。2. 表结构设计为什么邻接表、ENUM属性和JSON值能扛住大批量查询拿到SQL数据包先别急着导入重点要看清楚表结构怎么设计的。我一直强调类目数据是典型的基础数据它的查询频率高、变更频率低所以结构设计决定了后续所有业务查询是否顺手。2.1 为什么要用邻接表存类目树类目树最常见的建模方式有三种邻接表parent_id字段、嵌套集left/right、闭包表path枚举。这套SQL包用的是邻接表原因很直接数据来源就是从接口上直接拉下来的扁平结构每条记录只带一个parent_id导入成本最低也最容易做增量更新。CREATE TABLE category ( id BIGINT UNSIGNED NOT NULL COMMENT 类目ID, parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父级类目ID0表示顶级根类目, name VARCHAR(64) NOT NULL COMMENT 类目名称, level TINYINT NOT NULL DEFAULT 1 COMMENT 层级深度1为一级类目, is_leaf TINYINT NOT NULL DEFAULT 0 COMMENT 是否叶子类目1是 0否, sort_order INT NOT NULL DEFAULT 0 COMMENT 同级排序权重, PRIMARY KEY (id), KEY idx_parent (parent_id), KEY idx_level (level) ) ENGINEInnoDB COMMENT商品类目树;这个结构里靠 parent_id 形成树形关系一级类目的 parent_id 为 0。level 字段是从一级类目开始算的可以在导入时根据 parent_id 递归推算也可以在脚本里按层级循环填充。is_leaf 比较关键业务上很多查询会直接限定“只查叶子类目”比如筛选商品SPU时非叶子类目通常不挂商品有了这个标记就不用每次递归判断了。为什么不用嵌套集或闭包表嵌套集在查询子树时确实高效但插入和移动节点都要连带修改一大片 left/right 值类目数据恰好又经常出现新增子类目、调整排序的需求用嵌套集维护成本太高。闭包表适合“频繁查询任意两节点关系”的场景但存储量随节点数平方级增长几万个类目会膨胀到几十万行对这份数据包来说没必要。邻接表配合MySQL 8.0的递归CTE查完整类目路径也就一条SQL的事性价比最高。2.2 属性表关键属性、销售属性与属性值是三个维度类目和属性的关系要理清楚这是整个数据包最核心的部分。属性挂在类目下但并不是所有属性对业务的价值都一样。这套SQL里把属性分成了 key、sale、normal 三种类型设计上很贴合商品发布和搜索的实际情况。CREATE TABLE attr ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, category_id BIGINT UNSIGNED NOT NULL COMMENT 所属叶子类目ID, attr_name VARCHAR(64) NOT NULL COMMENT 属性名称例如“颜色”, attr_type ENUM(key,sale,normal) NOT NULL DEFAULT normal COMMENT 属性类型key关键属性、sale销售属性、normal普通属性, value_type ENUM(enum,input,multi) NOT NULL DEFAULT enum COMMENT 取值方式枚举/手工输入/多选, options JSON DEFAULT NULL COMMENT 枚举型属性的可选值列表, sort_order INT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_category_attr (category_id, attr_name) ) ENGINEInnoDB COMMENT类目属性表;key 属性指关键属性就是用户搜索时最常用来筛选的那批字段比如“连衣裙”类目下的“裙长”“风格”sale 属性是销售属性直接影响SKU组合比如颜色、尺码normal 属性是普通描述属性。value_type 是取值方式enum 表示枚举型options 里存的是可选项列表比如颜色属性的 options 可能是 [“黑色”,“白色”,“藏青”]input 表示手工输入型比如产地multi 表示多选型比如适用年龄段可以勾选多个值。属性类型和取值方式一定要分开存因为一个枚举型属性可能在展示时是文本框、在发布时是下拉框它跟底层数据类型没有强绑定关系。options 为什么用 JSON 字段而不是单独建一张属性值表我见过不少项目一开始图省事把多个值拼成逗号字符串结果后面统计每个属性值下的商品数时才发现坑大了。JSON 至少能保证结构化语义MySQL 8.0 对它支持 JSON_EXTRACT、JSON_CONTAINS必要时还能加虚拟列建索引。如果一开始就拆成子表属性值的维护逻辑会复杂一截而且 JSON 类型对该数据包的属性可选值场景完全够用。2.3 脚本文件拆解与执行顺序这套数据包不是单一脚本而是按职责拆分的多个SQL文件执行顺序有讲究。一般包含四个部分结构脚本、类目数据脚本、属性数据脚本、校验脚本。先建库建表再导类目再导属性最后跑校验顺序乱了容易出现外键约束报错或查询结果对不上。脚本文件作用执行顺序category_schema.sql建库、建表、定义索引和字符集第1个category_data.sql插入全量类目数据含层级和叶子标记第2个attr_data.sql插入类目属性数据含属性类型和可选值第3个check_script.sql校验数据完整性孤儿记录、重复属性、类目断链第4个字符集统一用 utf8mb4排序规则在 MySQL 8.0 下推荐 utf8mb4_general_ci中文场景下比较稳定。如果原来的库用了 utf8mb4_unicode_ci 也不用改两者对类目名称的中文比较结果差异极小只是部分生僻字的权重略有区别。关键不是选哪个排序规则而是导入时客户端和库表字符集必须一致这个问题留到第五章展开聊。3. 从命令行到Navicat导入流程与三项质量校验数据包拿到手有人喜欢直接在终端敲命令有人习惯用图形化工具两条路都行。这里我把两种方式都走一遍顺带说明参数怎么配、报错怎么看。3.1 命令行导入与Navicat导入两条路命令行导入适合服务器环境也是排查问题最快的方式。核心就一条命令把SQL文件重定向进mysql客户端。mysql -h 127.0.0.1 -u root -p \ --default-character-setutf8mb4 \ --max_allowed_packet256M \ mall_category category_schema.sql-h 指定数据库地址如果是本机可以用127.0.0.1走TCP协议-u 和 -p 是用户名和密码提示--default-character-setutf8mb4 这一步特别关键如果漏了客户端可能用系统默认的 latin1 或 gbk 字符集去解析SQL文件中文字段会全部变成乱码--max_allowed_packet 指的是客户端允许的最大数据包类目数据文件比较大时把它调大到 256M 可以避免中途报 Packet too large。mall_category 是先建好的库名如果 schema 脚本里已经包含 CREATE DATABASE也可以不带库名直接导入。后续类目数据和属性数据用同样的命令依次执行只是把文件名换成 category_data.sql 和 attr_data.sql。如果用 Navicat方式更直观连接数据库后右键目标库选择“运行SQL文件”弹窗里选中对应脚本文件重点检查编码一栏是否选择了 UTF-8而不是当面的默认 ANSI。Navicat导入大文件时同样有 packet 限制如果文件特别大建议在连接属性里把“高级”里的 max_allowed_packet 调大或者把单个 INSERT 拆成多条再执行。命令行和图形化工具的结果一样但命令行报错信息更完整能直接看到是第几行出的问题。3.2 递归CTE把扁平类目还原成完整路径数据导入后表里的类目还是扁平的每个类目只有一个 parent_id看起来不直观。要把它还原成“连衣裙 长裙 碎花长裙”这样的完整路径用 MySQL 8.0 的递归CTE一条SQL搞定。这也是选邻接表结构最爽的地方。SET cte_max_recursion_depth 100; WITH RECURSIVE cat_path AS ( SELECT id, parent_id, name, level, CAST(name AS CHAR(500)) AS full_path FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.parent_id, c.name, c.level, CONCAT(cp.full_path, , c.name) FROM category c INNER JOIN cat_path cp ON c.parent_id cp.id ) SELECT id, name, level, full_path FROM cat_path WHERE full_path LIKE %连衣裙%;递归CTE分两部分锚点查询和递归查询。锚点部分先抓出所有顶级类目递归部分把子类目一层层拼到父类目的 full_path 后面。INNER JOIN 一定要用不能改成 LEFT JOIN否则一旦数据里有环路或者缺失父节点会导致递归无限循环或产生重复路径。最终查询里我用了 LIKE 模糊匹配因为 full_path 是拼接出来的没法走索引但几万行的类目数据全表扫描也很快这个场景不需要考虑慢SQL优化真正要优化的是对 name 字段的检索下一章单独说。3.3 导入后的三项质量校验数据导完别急着接业务先跑校验脚本确认数据本身没有硬伤。我一般固定跑三条查询总量统计、孤儿记录检查、关联断裂检查。SELECT COUNT(*) AS total_cats, SUM(is_leaf 1) AS leaf_cats, SUM(is_leaf 0) AS parent_cats FROM category;总量统计用来对比数据包说明里的预期行数。比如数据包标注有2万多个类目导入后数量对不上就要检查是不是上次导入残留了历史数据。SUM(is_leaf 1) 在MySQL里直接对布尔表达式求和得到叶子类目的数量不用写 CASE WHEN。SELECT c.id, c.name, c.parent_id FROM category c LEFT JOIN category p ON c.parent_id p.id WHERE c.parent_id ! 0 AND p.id IS NULL;这条查的就是孤儿记录即 parent_id 指向了一个不存在的类目。出现孤儿记录有两种可能数据包在生成时漏了某一条父类目或者导入时父类目被过滤掉了。左连接把父表没有匹配记录的类目筛出来如果结果非空必须回到原始数据去定位这类脏数据会直接影响后续递归查询。SELECT a.id, a.attr_name, a.category_id FROM attr a LEFT JOIN category c ON a.category_id c.id WHERE c.id IS NULL;第三条是检查属性表和类目表的关联有没有断裂。属性表里的 category_id 必须在类目表里存在否则商品发布页会出现一个属性挂在不存在的类目下。这三条跑完数据基本能确定是可用的了。4. 高频查询实战类目搜索、属性关联与窗口函数去重类目和属性数据就位后日常开发里最常写的三类查询是类目关键词搜索、按类目关联属性、清理重复数据。这三个场景分别对应普通的索引优化、多表关联和窗口函数用法每个都有值得注意的细节。4.1 类目搜索前缀匹配与ngram全文索引的取舍用户在前台搜索“连衣裙”时期望是看到所有名称里包含“连衣裙”的类目。最直接的写法是 LIKE %连衣裙%但问题在于前导通配符导致 MySQL 无法使用 B 树索引全表扫描在几万行数据下虽然不至于卡死可每次查询都做全表扫描并发一高就会暴露问题。ALTER TABLE category ADD FULLTEXT INDEX ft_cat_name (name) WITH PARSER ngram; SELECT id, name, parent_id, level FROM category WHERE MATCH(name) AGAINST (连衣裙 IN BOOLEAN MODE) ORDER BY level ASC LIMIT 20;MySQL 8.0 的全文索引对中文支持依赖 ngram 解析器它把中文句子按分词元组切分默认 token 大小是 2也就是至少两个字符才能命中。说明一点搜索“裙”这种单字会搜不到因为 ngram 默认不切单字如果业务上必须支持单字搜索要么改 ngram_token_size 参数并重建索引要么退回 LIKE 查询。实际项目里搜索类目名最常用的还是“品类词叶子类目”的组合两个字的词足够覆盖大部分需求了。加全文索引的好处是 MATCH AGAINST 可以走全文索引查询耗时会比全表 LIKE 明显低。另外要注意如果用户输入的是类目全路径的一部分比如“碎花”那搜 name 字段搜不到因为 name 只存了当前层级的类目名。正确做法是先查出候选类目ids再反向去 category 表里查它的祖先路径。批量场景下可以先查出全路径临时表再过滤递归CTE那部分已经给了范式。4.2 属性-类目关联查询里SQL去重的正确写法类目属性表在设计上是允许一个类目下出现多个同名属性的比如不同数据来源合并后“材质”可能出现两次。使用前必须去重。很多人第一反应是 SELECT DISTINCT 一把梭但 DISTINCT 会把 id 也纳入去重范围根本去不掉真正的重复记录正确做法是用窗口函数按业务键排优先级。CREATE TABLE tmp_attr_dedup AS SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY category_id, attr_name ORDER BY sort_order DESC, id ASC ) AS rn FROM attr ) t WHERE t.rn 1; DELETE a FROM attr a INNER JOIN tmp_attr_dedup d ON a.id d.id; DROP TABLE tmp_attr_dedup;PARTITION BY category_id, attr_name 意思是同一个类目下属性名相同的记录作为一组ROW_NUMBER 给组内每条记录编号rn 1 的即重复记录。ORDER BY sort_order DESC, id ASC 的作用是保留排序权重更高、ID更小的那条。注意这里不能直接把 DELETE 写在子查询里MySQL 不允许先 SELECT 同一张表再对它 DELETE会报“You cant specify target table for update in FROM clause”。建临时表绕一下是很标准的做法千万记得删临时表别留在库里占地方。如果属性表数据量极大或者去重是每天定时执行的场景临时表方案每次都要先建表再删表略繁琐。更省事的做法是直接在新表上重建数据先按去重逻辑 SELECT 出干净数据INSERT INTO 一张新表然后 RENAME 替换旧表。替换前最好先备份旧表基础数据出问题时有后悔药可吃。4.3 窗口函数给类目和属性做排序与聚合SQL 窗口函数在类目这种层级数据的处理上价值很大比如给某个类目下的所有属性排优先级或者把多个属性聚合成 JSON 串返回给前端。这里有两个常用场景。SELECT category_id, attr_name, attr_type, ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY CASE WHEN attr_type key THEN 0 WHEN attr_type sale THEN 1 ELSE 2 END, sort_order ) AS attr_order FROM attr WHERE category_id 168499 ORDER BY attr_order;这条SQL把“关键属性、销售属性、普通属性”的优先级转成了 0、1、2 的排序值再叠加 sort_order 作为次级排序。前端发布商品页需要按固定顺序展示属性表单用这一条查询就能拿到有序结果省掉了在业务代码里做Comparator的麻烦。SELECT category_id, JSON_ARRAYAGG( JSON_OBJECT(name, attr_name, type, attr_type) ) AS attr_list FROM attr GROUP BY category_id;JSON_ARRAYAGG 把组内的多行聚合成 JSON 数组一条SQL直接产出“类目对应的属性集合”很适合后台管理系统一次性拉取所有类目配置。要注意的是GROUP BY 聚合后字段顺序不受控制如果需要保持 key、sale、normal 的顺序得先用子查询给 attr_type 排序再在子查询外面做 JSON_ARRAYAGG。这类组合是类目属性数据包上最实用的两个窗口函数用法。5. 避坑指南导入和查询中最容易翻车的五个环节这套数据包本身不难但我在多个项目里实际用下来发现翻车场景高度集中。下面五条都是真实踩过的坑按“现象、原因、解决”写清楚希望你能绕开。5.1 导入后中文变成“????”或乱码现象SQL文件导入后执行 SELECT * FROM category LIMIT 10发现 name 字段全是问号或者类似“鏉庢兂”这种张冠李戴的乱码。原因多半是客户端连接字符集和文件字符集不一致。SQL文件是用 utf8mb4 生成的但 mysql 命令行客户端默认可能用了 latin1 或系统 locale 对应的字符集去解析文本。Navicat 导入时如果编码没选 UTF-8 也会出现同样问题。解决命令行导入时务必加上 --default-character-setutf8mb4并且在连接后先执行 SET NAMES utf8mb4 确认会话级字符集。Navicat 场景下在“运行SQL文件”弹窗的编码选择里明确指定 UTF-8。已经导错的库不用重新导如果乱码发生在导入阶段直接 DROP 掉整表重新导入即可如果只是展示层乱了检查连接串里 characterEncoding 参数。5.2 递归CTE查询跑半天不出结果服务器CPU飙高现象执行类目路径查询时查询一直转圈数据库 CPU 直接飙到 100%最后超时。原因类目数据里存在环路也就是 A 的 parent_id 是 BB 的 parent_id 又指回 A。递归CTE在遇到环时会产生无限递归或者 parent_id 指向了非根目录的父级但父级缺失导致 JOIN 不断产生新行。解决先跑上文的孤儿记录检查把缺父节点的行清理掉同时执行一个环检测SQL把“父的父等于自己”这种记录找出来删除。另外在递归CTE里强制设置 cte_max_recursion_depth防止程序无限跑SET cte_max_recursion_depth 100。默认值是60是个安全阀正常类目树深度一般不超过10层设100足够。查完记得把会话级变量恢复否则影响同会话其他查询。5.3 属性表里 JSON 字段查询慢LIKE 全表扫描现象业务里要按属性值筛选商品比如查 options 里包含“白色”的记录SQL 写成 WHERE options LIKE %白色%数据量一大就慢到不可接受。原因JSON 字段本身不能建普通索引LIKE 又带前导通配符必然全表扫描。这是设计阶段最容易被忽略的问题属性值往往要到实际查询性能测试时才暴露。解决如果只是偶尔查一两个属性值全表扫描可以忍但作为高频查询就必须处理。常见做法是针对 JSON 里的高频字段建虚拟列再在虚拟列上建索引。ALTER TABLE attr ADD COLUMN first_value VARCHAR(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(options, $[0]))) VIRTUAL; ALTER TABLE attr ADD INDEX idx_first_value (first_value);GENERATED ALWAYS AS 定义的是虚拟列VIRTUAL 关键字表示不占用物理存储。JSON_EXTRACT 取出 options 数组第一个元素JSON_UNQUOTE 去掉引号。之后查询用 WHERE first_value 白色 就能走 id 上的索引。如果属性值不止一个且都要查询那就别死磕JSON把属性值和类目关系拆成子表是更彻底的做法。5.4 MyBatis-Plus 自动建表把字段截短或类型改掉现象项目启动后应用自带的 MyBatis-Plus 自动建表逻辑把 category 表的 name 字段从 VARCHAR(64) 改成了 VARCHAR(255)或者把 options 字段变成了 TEXT导致现有数据差点溢出。原因很多团队习惯用实体类配合 MyBatis-Plus 的自动建表 SQL 生成功能去维护表结构开发环境里把 DDL 策略设成了 update。实体类里 String 类型没指定长度时默认生成 VARCHAR(255)和基础数据脚本里给的 VARCHAR(64) 冲突启动时就自动执行了 ALTER TABLE。属性表的 JSON 字段更危险实体类如果映射成 String自动建表会把它改写成 TEXTMySQL 8.0 里 TEXT 和 JSON 的底层存储方式不同依赖 JSON 函数的查询直接报错。解决生产环境绝对不能用自动建表策略管理已经有数据的表。把 DDL 策略改成 none 或 validate表结构统一用资源包里的 schema 脚本管理。实体类只做 ORM 映射字段注解里的 length 属性要和 SQL 脚本保持完全一致。养成习惯每次改表结构先改 SQL 脚本再改实体发布时确认两个文件同时上线。5.5 大SQL文件导入中途报错“Packet too large”现象导入 category_data.sql 或 attr_data.sql 到一半终端报错 Number of packets is larger than max_allowed_packet或者直接提示 Lost connection during query数据只进去了一部分。原因类目数据文件包含大批量 INSERT单条语句超过数据库允许的最大数据包大小。max_allowed_packet 的默认值在 MySQL 8.0 里是 64MB如果数据文件里把上万条插入写成了一条超长语句就被截断了。解决命令行导入时把会话级参数调大mysql 客户端命令里直接加 --max_allowed_packet256M。Navicat 里在连接属性的高级选项卡里同步调整。如果文件真的大到几百MB更稳妥的做法是拆分成若干个小文件分批导入每个文件控制在 50MB 以内也方便定位哪一批数据有语法问题。导入中途报错不会自动回滚所以重新导入前必须先清空目标表否则重复数据叠加后还得重新去重。6. 从一次性导入变成长效数据服务增量更新与验证技巧类目数据不是一成不变的平台会不定期增加新类目、调整层级、补充属性。如果这套SQL包只做一次性导入三个月后就得重新操心数据过期的问题。我建议把它改造成一个小型的版本化数据服务每次更新都留下痕迹上线前有明确的验收口径。最简单的增量模型是建一张版本表记录每次导入的数据日期和变更行数。CREATE TABLE category_version ( version_id INT AUTO_INCREMENT PRIMARY KEY, version_no VARCHAR(32) NOT NULL COMMENT 版本号例如20250901, data_date DATE NOT NULL COMMENT 数据快照日期, changed_rows INT DEFAULT 0 COMMENT 本次更新变更的行数, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_version_no (version_no) ) COMMENT类目数据版本表;更新流程走四步先把新数据快照导入到一个 staging 临时表用 NOT EXISTS 保留新新增和修改的类目记录变更行数再执行正式表的替换逻辑最后做一次完整性校验。校验SQL是我的固定动作检查每个叶子类目是否至少有一条关键属性没有key属性的类目在商品发布页会出现属性表单为空的情况。SELECT c.id, c.name, COUNT(a.id) AS key_attr_cnt FROM category c LEFT JOIN attr a ON c.id a.category_id AND a.attr_type key WHERE c.is_leaf 1 AND c.parent_id ! 0 GROUP BY c.id, c.name HAVING key_attr_cnt 0 LIMIT 10;为了提升线上查询性能我一般还会给 category 表补一个 depth 冗余字段在增量导入时用递归CTE把每个类目的深度算好直接写进去避免业务SQL里实时递归。这个动作虽然有点冗余但对查询的优化效果是立竿见影的。走过一次“线上查询没加depth、递归路径每次实时算”的弯路之后我现在每次导入类目数据都要强制跑一遍孤儿检测、重复属性检测和叶子类目key属性校验这三道检查确认全绿了才敢对外放开接口。希望这个流程也能帮你少走弯路。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
