电商系统商品多规格数据库设计：告别字段扩展噩梦的优雅方案

前言

在电商系统开发中，商品规格管理一直是一个让人头疼的问题。传统的做法是在SKU表中为每个规格创建单独的字段，比如 color、size、material 等。这种设计看似简单直观，但随着业务发展，问题逐渐暴露：

• 扩展性差：每增加一个新规格，就要修改表结构，增加字段
• 字段冗余：不同商品使用的规格不同，导致大量字段为空
• 维护困难：表结构频繁变更，影响线上业务稳定性
• 查询复杂：动态规格查询需要大量条件判断

本文将介绍一种基于 EAV（Entity-Attribute-Value）模式变体 的灵活设计方案，彻底解决规格扩展问题，让你的电商系统可以轻松支持任意数量、任意类型的商品规格。

设计思路

核心理念

将 规格定义 与 SKU数据 解耦，通过关联表建立多对多关系，实现：

1. ✅ 无需修改表结构即可添加新规格
2. ✅ 每个商品可以使用不同的规格组合
3. ✅ 灵活查询任意规格组合的SKU
4. ✅ 规格值可复用，便于统计和搜索

数据模型架构

整个设计由 6张核心表 组成，形成一个完整的规格管理体系：

产品基础信息
    ↓
商品 ←→ 商品规格关联 ←→ 规格名称 ←→ 规格值
    ↓                                    ↓
   SKU ←────────── SKU规格值关联 ─────────┘

数据库表设计详解

1. 商品基础表 (product)

存储商品的基本信息，与具体规格无关。

CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
    product_code VARCHAR(100) UNIQUE COMMENT '商品编码',
    category_id BIGINT COMMENT '分类ID',
    brand_id BIGINT COMMENT '品牌ID',
    description TEXT COMMENT '商品描述',
    status TINYINT DEFAULT 1 COMMENT '状态:1-上架,0-下架',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品基础表';

设计要点：

• 只存储商品公共属性
• 不包含任何规格相关字段
• 支持商品分类、品牌等扩展

2. 规格名称表 (spec_name)

定义规格的类型，如：颜色、尺寸、材质、容量、版本等。

CREATE TABLE spec_name (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    spec_name VARCHAR(50) NOT NULL COMMENT '规格名称',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_spec_name (spec_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规格名称表';

设计要点：

• 全局共享，所有商品都可以使用
• 支持排序，控制前端展示顺序
• 支持启用/禁用状态管理

示例数据：

INSERT INTO spec_name (spec_name, sort_order) VALUES 
('颜色', 1),
('尺寸', 2),
('材质', 3),
('容量', 4),
('内存', 5),
('版本', 6);

3. 规格值表 (spec_value)

存储每个规格下的具体值。

CREATE TABLE spec_value (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    spec_name_id BIGINT NOT NULL COMMENT '规格名称ID',
    spec_value VARCHAR(100) NOT NULL COMMENT '规格值',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (spec_name_id) REFERENCES spec_name(id),
    UNIQUE KEY uk_spec_name_value (spec_name_id, spec_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规格值表';

设计要点：

• 通过外键关联规格名称
• 同一规格下的值不能重复
• 支持独立的排序和状态控制

示例数据：

-- 颜色规格值
INSERT INTO spec_value (spec_name_id, spec_value) VALUES 
(1, '红色'), (1, '蓝色'), (1, '黑色'), (1, '白色');

-- 尺寸规格值
INSERT INTO spec_value (spec_name_id, spec_value) VALUES 
(2, 'S'), (2, 'M'), (2, 'L'), (2, 'XL');

-- 容量规格值
INSERT INTO spec_value (spec_name_id, spec_value) VALUES 
(4, '128GB'), (4, '256GB'), (4, '512GB');

4. 商品规格关联表 (product_spec)

定义每个商品使用哪些规格类型。

CREATE TABLE product_spec (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL COMMENT '商品ID',
    spec_name_id BIGINT NOT NULL COMMENT '规格名称ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE,
    FOREIGN KEY (spec_name_id) REFERENCES spec_name(id),
    UNIQUE KEY uk_product_spec (product_id, spec_name_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格关联表';

设计要点：

• 多对多关系：一个商品可以有多个规格，一个规格可以被多个商品使用
• 级联删除：商品删除时自动清理关联数据
• 唯一约束：防止重复关联

应用场景：

-- T恤使用：颜色 + 尺寸
INSERT INTO product_spec (product_id, spec_name_id) VALUES 
(1, 1), (1, 2);

-- 手机使用：颜色 + 容量 + 内存
INSERT INTO product_spec (product_id, spec_name_id) VALUES 
(2, 1), (2, 4), (2, 5);

-- 水杯使用：颜色 + 容量 + 材质
INSERT INTO product_spec (product_id, spec_name_id) VALUES 
(3, 1), (3, 4), (3, 3);

5. SKU表 (product_sku)

存储每个具体的库存单位（SKU）信息。

CREATE TABLE product_sku (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL COMMENT '商品ID',
    sku_code VARCHAR(100) UNIQUE NOT NULL COMMENT 'SKU编码',
    sku_name VARCHAR(255) COMMENT 'SKU名称',
    price DECIMAL(10,2) NOT NULL COMMENT '价格',
    original_price DECIMAL(10,2) COMMENT '原价',
    cost_price DECIMAL(10,2) COMMENT '成本价',
    stock INT DEFAULT 0 COMMENT '库存',
    warning_stock INT DEFAULT 0 COMMENT '预警库存',
    sales_count INT DEFAULT 0 COMMENT '销量',
    image_url VARCHAR(500) COMMENT 'SKU图片',
    barcode VARCHAR(100) COMMENT '条形码',
    weight DECIMAL(10,2) COMMENT '重量(kg)',
    volume DECIMAL(10,2) COMMENT '体积(m³)',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SKU表';

设计要点：

• 包含完整的价格、库存、销量等业务字段
• SKU编码全局唯一，便于溯源管理
• 支持SKU级别的图片、条码等信息

6. SKU规格值关联表 (sku_spec_value) ⭐核心

这是整个设计的核心表，记录每个SKU对应的具体规格值组合。

CREATE TABLE sku_spec_value (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    spec_name_id BIGINT NOT NULL COMMENT '规格名称ID',
    spec_value_id BIGINT NOT NULL COMMENT '规格值ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sku_id) REFERENCES product_sku(id) ON DELETE CASCADE,
    FOREIGN KEY (spec_name_id) REFERENCES spec_name(id),
    FOREIGN KEY (spec_value_id) REFERENCES spec_value(id),
    UNIQUE KEY uk_sku_spec (sku_id, spec_name_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SKU规格值关联表';

设计要点：

• 一个SKU对应多条记录，每条记录代表一个规格维度
• 通过 spec_name_id 和 spec_value_id 确定具体的规格值
• 唯一约束确保一个SKU在同一规格维度只有一个值

数据示例：

-- SKU: 红色-M码的T恤
INSERT INTO sku_spec_value (sku_id, spec_name_id, spec_value_id) VALUES 
(1, 1, 1),  -- 规格：颜色 = 红色
(1, 2, 2);  -- 规格：尺寸 = M

-- SKU: 黑色-256GB的手机
INSERT INTO sku_spec_value (sku_id, spec_name_id, spec_value_id) VALUES 
(2, 1, 3),  -- 规格：颜色 = 黑色
(2, 4, 14); -- 规格：容量 = 256GB

实战案例演示

案例1：服装类商品（T恤）

需求：T恤有颜色和尺寸两个规格

-- 1. 创建商品
INSERT INTO product (product_name, product_code) VALUES 
('经典纯棉T恤', 'PROD001');

-- 2. 设置商品使用的规格
INSERT INTO product_spec (product_id, spec_name_id) VALUES 
(1, 1),  -- 使用颜色规格
(1, 2);  -- 使用尺寸规格

-- 3. 创建SKU（红色-M码）
INSERT INTO product_sku (product_id, sku_code, sku_name, price, stock) VALUES 
(1, 'SKU001-RED-M', '红色-M', 99.00, 100);

-- 4. 关联SKU的规格值
INSERT INTO sku_spec_value (sku_id, spec_name_id, spec_value_id) VALUES 
(1, 1, 1),  -- 颜色：红色
(1, 2, 2);  -- 尺寸：M

案例2：电子产品（手机）

需求：手机有颜色和容量两个规格

-- 1. 创建商品
INSERT INTO product (product_name, product_code) VALUES 
('智能手机 Pro Max', 'PROD002');

-- 2. 设置商品使用的规格
INSERT INTO product_spec (product_id, spec_name_id) VALUES 
(2, 1),  -- 使用颜色规格
(2, 4);  -- 使用容量规格

-- 3. 创建SKU（黑色-256GB）
INSERT INTO product_sku (product_id, sku_code, sku_name, price, stock) VALUES 
(2, 'SKU002-BLACK-256GB', '黑色-256GB', 6999.00, 30);

-- 4. 关联SKU的规格值
INSERT INTO sku_spec_value (sku_id, spec_name_id, spec_value_id) VALUES 
(2, 1, 3),   -- 颜色：黑色
(2, 4, 14);  -- 容量：256GB

案例3：新增规格（材质）

需求：后续要为商品增加"材质"规格，无需修改表结构！

-- 1. 添加新规格类型（一次性操作）
INSERT INTO spec_name (spec_name, sort_order) VALUES ('材质', 3);

-- 2. 添加规格值
INSERT INTO spec_value (spec_name_id, spec_value) VALUES 
(3, '纯棉'), (3, '涤纶'), (3, '混纺');

-- 3. 为已有商品启用材质规格
INSERT INTO product_spec (product_id, spec_name_id) VALUES (1, 3);

-- 4. 为已有SKU添加材质属性
INSERT INTO sku_spec_value (sku_id, spec_name_id, spec_value_id) VALUES 
(1, 3, 9);  -- 添加材质：纯棉

完全不需要修改任何表结构！ 🎉

常用SQL查询

1. 查询商品的所有SKU及规格组合

用于商品详情页展示所有可选SKU。

SELECT 
    ps.id,
    ps.sku_code,
    ps.sku_name,
    ps.price,
    ps.stock,
    GROUP_CONCAT(
        CONCAT(sn.spec_name, ':', sv.spec_value) 
        ORDER BY sn.sort_order 
        SEPARATOR ', '
    ) as specs
FROM product_sku ps
LEFT JOIN sku_spec_value ssv ON ps.id = ssv.sku_id
LEFT JOIN spec_name sn ON ssv.spec_name_id = sn.id
LEFT JOIN spec_value sv ON ssv.spec_value_id = sv.id
WHERE ps.product_id = 1
GROUP BY ps.id;

输出示例：

id | sku_code       | price | stock | specs
---|----------------|-------|-------|------------------
1  | SKU001-RED-M   | 99.00 | 100   | 颜色:红色, 尺寸:M
2  | SKU001-RED-L   | 99.00 | 150   | 颜色:红色, 尺寸:L
3  | SKU001-BLUE-M  | 99.00 | 80    | 颜色:蓝色, 尺寸:M

2. 获取商品的规格选择器数据

用于前端渲染规格选择组件（如颜色、尺寸选择器）。

SELECT 
    sn.id as spec_name_id,
    sn.spec_name,
    sn.sort_order,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', sv.id,
            'value', sv.spec_value,
            'sort_order', sv.sort_order
        ) ORDER BY sv.sort_order
    ) as spec_values
FROM product_spec ps
JOIN spec_name sn ON ps.spec_name_id = sn.id
JOIN spec_value sv ON sv.spec_name_id = sn.id
WHERE ps.product_id = 1 AND sv.status = 1
GROUP BY sn.id
ORDER BY sn.sort_order;

输出示例（JSON格式）：

[
  {
    "spec_name_id": 1,
    "spec_name": "颜色",
    "spec_values": [
      {"id": 1, "value": "红色"},
      {"id": 2, "value": "蓝色"},
      {"id": 3, "value": "黑色"}
    ]
  },
  {
    "spec_name_id": 2,
    "spec_name": "尺寸",
    "spec_values": [
      {"id": 5, "value": "S"},
      {"id": 6, "value": "M"},
      {"id": 7, "value": "L"}
    ]
  }
]

3. 根据用户选择的规格查找SKU

用户在前端选择规格后，查找对应的SKU和价格。

-- 查找：商品1中，颜色=红色(id=1) 且 尺寸=M(id=2) 的SKU
SELECT ps.*
FROM product_sku ps
WHERE ps.product_id = 1
  AND ps.id IN (SELECT sku_id FROM sku_spec_value WHERE spec_value_id = 1)
  AND ps.id IN (SELECT sku_id FROM sku_spec_value WHERE spec_value_id = 2);

适用场景：

• 用户点击规格后实时更新价格
• 判断当前规格组合是否有货
• 加入购物车时确定具体SKU

4. 获取SKU完整信息（含规格详情）

用于订单详情、购物车等场景。

SELECT 
    ps.*,
    p.product_name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'spec_name', sn.spec_name,
            'spec_value', sv.spec_value
        )
    ) as spec_details
FROM product_sku ps
JOIN product p ON ps.product_id = p.id
LEFT JOIN sku_spec_value ssv ON ps.id = ssv.sku_id
LEFT JOIN spec_name sn ON ssv.spec_name_id = sn.id
LEFT JOIN spec_value sv ON ssv.spec_value_id = sv.id
WHERE ps.id = 1
GROUP BY ps.id;

5. 查询某个规格值下的所有SKU

用于商品筛选、搜索功能。

-- 查询所有红色的商品SKU
SELECT DISTINCT ps.*
FROM product_sku ps
JOIN sku_spec_value ssv ON ps.id = ssv.sku_id
WHERE ssv.spec_value_id = 1;  -- 红色

方案优势分析

✅ 优势

1. 极强的扩展性

   • 增加新规格无需修改表结构
   • 每个商品可以有独特的规格组合
   • 支持无限规格维度

2. 数据一致性好

   • 规格值统一管理，避免数据不一致
   • 外键约束保证数据完整性
   • 便于规格值的统一修改和维护

3. 查询灵活

   • 支持任意规格组合查询
   • 可以按规格维度进行统计分析
   • 便于实现规格筛选功能

4. 存储效率高

   • 无冗余字段，按需存储
   • 规格值复用，减少存储空间
   • 索引设计合理，查询性能好

⚠️ 注意事项

1. 查询复杂度

   • 需要多表JOIN，相比单表查询稍复杂
   • 建议使用缓存优化热点数据
   • 对高频查询建立合适的索引

2. 数据冗余

   • 可以在SKU表增加 spec_json 字段缓存规格信息
   • 通过触发器或应用层保持数据同步

3. 性能优化

   -- 为高频查询添加索引
   CREATE INDEX idx_sku_product ON product_sku(product_id, status);
   CREATE INDEX idx_spec_value_name ON spec_value(spec_name_id, status);
   CREATE INDEX idx_sku_spec_value ON sku_spec_value(spec_value_id);
   

进阶优化建议

1. 增加规格值图片

在 spec_value 表中添加图片字段，支持颜色色块、材质图片等。

ALTER TABLE spec_value ADD COLUMN image_url VARCHAR(500) COMMENT '规格值图片';

2. SKU规格快照

在 product_sku 表增加JSON字段缓存规格信息，提升查询性能。

ALTER TABLE product_sku 
ADD COLUMN spec_snapshot JSON COMMENT '规格快照';

-- 示例数据
UPDATE product_sku 
SET spec_snapshot = '[{"颜色":"红色"},{"尺寸":"M"}]'
WHERE id = 1;

3. 规格模板

创建规格模板表，快速应用常用规格组合。

CREATE TABLE spec_template (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(100) NOT NULL COMMENT '模板名称',
    category_id BIGINT COMMENT '适用分类'
);

CREATE TABLE spec_template_detail (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    template_id BIGINT NOT NULL,
    spec_name_id BIGINT NOT NULL
);

4. 规格值排序优化

支持规格值在不同商品中的个性化排序。

CREATE TABLE product_spec_value_sort (
    product_id BIGINT,
    spec_value_id BIGINT,
    sort_order INT,
    PRIMARY KEY (product_id, spec_value_id)
);

实际应用场景

这套设计方案适用于：

• ✅ 电商平台：淘宝、京东等多品类商品管理
• ✅ SaaS商城系统：支持多租户的灵活规格配置
• ✅ 供应链系统：复杂SKU管理和库存追踪
• ✅ O2O平台：商品规格与门店库存关联
• ✅ 跨境电商：多语言规格值管理

总结

本文介绍的商品多规格数据库设计方案，通过将规格定义与SKU数据解耦，实现了高度灵活的规格管理能力。核心思想是：

不要为了数据而设计字段，而是为了关系而设计表。

这套方案已在多个大型电商项目中得到验证，能够很好地平衡扩展性、性能和维护成本。希望能帮助你摆脱传统设计的束缚，构建更加优雅、可扩展的电商系统！

---

完整SQL代码

完整的建表语句和示例数据已包含在本文配套的 SQL 文件中，可以直接导入测试使用。

欢迎关注我，持续分享系统架构设计、数据库优化等干货内容！ 🚀

如果这篇文章对你有帮助，请点赞、收藏、转发支持一下~ 💪