简介一份包含淘宝全量类目、属性及属性值的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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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 父类目ID0为根, 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)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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让最新更新的排第一rn1就是最新版本。这个子查询放在前面两节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_name1 or 11结果返回了全量数据。原因属性名直接当成标识符拼接进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里不会报错只显示typeALL容易被误判为数据量差异。解决建表时统一字符集所有关联字段统一用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。这套流程救了我很多次希望帮到你。本文还有配套的精品资源点击获取