简介这是一份包含淘宝全类目、属性及属性值数据的SQL文件面向需要处理电商商品结构化数据的数据库开发、数据分析或后端工程师也适合用于类目树设计、商品筛选与统计分析等场景。压缩包仅1个文件为单个SQL脚本整体大小353KB导入数据库后即可直接查询、关联和扩展轻量实用。目前已有293人学习下载可作为商品类目字典或初始化基础数据使用。借助该SQL可快速了解淘宝多层级的类目体系理解属性与属性值如何关联商品并在此基础上开展类目导航、商品推荐、市场分析或数据同步等开发工作也可用于学习SQL表结构设计与复杂查询写法帮助熟悉电商平台核心数据模型。文件体量精简、结构明确能有效降低自行采集整理类目数据的成本是一份实用型数据资产。 聊到“淘宝全类目加属性SQL”不少做电商数据分析、商品管理后台、或者爬虫数据清洗的朋友应该都有共鸣——淘宝的类目体系庞大且层级复杂每个类目下面又挂了不同的销售属性、关键属性如果靠手工一条条去整理基本是灾难。这个需求本质上不是“要不要用SQL”而是“怎么把一套层级化、动态化的电商类目数据结构化地落进数据库”并保证后续业务能高效查询和维护。这篇文章我就围绕“如何设计并生成一份可用的淘宝全类目加属性SQL脚本”展开把我在实际项目里踩过的坑、用过的方案、优化过的执行细节都写出来。无论是你只想把类目和属性导入 Mysql 做数据分析还是想给运营后台做联动筛选这篇内容都能直接落地参考。1. 项目背景与核心需求拆解1.1 这个需求到底在解决什么问题先说清楚这不是一个“从淘宝官方接口拉数据”的教程因为官方开放平台的类目权限申请门槛不低很多中小型团队根本拿不到全量类目数据。现实情况往往是你手上有一份通过采集工具或历史项目积累下来的类目 JSON / Excel里面有类目 ID、类目层级、父级 ID、属性名、属性值列表但它是嵌套结构没法直接用于数据库查询。所以“淘宝全类目加属性 SQL”这句话翻译成实际需求就是三件事把嵌套的类目树拍平变成一张父子关系清晰的类目表。把类目下挂的属性解析出来拆成属性主表和属性值子表。生成一套可重复执行的 SQL 脚本建表、导数据、加索引一气呵成。1.2 这套 SQL 能做哪些事、适合谁用这套 SQL 一旦落地直接能支撑的场景非常多。比如电商后台的商品发布页需要根据前台类目联动展示对应的品牌、尺码、颜色等属性再比如数据分析团队需要统计某个一级类目下所有叶子类目的商品数如果没有一张完整的类目树表这个统计基本没法写。适合的人群大概是三类数据开发 / 后端工程师需要把外部采集的类目数据快速入库给业务方提供查询接口。数据分析师做类目维度拆解分析需要一个稳定且更新的维度表。爬虫方向的技术爱好者采集到数据后不知道怎么标准化存储可以参考这套表结构和 SQL 设计。1.3 技术方案的选型思路市面上有人直接用 JSON 字段把属性塞进一张表这样做也不是不行但查询属性时就要用 LIKE 去捞数据量一大必然卡死。合理的方案是关系型建模 汇总冗余字段。用 MySQL 作为载体类目表、属性表、属性值表三张表分开存这是比较通用的做法。类目表用自增主键和 parent_id 构建层级属性表用类目 ID 做外键关联属性值表再关联属性 ID。查询时如果需要一次性拿全路径可以通过冗余一个category_path字段来避免递归查询。2. 数据模型设计类目树与属性的存储结构2.1 类目表设计要点淘宝类目体系最高有三级部分类目甚至到第四级。建表时最核心的字段是parent_id和level。parent_id指向上级类目顶级类目默认为 0level从 1 开始计数。我当时设计的类目表结构大致是这样CREATE TABLE tb_category ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 自增主键, cat_id bigint(20) NOT NULL COMMENT 淘宝类目ID, cat_name varchar(100) NOT NULL COMMENT 类目名称, parent_id bigint(20) NOT NULL DEFAULT 0 COMMENT 父级类目ID, level tinyint(4) NOT NULL DEFAULT 1 COMMENT 层级1一级类目, is_leaf tinyint(1) NOT NULL DEFAULT 0 COMMENT 是否叶子类目, category_path varchar(500) DEFAULT NULL COMMENT 冗余路径如女装/连衣裙/碎花裙, sort_order int(11) NOT NULL DEFAULT 0 COMMENT 排序, created_at datetime DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_cat_id (cat_id), KEY idx_parent_id (parent_id), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT淘宝类目表;这里有两个细节值得注意第一cat_id必须加唯一索引。因为淘宝类目 ID 在源数据里就是唯一的后续如果做增量更新直接用INSERT ... ON DUPLICATE KEY UPDATE就能幂等执行。第二category_path字段是典型的空间换时间。虽然第三范式要求路径通过关联查询算出来但实际业务中 90% 的场景都是直接展示全路径与其每次递归查不如在导入时就拼好。2.2 属性表与属性值表的设计属性这块比类目表稍微复杂一点。一个类目下面可能挂多个属性一个属性又可能有多个属性值。比如“连衣裙”这个类目属性有“尺码”、“颜色”、“裙长”其中“尺码”的值可能是“S、M、L、XL”。这种一对多的嵌套结构必须拆两张表。属性主表CREATE TABLE tb_category_attr ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 自增主键, cat_id bigint(20) NOT NULL COMMENT 类目ID, attr_id bigint(20) NOT NULL COMMENT 属性ID, attr_name varchar(100) NOT NULL COMMENT 属性名称, attr_type tinyint(4) NOT NULL DEFAULT 1 COMMENT 属性类型1关键属性2销售属性3非关键属性, is_required tinyint(1) NOT NULL DEFAULT 0 COMMENT 是否必填, sort_order int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_cat_attr (cat_id, attr_id), KEY idx_attr_name (attr_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT类目属性表;属性值表CREATE TABLE tb_category_attr_value ( id int(11) NOT NULL AUTO_INCREMENT, attr_id bigint(20) NOT NULL COMMENT 属性ID, value_id bigint(20) NOT NULL COMMENT 属性值ID, value_name varchar(200) NOT NULL COMMENT 属性值名称, sort_order int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_attr_value (attr_id, value_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT属性值表;之所以单独拆属性值表是因为同一个属性在不同类目下会有不同的值集合。比如“尺码”在女装下有“S、M、L”在童装下却有“80、90、100、110”。如果只用一个字段存值后续做属性筛选时没法用索引。2.3 为啥不直接用 JSON 字段一把梭我知道很多人会图省事把属性全部塞进一个 JSON 字段或者用一个attr_name和attr_value的纵表存。这两种方案在数据量小的时候看着方便但一旦涉及联合查询和统计就非常痛苦。纵表虽然扩展性强但同一行数据要取出多个属性时就只能做大量的行转列操作SQL 写起来又长又容易出错。JSON 字段的问题则是没法对内部属性建索引一旦某个类目下商品属性需要作为筛选条件查询性能会很差。拆表的代价也不高无非是导入时多两次INSERT但查询和维护的体验提升是实打实的。3. 数据采集与清洗从原始数据到可用 SQL3.1 源数据从哪来、长什么样通常拿到的原始数据会有四种形态淘宝开放平台接口返回的 JSON全量权限一般团队拿不到。采集工具导出的类目页 HTML 或 JSON 文件。第三方数据服务商提供的 Excel 表格。网上各种开源项目里已经抓好的 SQL / CSV。不管哪种形态内部逻辑都是一致的类目是树形结构属性是挂在叶子类目或非叶子类目下的键值对。我以最常见的 JSON 为例说明处理思路下面是简化后的结构[ { cat_id: 16, cat_name: 女装/女士精品, parent_id: 0, level: 1, children: [ { cat_id: 1621, cat_name: 连衣裙, parent_id: 16, level: 2, leaf: true, attrs: [ { attr_id: 122216001, attr_name: 尺码, attr_type: 销售属性, is_required: true, values: [ {value_id: 1, value_name: S}, {value_id: 2, value_name: M} ] } ] } ] } ]拿到这个 JSON 后最关键的一步是把嵌套数据拍平。很多人会手动去重、手写 INSERT结果数据一多就乱了。正确做法是先写一个简单的解析脚本把类目、属性、属性值拆成三个文件或三张临时表再统一导入。3.2 清洗时的三个关键检查点检查点一类目 ID 重复。淘宝偶尔会出现同一个类目 ID 在文件里出现多次但名称略有差异的情况。这种一般以最新出现的为准或者以叶子类目优先级更高的那条为准。检查点二属性的父子继承。有些属性挂在二级类目上但它的子类目实际上也继承了这些属性。导入时如果只处理叶子类目会导致部分数据缺失。我的做法是先导入所有类目再为每个非叶子类目生成一张“继承关系临时表”把父级属性和子级类目做笛卡尔积关联再统一导入属性表。检查点三属性值去重。同一个 value_name 在不同属性下可能对应不同的 value_id。所以属性值表唯一索引必须建立在(attr_id, value_id)上不能只对 value_id 建唯一。3.3 脚本生成策略直接生成 SQL 文件清洗完成后可以直接生成一份 .sql 文件也可以生成 CSV 再用LOAD DATA导入。这里我更推荐直接生成 SQL 脚本理由有两点SQL 脚本自带建表语句可以直接在目标环境跑不需要额外准备导入工具。只要加上INSERT IGNORE或ON DUPLICATE KEY UPDATE就能保证脚本可重复执行。生成时注意控制单条 INSERT 的规模比如每条 INSERT 包含 500 行数据避免单条 SQL 过长导致max_allowed_packet报错。4. SQL 脚本的完整实操从建表到数据入库4.1 建表脚本的执行顺序如果你的环境还没有这三张表建议按以下顺序执行tb_categorytb_category_attrtb_category_attr_value执行顺序不能乱因为属性表依赖类目表的cat_id属性值表依赖属性表的attr_id。如果是重复执行要么先DROP TABLE再重建要么基于唯一索引做幂等插入。生产环境我一般用后者因为直接 DROP 会丢失业务运行期间手工补充的数据。-- 如果只是临时测试可以这样清理重建 DROP TABLE IF EXISTS tb_category_attr_value; DROP TABLE IF EXISTS tb_category_attr; DROP TABLE IF EXISTS tb_category; SOURCE /path/to/schema.sql;4.2 批量导入套路示范类目表批量导入的 SQL 长这样INSERT INTO tb_category (cat_id, cat_name, parent_id, level, is_leaf, category_path, sort_order) VALUES (16, 女装/女士精品, 0, 1, 0, 女装/女士精品, 1), (1621, 连衣裙, 16, 2, 1, 女装/女士精品/连衣裙, 2), ... ON DUPLICATE KEY UPDATE cat_name VALUES(cat_name), parent_id VALUES(parent_id), level VALUES(level), is_leaf VALUES(is_leaf), category_path VALUES(category_path);属性表批量导入类似只不过需要在ON DUPLICATE KEY UPDATE里更新属性名称和是否必填字段。属性值表更新频次较低但一旦源数据修正同样用UPDATE覆盖。这里有个优化技巧如果是千万级以上的数据量先用ALTER TABLE ... DISABLE KEYS关闭非唯一索引导入完成后再ENABLE KEYS速度能快一个量级。唯一索引不受这个开关控制所以不用担心重复数据混入。4.3 查询实践如何用一条 SQL 拿到完整属性列表这是很多朋友实际最关心的一点。属性表拆开后要一次性查出某个类目下挂的所有属性和值用 LEFT JOIN 就能搞定SELECT c.cat_id, c.cat_name, a.attr_id, a.attr_name, a.attr_type, a.is_required, v.value_id, v.value_name FROM tb_category c LEFT JOIN tb_category_attr a ON c.cat_id a.cat_id LEFT JOIN tb_category_attr_value v ON a.attr_id v.attr_id WHERE c.cat_id 1621 ORDER BY a.sort_order, v.sort_order;如果还想同时把父级类目的属性也带出来就在 WHERE 条件里加上c.category_path LIKE CONCAT(%, 女装, %)或者用c.parent_id IN (...)把父子类目一起捞出来再去重展示。4.4 WITH RECURSIVE 实现任意层级子类目查询MySQL 8.0 以上支持递归 CTE这个功能用来查类目树非常合适。比如我要查“女装”下所有层级的叶子类目 ID可以直接写WITH RECURSIVE cat_tree AS ( SELECT cat_id, cat_name, parent_id, level FROM tb_category WHERE cat_id 16 UNION ALL SELECT c.cat_id, c.cat_name, c.parent_id, c.level FROM tb_category c INNER JOIN cat_tree ct ON c.parent_id ct.cat_id ) SELECT cat_id, cat_name FROM cat_tree WHERE is_leaf 1;这套写法在老版本的 MySQL 里只能用多次 JOIN 或临时表替代但不管是哪种方式核心思路都是利用 parent_id 顺着树往下走。5. 常见问题与排查技巧实录5.1 中文乱码这是最常踩的坑。导入后发现表中中文全是问号99% 的原因是连接字符集和表字符集不一致。建表时已经用了 utf8mb4但客户端连接时如果设的是 latin1照样会乱。解决办法是在执行 SQL 文件前先执行SET NAMES utf8mb4;在命令行下导入时还可以显式指定mysql -uroot -p --default-character-setutf8mb4 ecommerce tb_category.sql这个细节看着小但一旦数据灌进去了再返工清洗脚本要重跑一遍浪费半个小时都是少的。5.2 数据量太大导致导入中断类目表可能几千行属性值表则是几十万行起步。如果一次性执行一个巨大的 SQL 文件很容易卡死或报错。解决思路有三种脚本里批量 INSERT每条 500 行左右避免单条 SQL 过长。用mysql客户端的source命令取代在 Navicat 里复制粘贴执行。分文件导入比如类目一个文件、属性一个文件、属性值一个文件哪个环节失败就重跑哪个。另外别忘了调大max_allowed_packet否则稍微大一点的批量 INSERT 直接报 “packet too large”。5.3 属性表数据重复重复来源主要是源数据里同一个属性在多个类目下重复出现且属性 ID 一样。此时如果唯一索引没建好很容易插两遍。解决方式唯一索引必须落在(cat_id, attr_id)这样即使源数据重复MySQL 也会静默拒绝。用INSERT IGNORE代替普通INSERT可以明确跳过已有数据。如果是数据修复场景用临时表导入新数据后再UPDATE JOIN更新正式表。5.4 如何做增量更新类目和属性不是一成不变的淘宝每年都会调整几次。增量更新方案建议用“全量比对 增量写入”把最新采集的数据导到临时表。用LEFT JOIN找出正式表不存在的 cat_id / attr_id / value_id作为新增写入。对于已存在的记录只更新cat_name、attr_name、sort_order等可变字段。这样既保证了数据最新又不会误删业务侧已经配置的扩展数据。5.5 排查 SQL 慢查询的定位思路如果你发现按属性筛选商品时很慢先不要急着加索引用EXPLAIN看一下执行计划EXPLAIN SELECT * FROM tb_category_attr_value WHERE attr_id 122216001;正常情况下应该走uk_attr_value唯一索引Extra 列出现Using index condition说明索引生效。如果出现Using where; Using filesort就要考虑是否排序字段没有索引或者类型转换导致索引失效。属性值表的value_name如果需要模糊查询建议加普通索引但如果查询模式是LIKE %S%普通索引也帮不上忙这种场景就该考虑接入 Elasticsearch 或其他的搜索组件了。这套类目加属性的 SQL 设计我在两个电商项目里完整用过一遍。坦白说第一次做的时候只在类目表上建了唯一索引属性值表漏了(attr_id, value_id)联合唯一索引结果就是同样的数据出现在多个类目下后续做商品发布筛选项时总是多出重复值。后来重新清洗数据、补上索引才逐步稳定下来。所以如果你准备按这个方案落地强烈建议在建表阶段就把唯一索引和字符集都定好后面会省掉大量返工的麻烦。如果你手头的数据量不大也可以先用这套表结构顶着后续等采集源稳定了再做增量同步。总之类目数据这种“看起来简单、做起来繁琐”的东西最重要的就是从一开始就得把结构设计对尤其是索引和层级关系这两块千万别图省事塞 JSON 了。本文还有配套的精品资源点击获取
