王明昌博客在电子商城项目开发中, 会存在同一种商品存在不同规格的情况, 如图某型号手机在售卖时用户可以选择的不同规格:
这里商品的库存量单位(SKU)便不再是该商品, 而是到具体属性组合出的规格, 每种规格可能会有不同的售价、运费与库存剩余情况, 所以用户在购买时, 不仅需要记录所购买的商品 ID, 同时也需要记录购买的该商品的具体规格。
直观分析图示中的规格情况, 网络类型、机身颜色、机身内存 属于商品不同属性的名称, 与之对应的为属性可选择的的具体值, 属于一对多关系, 在 MySQL 数据库表结构中反应出为:
|
1 |
<span class="com"># 商品属性名</span><span class="pln"> CREATE TABLE </span><span class="str">`item_attr_key`</span> <span class="pun">(</span> <span class="str">`attr_key_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NOT NULL AUTO_INCREMENT</span><span class="pun">,</span> <span class="com"># 主键, 自增 ID</span> <span class="str">`item_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NOT NULL</span><span class="pun">,</span> <span class="com"># 关联到商品</span> <span class="str">`attr_name`</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span> <span class="com"># 属性名称</span><span class="pln"> PRIMARY KEY </span><span class="pun">(</span><span class="str">`attr_key_id`</span><span class="pun">)</span> <span class="pun">);</span> <span class="com"># 商品属性值</span><span class="pln"> CREATE TABLE </span><span class="str">`item_attr_val`</span> <span class="pun">(</span> <span class="str">`attr_key_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NULL DEFAULT NULL</span><span class="pun">,</span> <span class="com"># 对应 item_attr_key 表的 attr_key_id, 完成一对多关联</span> <span class="str">`item_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NULL DEFAULT NULL</span><span class="pun">,</span> <span class="com"># 关联到商品</span> <span class="str">`symbol`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL DEFAULT NULL</span><span class="pun">,</span> <span class="com"># 属性编码</span> <span class="str">`attr_value`</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">255</span><span class="pun">)</span><span class="pln"> NULL DEFAULT NULL </span><span class="com"># 属性值</span> <span class="pun">);</span> |
加入数据后表内容如图:
symbol 字段是对指定商品 ID 下的属性值的一个序号标记, 是为了提高在后面使用到时的检索效率。该值在不同商品间可以重复, 在同一商品的属性中需要保证唯一。
以上就完成了商品 ID 为 6 的商品多属性的存储工作。
为了能够记录和快速查询出每种属性组合出的商品的价格、库存等信息, 我们还需要张表来维护这部分数据, 建立 item_sku 表:
|
1 |
<span class="com"># 商品库存量单位表</span><span class="pln"> CREATE TABLE </span><span class="str">`item_sku`</span> <span class="pun">(</span> <span class="str">`sku_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NOT NULL AUTO_INCREMENT</span><span class="pun">,</span> <span class="com"># 主键, 自增 ID</span> <span class="str">`item_id`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NOT NULL DEFAULT </span><span class="str">'0'</span><span class="pun">,</span> <span class="com"># 关联到商品</span> <span class="str">`attr_symbol_path`</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">255</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span> <span class="com"># 属性组合出的规格路径</span> <span class="str">`price`</span><span class="pln"> DOUBLE</span><span class="pun">(</span><span class="lit">15</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> NOT NULL DEFAULT </span><span class="str">'0.00'</span><span class="pun">,</span> <span class="com"># 价格</span> <span class="str">`freight`</span><span class="pln"> DOUBLE</span><span class="pun">(</span><span class="lit">15</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> NULL DEFAULT </span><span class="str">'0.00'</span><span class="pun">,</span> <span class="com"># 运费</span> <span class="str">`stock`</span><span class="pln"> INT</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> UNSIGNED NOT NULL DEFAULT </span><span class="str">'0'</span><span class="pun">,</span> <span class="com"># 库存数量</span><span class="pln"> PRIMARY KEY </span><span class="pun">(</span><span class="str">`sku_id`</span><span class="pun">)</span> <span class="pun">);</span> |
该表是用户在添加完商品属性后, 由系统负责生成的该商品所有的可以选择的属性组合方案, attr_symbol_path 字段即是该商品不同属性值的 symbol 字段组合后的路径, 用来指示该规格具体指代哪些属性。
如 attr_symbol_path 字段值为 1,4,7, 则对应 item_attr_val 表中 item_sku.item_id = item_attr_val.item_id 条件下 symbol 字段值为 1, 4, 7 的属性组合: TD-LTE/FDD-LTE/TDS/WCDMA/EVDO/GSM – 深空灰 – 16GB。
将示例中具有三种网络类型、三种机身颜色、三种机身内存的属性数据生成 SKU 后的 item_sku 表数据图示:
从图中数据看出, 该商品共有27种不同规格可选, 那么这时在确定用户选择的某种规格的价格等信息时只需一条 SQL 语句即可完成:
|
1 |
<span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span> <span class="str">`item_sku`</span> <span class="kwd">where</span> <span class="str">`item_id`</span><span class="pun">=</span><span class="lit">6</span> <span class="kwd">and</span> <span class="str">`attr_symbol_path`</span><span class="pun">=</span><span class="str">'1,4,7'</span><span class="pun">;</span> |
