如何在mysql中存储商品的属性? [英] How to store the properties of the goods in mysql?

问题描述

现在，我正在计划数据库产品的结构.这里有一个存储选项/货物参数的问题. 例如，如果是商品的大小-足以在宽度/高度/深度上创建三个字段，但是如果有几十个参数怎么办? 假定可以更改参数列表，并且可以看到以下结构: 1-宽度 2-身高 ... 11-颜色

Now I'm planning the structure of the database products. There was a question of storage options / parameters of the goods. For example, if it were the size of the goods - it would be enough to create three fields to the width / height / depth, but what if dozens of parameters? It is assumed that the list of parameters can be changed and I see about this structure: 1 - width 2 - height ... 11 - color

但是我不能像商品一样绑它-可以将字段设置为json类型

but I can not tie it like a commodity - it is possible to make the field with json type

{1:"20"，2"30"，...，11:白色"} 然后对货物样本进行处理，但我不知道该如何处理过滤器参数

{1: "20", 2 "30", ..., 11: "white"} and then treated at a sample of the goods, but I do not know how then do filter parameters

谁能在正确的道路上提供建议?

who can advise on the right path?

推荐答案

这可以通过三种方法实现

This thing can be achieved with three methods

1. 将所有separate columns添加到products table中.
2. 在products table的末尾添加一个带有长文本类型的extras列，以通过 json-encoding 或序列化在其中存储所有额外的/动态参数.

3. 最后一个方法是使用单独的元表在表中存储键，值对和product_id.

1. Adding all separate columns in the products table.
2. Adding an extras column with longtext type at end of products table to store all extra/dynamic parameters in it by json-encoding or serializing.

3. Last one is to use a separate meta table for storing key,value pair alongwith product_id in that table.

CREATE TABLE product_meta()
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  // Primary key of this table
    product_id INT, // For joining purpose and reference
    meta_key VARCHAR(16),
    meta_value VARCHAR(64)
);

因此，对于产品ID 137，您可能有几条记录:

So then for product ID 137, you might have several records:

|------------------------------------------|
| id   | product_id | meta_key | meta_value|
|------------------------------------------|
| 3137 | 137        | size     | large     |
| 3138 | 137        | color    | green     |
| 3139 | 137        | height   | 11.3      |
|------------------------------------------|

这篇关于如何在mysql中存储商品的属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！