简介：一份包含淘宝全量类目、属性及属性值的SQL数据文件，主要面向电商后台开发、数据分析以及数据库学习者，可用于还原淘宝类目树结构、梳理属性与属性值的枚举关系，也为商品筛选、竞品分析或推荐系统原型提供真实数据支撑。压缩包内仅一个SQL文件，大小约353KB，内含建表语句和INSERT数据记录，可直接导入MySQL等关系型数据库，通过标准SQL查询不同层级类目、属性名称及可选项。该资源当前已有294人学习，适合作为理解电商SPU/SKU属性体系的参考数据集。借助这份SQL，开发者能快速搭建类目-属性模型，验证多级分类与属性筛选逻辑，分析者也能针对具体类目做数据统计、市场洞察或清洗映射。文件虽小，但字段覆盖较全，可观察一级到多级类目如何关联属性，属性值又如何约束商品前端展示，对需要真实电商类目数据进行算法测试与功能验证的场景较为实用。
1. 全类目加属性SQL：电商数据里最难啃的那张宽表
接过电商数据需求的开发者，多半听过「全类目加属性」这个说法。手机类目要有「电池容量」「屏幕尺寸」，女装类目要有「袖长」「版型」，零食类目又变成「净含量」「保质期」。不同类目属性千差万别，而业务方往往一句话就要全部结果：「把全类目商品和属性拉成一张宽表，一商品一行，属性放列上」。这句话背后是一整套模型设计加SQL落地的组合拳，翻车率在数据开发需求里常年排前三。
这篇文章要讲的，就是一套在真实电商数据场景里反复打磨过的方案：用三张表把类目属性和商品属性值组织起来，再通过行转列SQL输出宽表。新手可以照着建表语句一步步跑通，熟手能直接拿走窗口函数去重、过滤时机、索引设计这些关键参数。文章不会回避坑——全类目属性数据的坑，一半在模型设计阶段就埋下了，另一半藏在SQL边界条件里。读完你基本能独立交付「全类目加属性SQL」这个需求，也知道哪些环节是玄学，哪些是真正可控的。
2. 先把模型立住：类目、属性、属性值三张表怎么设计
2.1 为什么不能把属性直接塞进商品表
很多第一次做这个需求的同学，第一反应是给商品表加列。手机加「电池容量」，衣服加「袖长」，想着不够再加。这个思路在单一类目下勉强能跑，一旦切到全类目就直接失控——某电商平台全站类目几千个，属性定义累计上万条，全部做成商品表的物理列既不现实也无必要。
业界通行做法是EAV模型（Entity-Attribute-Value，实体-属性-值）。商品是实体，属性定义是元数据，属性值是实际记录。这个模型的优势在于：新增一个类目或属性，只需要往属性定义表里加记录，不需要改表结构；查询时通过行转列把宽表还原出来。代价也明显：行转列的SQL写起来比普通关联查询复杂，且数据量大时聚合开销高。但全类目场景下，EAV是唯一能长期维护的方案。
2.2 三表结构定义与建表SQL
我一般会建三张表：商品主表、类目属性定义表、商品属性值表。商品主表只存SPU级别的基础信息，类目属性定义表存「哪个类目有哪些属性」，商品属性值表存「哪个商品在哪个属性上的值是什么」。下面是一套可直接执行的MySQL建表语句。
-- 商品主表：一个商品一行，SPU粒度 CREATE TABLE spu_info ( spu_id BIGINT NOT NULL COMMENT '商品ID', cat_id INT NOT NULL COMMENT '叶子类目ID', spu_name VARCHAR(256) NOT NULL COMMENT '商品标题', brand_id INT DEFAULT NULL COMMENT '品牌ID', status TINYINT NOT NULL DEFAULT 1 COMMENT '上下架状态 1上架 0下架', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (spu_id), KEY idx_cat_id (cat_id), KEY idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品主表'; -- 类目属性定义表：决定每个类目有哪些属性 CREATE TABLE cat_attr_def ( id INT NOT NULL AUTO_INCREMENT, cat_id INT NOT NULL COMMENT '类目ID', attr_id INT NOT NULL COMMENT '属性ID', attr_name VARCHAR(64) NOT NULL COMMENT '属性名，如电池容量', attr_type TINYINT NOT NULL DEFAULT 1 COMMENT '1单选 2多选 3输入', is_required TINYINT NOT NULL DEFAULT 0 COMMENT '是否必填', sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号', PRIMARY KEY (id), UNIQUE KEY uk_cat_attr (cat_id, attr_id), KEY idx_attr_id (attr_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='类目属性定义表'; -- 商品属性值表：记录每个商品在每个属性上的值 CREATE TABLE spu_attr_value ( id BIGINT NOT NULL AUTO_INCREMENT, spu_id BIGINT NOT NULL COMMENT '商品ID', cat_id INT NOT NULL COMMENT '类目ID', attr_id INT NOT NULL COMMENT '属性ID', attr_value VARCHAR(512) NOT NULL COMMENT '属性值，统一按字符串存', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_spu_attr (spu_id, attr_id), KEY idx_cat_attr_value (cat_id, attr_id, attr_value(32)), KEY idx_update_time (update_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品属性值表';这套表结构里有几个关键设计值得说明。attr_value统一用VARCHAR存，不管原来是数字还是日期，全类目下数据类型太杂，转成字符串最省心，但代价是数字范围查询要强制转换，后面性能章再讲。spu_attr_value表加了uk_spu_attr唯一键，保证一个商品同一个属性只有一条记录，这是行转列不出现重复列的前提。attr_value字段上建了前缀索引，因为属性值长度差异大，前缀索引既能加速等值查询又省空间。
2.3 类目路径设计：用path字段绕开递归查询
三张表之间通过cat_id关联，但全类目查询经常需要「查某个一级类目下的所有商品」。如果只存叶子类目ID，向上找父类目就要递归，SQL写起来非常痛苦。我一般会在商品主表之外单独维护一张类目树表，用path字段记录从根到叶子的全路径。
CREATE TABLE cat_tree ( cat_id INT NOT NULL COMMENT '类目ID', cat_name VARCHAR(64) NOT NULL COMMENT '类目名', parent_id INT NOT NULL DEFAULT 0 COMMENT '父类目ID，0为根', level TINYINT NOT NULL COMMENT '层级，1级为顶级', path VARCHAR(512) NOT NULL COMMENT '从根到当前的全路径,如 1,100,1005', PRIMARY KEY (cat_id), KEY idx_parent (parent_id), KEY idx_path (path(32)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='类目树表';path字段存逗号分隔的祖先ID链，查询「某类目及其所有子类目」直接WHERE path LIKE '1,100,%'就能命中，不用递归。维护成本是每次类目调整要同步更新子节点path，但类目调整频率远低于查询频率，这个代价值得。真正需要小心的场景是类目被迁移到别的父节点下——那一批商品的path要跟着改，后面避坑章节会详细讲。
3. 核心SQL：把行属性转成列宽表
3.1 最稳妥的写法：GROUP_CONCAT加应用层解析
拿到三张表之后，最核心的问题是怎么把属性值从行转成列。MySQL没有内置的PIVOT函数，常见做法有两条路：一条是提前知道所有属性名，写死CASE WHEN列；另一条是GROUP_CONCAT拼接后再由应用层拆解。全类目场景下属性总数随时在变，写死列不现实，我绝大多数时候选GROUP_CONCAT。
SELECT s.spu_id, s.spu_name, s.cat_id, GROUP_CONCAT( CONCAT_WS(':', d.attr_name, v.attr_value) ORDER BY d.sort_no SEPARATOR '||' ) AS attrs FROM spu_info s LEFT JOIN spu_attr_value v ON s.spu_id = v.spu_id AND s.cat_id = v.cat_id LEFT JOIN cat_attr_def d ON v.attr_id = d.attr_id AND v.cat_id = d.cat_id WHERE s.status = 1 GROUP BY s.spu_id, s.spu_name, s.cat_id这条SQL的逻辑是把一个商品的所有属性和值拼成一个长字符串，每个属性对用attr_name:attr_value表示，属性对之间用||分隔。应用层拿到attrs字段后按||拆分，再按冒号拆成键值对，动态渲染到宽表里。
这里用LEFT JOIN而不是INNER JOIN是有原因的：商品可能没有属性值记录，但商品本身要保留在结果集中。执行计划里要留意GROUP_CONCAT的排序是否会走文件排序，数据量大时这可能成为瓶颈。另外GROUP_CONCAT默认长度上限是1024字符，一个属性多的商品（比如手机类目几十个属性）很容易超长被截断，后面避坑章节专门说。
3.2 属性值筛选：先过滤还是后过滤
业务上经常需要按属性值筛商品，比如「电池容量大于4000mAh的手机」。这个需求如果直接写在GROUP_CONCAT外面是做不动的，因为attrs已经是聚合后的字符串。必须把筛选条件下推到聚合之前的子查询里。
SELECT t.spu_id, t.spu_name, t.cat_id, GROUP_CONCAT(CONCAT_WS(':', t.attr_name, t.attr_value) ORDER BY t.sort_no SEPARATOR '||') AS attrs FROM ( SELECT s.spu_id, s.spu_name, s.cat_id, d.attr_name, v.attr_value, d.sort_no FROM spu_info s JOIN spu_attr_value v ON s.spu_id = v.spu_id AND s.cat_id = v.cat_id JOIN cat_attr_def d ON v.attr_id = d.attr_id AND v.cat_id = d.cat_id JOIN cat_tree c ON s.cat_id = c.cat_id WHERE c.path LIKE '1,100,%' AND d.attr_name = '电池容量' AND CAST(v.attr_value AS UNSIGNED) >= 4000 AND s.status = 1 ) t GROUP BY t.spu_id, t.spu_name, t.cat_id注意这个子查询里已经做了三层JOIN，过滤条件全部放在最内层。这样做的收益是：聚合前已经把数据压到最小集，GROUP_CONCAT处理的记录数大幅下降，整体查询耗时能压到先聚合再过滤方案的十分之一甚至更低。代价是子查询里没法直接用外层表的索引关联，但实际跑下来，数据量千万级以内时这个写法的性能完全可接受。
这里还涉及一个取舍：属性值按字符串存，CAST(v.attr_value AS UNSIGNED)在数据量大时会全表扫描吗？答案是看执行计划。如果过滤条件卡在最内层且走的是联合索引，MySQL会在索引层面先缩小范围再CAST，不会全表扫。但如果你的查询形态经常变，比如今天筛电池容量明天筛重量，索引覆盖不了所有属性，那就接受慢查询，把它放到离线跑批里执行。
3.3 保留最新属性：窗口函数ROW_NUMBER让旧值退役
同一商品同一属性理论上只该有一条记录，唯一键uk_spu_attr已经兜底。但数据同步链路里经常出现重复：采集任务重复推数、历史数据修正产生新版本。这类脏数据会让GROUP_CONCAT结果里出现两个同名字段，宽表解析时后者覆盖前者，结果不确定。
我的习惯是写一个去重子查询，用窗口函数ROW_NUMBER按更新时间取最新一条。这也是热词「sql窗口函数」在数据场景里最典型的一次落地。
SELECT spu_id, cat_id, attr_id, attr_value FROM ( SELECT v.spu_id, v.cat_id, v.attr_id, v.attr_value, ROW_NUMBER() OVER ( PARTITION BY v.spu_id, v.attr_id ORDER BY v.update_time DESC ) AS rn FROM spu_attr_value v ) t WHERE t.rn = 1PARTITION BY v.spu_id, v.attr_id的含义是「同一商品同一属性的一组记录」，ORDER BY update_time DESC让最新更新的排第一，rn=1就是最新版本。这个子查询放在前面两节SQL的JOIN里，能保证进入聚合的数据天然去重。性能上要注意：窗口函数会把分组数据落盘排序，千万级数据量下要确保spu_attr_value表按update_time有索引，否则临时文件会拖垮整个查询。
4. 性能瓶颈与优化：千万级属性值表的慢SQL排查
4.1 执行计划盯这两列：type和rows
全类目属性宽表查询慢，九成问题出在spu_attr_value表上。这张表是典型的「数据多、单行短」结构，千万行起步，联合索引用不对就直接全表扫。我在排查尴尬慢查询时的固定动作是先跑一遍EXPLAIN，重点看type和rows两列。
EXPLAIN SELECT s.spu_id, v.attr_id, v.attr_value FROM spu_info s JOIN spu_attr_value v ON s.spu_id = v.spu_id AND s.cat_id = v.cat_id WHERE s.cat_id = 1005执行计划返回里，type字段如果出现ALL，说明spu_attr_value被全表扫描，这是最坏的情况。正常期望是ref或者eq_ref，代表走的是非唯一索引或主键关联。rows字段是预估扫描行数，如果预估值远大于实际返回行数，大概率索引设计有问题。
我见过不少同事在这一步翻车：建了idx_cat_attr_value(cat_id, attr_id, attr_value)联合索引，就觉得万事大吉。实际上这个索引的最左前缀是cat_id，如果业务查询永远只传attr_id不带cat_id，索引根本用不上。看着是联合索引，实际退化成全表扫描，这类问题用EXPLAIN一眼就能看出来。
4.2 联合索引设计顺序有讲究：等值在前，范围在后
给spu_attr_value设计索引时，核心原则是「等值条件放前面，范围条件放后面」。全类目查询最常见的形态是：限定一个类目，然后对某个属性做等值或范围过滤。对应到联合索引，就该把cat_id放第一位，attr_id放第二位。
ALTER TABLE spu_attr_value ADD INDEX idx_cat_attr_v (cat_id, attr_id, attr_value(32)); ALTER TABLE spu_attr_value ADD INDEX idx_spu_updated (spu_id, update_time);第一条索引服务「按类目查属性值」的典型场景，attr_value用前缀索引是为了控制索引体积——VARCHAR(512)字段全量进索引会让索引页分裂严重，实测前缀32字符对等值查询的准确度几乎无损。第二条索引是给窗口函数去重场景用的：窗口函数PARTITION BY spu_id ORDER BY update_time，这个索引能让排序直接走索引有序性，避免filesort临时表。
索引不是越多越好，每多一个索引写放大的成本都在涨。实际业务里保持三四条关键索引就够，频繁的线上导入会教你怎么做减法——写慢比读慢更难救。
4.3 分批拉取与并行SQL优化：别让一条SQL吃掉整个资源池
全类目属性宽表如果一次性全量生成，数据量上千万时单条SQL跑半小时很正常，还会把线上从库的IO打满。我一般会按类目维度拆分任务：每个一级类目单独跑一批，批次间串行或控制并发数。这样某个类目SQL写坏了只会影响那一个批次，不会拖垮整个调度。
并行SQL优化的要点是两个：一是保证每个子任务的数据范围不重叠，二是控制同时运行的子任务数量。数据范围不重叠靠类目path前缀实现，比如WHERE c.path LIKE '1,100,%'和WHERE c.path LIKE '1,200,%'天然隔离。并发数控制在数据库连接池大小的一半以下，留出余量给其他业务查询。
还有一类慢是「慢」在客户端解析上。GROUP_CONCAT拼出来的长字符串，网络传输和内存占用都不是小数目。我做全量导出的习惯是：宽表结果落到临时表，再由数据同步工具拉取，而不是让查询结果直接经过应用层。这样即便某批次失败，重跑也只重跑那一个类目，不用全量再来。
5. 避坑指南：做全类目属性SQL最常见的6个坑
5.1 SPU和SKU粒度混淆，属性值张冠李戴
现象：同一商品下不同规格（比如手机的不同颜色版本）属性值互相覆盖，品牌属性串到了颜色属性上。原因：属性值表建在SPU粒度，但采集端某些类目上报的是SKU粒度数据。两个SKU同属一个SPU时，后写入的覆盖先写入的。解决：在采集端做粒度归一化，明确统一的规则：全类目一律按SPU粒度入表；如果业务必须要SKU粒度属性，单独建SKU属性表，不跟SPU属性混在一张表里。表设计时就定死粒度，比事后清洗省一百倍力气。
5.2 类目调整后属性残留，历史数据全部错位
现象：类目A被并入类目B后，商品还挂在旧类目上，新增属性查询出来全是空。原因：商品主表的cat_id是快照字段，类目调整时没有回刷历史商品的cat_id，导致JOIN类目属性定义表时匹配不上。解决：类目树调整时写一个回刷任务，把受影响类目下的商品cat_id更新到新类目，同时同步更新spu_attr_value里的cat_id。这个回刷脚本必须和类目调整在同一次发布里执行，否则线上数据就断档了。
5.3 LEFT JOIN后属性凭空消失，NULL值被过滤掉
现象：商品明明有属性值，宽表里对应列却是空的。原因：宽表解析时用if(attr_value is null, '', attr_value)兜底了，但行转列之前某个中间层用了INNER JOIN，把没有属性值的商品整行过滤了。GROUP_CONCAT也一样，聚合函数会忽略NULL，但null值字段拼出来的键值对本身就丢了。解决：明确全流程的JOIN语义。展示类查询统一LEFT JOIN，过滤类查询才用INNER JOIN。属性值为空不代表商品不存在，这个边界要和业务方对齐清楚。
5.4 动态SQL拼接属性名，注入漏洞趁虚而入
现象：用程序动态拼属性列名时，传入attr_name=1 or 1=1，结果返回了全量数据。原因：属性名直接当成标识符拼接进SQL，没做白名单校验。这类注入不是通过参数值进来的，而是通过列名进来的，预处理语句的参数化机制管不到标识符位置。解决：动态列名必须走白名单。从cat_attr_def表里用WHERE attr_name = ?先查出合法attr_id，再拿着attr_id去拼SQL。永远不要直接信任外部传入的属性名字符串，这是动态SQL场景里最容易踩的注入坑。
5.5 GROUP_CONCAT长度截断，属性多时静默丢失
现象：手机类目商品宽表里明显少了好几个属性，检查源数据却都在。原因：group_concat_max_len默认1024字节，一个商品几十个属性动辄超过这个长度，超出的部分被MySQL静默截断，没有任何报错。解决：执行前先调大会话级参数。离线跑批场景建议设到1MB以上，注意这个参数是会话级别的，每次连接都要设置，最好在连接初始化时统一配置。另外把SEPARATOR从逗号换成不常见字符（比如||），也是防止属性值本身含逗号导致解析错位的常用手段。
5.6 join字段字符集不一致，索引莫名失效
现象：同一条SQL在测试环境秒出，到生产环境执行计划走了ALL全表扫描。原因：两张表的关联字段字符集不一致，一张utf8一张utf8mb4。MySQL会在关联时做隐式转换，导致索引失效。这类问题最坑的是EXPLAIN里不会报错，只显示type=ALL，容易被误判为数据量差异。解决：建表时统一字符集，所有关联字段统一用utf8mb4。老表改造时用ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4，改完记得重新ANALYZE TABLE刷新统计信息。排查时可以用SHOW FULL COLUMNS FROM 表名快速核对字符集差异。
6. 进阶玩法：用存储过程自动生成类目属性宽表
6.1 动态列SQL的生成思路
前面的GROUP_CONCAT方案是通用的，但性能上有一个天然短板：结果集里所有属性都挤在一个字符串里，下游使用还是得拆。如果业务方明确要求「一商品一行，每个属性一个物理列」，那就得走动态SQL：先从cat_attr_def表查出指定类目的所有属性名，再拼出带CASE WHEN的查询语句，最后执行。
这个思路的可行前提是属性名可控且数量有限。单个类目的属性一般几十个，拼出来的SQL不会超过数据库对SQL长度的限制。反例是全类目一次性拼几千列，那种场景应该回到底层宽表或物化视图，而不是SQL硬拼。
6.2 一个可控的模板存储过程
我一般会把动态列逻辑封装成存储过程，输入类目ID，输出该类的宽表到一张结果表。下面是一个可直接改用的模板，请务必注意白名单校验部分。
DELIMITER $$ CREATE PROCEDURE build_cat_wide_table(IN v_cat_id INT) BEGIN DECLARE v_cols TEXT DEFAULT ''; DECLARE v_sql TEXT DEFAULT ''; DECLARE v_cnt INT DEFAULT 0; -- 从属性定义表取列名，白名单就来自这张表 SELECT GROUP_CONCAT( CONCAT( 'MAX(CASE WHEN v.attr_id = ', attr_id, ' THEN v.attr_value END) AS `', attr_name, '`' ) SEPARATOR ',' ), COUNT(*) INTO v_cols, v_cnt FROM cat_attr_def WHERE cat_id = v_cat_id; IF v_cnt > 0 THEN SET v_sql = CONCAT( 'CREATE TABLE tmp_wide_cat_', v_cat_id, ' AS SELECT ', 's.spu_id, s.spu_name, ', v_cols, ' FROM spu_info s ', 'LEFT JOIN spu_attr_value v ON s.spu_id = v.spu_id AND s.cat_id = v.cat_id ', 'WHERE s.cat_id = ', v_cat_id, ' GROUP BY s.spu_id, s.spu_name' ); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END$$ DELIMITER ;这段存储过程的核心逻辑是先查属性定义表拿到合法的attr_id和attr_name，再拼CASE WHEN聚合模板。MAX(CASE WHEN v.attr_id = ? THEN v.attr_value END)是行转列的经典写法，配合GROUP BY s.spu_id，每个属性只保留一行值。因为spu_attr_value表上已有uk_spu_attr唯一键，每个商品每个属性最多一条记录，MAX不会误吞多值。
调用示例：CALL build_cat_wide_table(1005);。结果表生成后，可以用普通SELECT直接查看，也可以作为临时表继续和其他表JOIN。这个方案的收益是下游使用极简单——不需要做任何字符串解析，列名直接是属性名，数据分析的同学拿过去就能用。
6.3 验证与结果检查
动态SQL拼出来的宽表，最怕静默出错。存储过程跑完不是结束，我给自己定的规矩是跑完立刻做三类检查。
第一是行数对比：SELECT COUNT(*) FROM tmp_wide_cat_1005要和spu_info表里该类目在售商品数对得上。第二是属性列抽查：随便挑几个常用属性列，统计非空比例是否符合预期，比如手机类目「电池容量」非空率应该很高，如果突然大面积为空，说明属性值表的数据链路出了问题。第三是抽样人工核对：挑一个商品，把它在宽表里的属性和值，和原spu_attr_value表里一条条对比，确认没有错位。
这套检查看起来笨，但实际执行成本很低，而且特别能发现「看起来对、实际上错」的情况。尤其是类目调整后首跑，历史数据回刷有没有成功，靠的就是列非空率这一步。
这是我做全类目加属性SQL以来沉淀下来的最大教训：大多数坑不是SQL写错，而是数据链路和模型设计的问题，等SQL跑通再回头填坑，代价往往是双份。所以现在每到一个新环境，我第一件事永远是先确认三张表的粒度、字符集和唯一键，然后才动SELECT。这套流程救了我很多次，希望帮到你。
本文还有配套的精品资源，点击获取
