在一列中添加多个值

I have to create a table in the way shown below. Can we create in this way? (If Yes)

我必须按照如下所示的方式创建一个表。我们能这样创造吗?(如果有)

Table_name: Sample

Table_name:示例

product_id|   product_name| category   |
    1     |   Sample1     |  1|2|3     |
    2     |   sample2     |  4|5|6     |

where category filed which contains multiple values.

其中包含多个值的类别。

& how we can search for category 4 comes in which row of the table.

以及我们如何搜索第4类在表格的哪一行。

7 个解决方案

#1

You cannot create nested table. And the thing on your mind is not a good idea to design table like that. You should have two tables (exactly three which holds the description if the category). One is for the product and the second table holds the category for each product. Example design would look like this,

不能创建嵌套表。你脑子里想的不是这样设计桌子的好主意。您应该有两个表(恰好有三个表，其中包含类别的描述)。一个是产品，另一个是每个产品的类别。例子设计是这样的，

CREATE TABLE Product
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50) UNIQUE
);

CREATE TABLE Category
(
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50) UNIQUE
);

CREATE TABLE Product_Category
(
    RecordD INT AUTO_INCREMENT PRIMARY KEY,
    CategoryID INT,
    ProductID INT,
    CONSTRAINT tb_uq UNIQUE(CategoryID, ProductID)
);

and Populate Sample Records

和填充样品记录

INSERT Category VALUES (1, 'Fruit');
INSERT Category VALUES (2, 'Vegetable');

INSERT Product VALUES (1, 'Apple');
INSERT Product VALUES (2, 'Banana');
INSERT Product VALUES (3, 'Cabbage');
INSERT Product VALUES (4, 'Squash');
INSERT Product VALUES (5, 'Tomato');

INSERT Product_Category (CategoryID, ProductID) VALUES (1,1);
INSERT Product_Category (CategoryID, ProductID) VALUES (1,2);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,3);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,4);
INSERT Product_Category (CategoryID, ProductID) VALUES (1,5);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,5);

• SQLFiddle Demo (with records included)
• sql小提琴演示(包含记录)

sample queries

示例查询

-- NORMAL QUERY
SELECT  a.ProductName, c.CategoryName
FROM    Product a
        INNER JOIN Product_category b
          ON a.ProductID = b.ProductID
        INNER JOIN Category c
          ON b.CategoryID = c.CategoryID
ORDER BY ProductName;

-- If you want catgoryName to be comma separated
SELECT  a.ProductName, GROUP_CONCAT(c.CategoryName) CategoryList
FROM    Product a
        INNER JOIN Product_category b
          ON a.ProductID = b.ProductID
        INNER JOIN Category c
          ON b.CategoryID = c.CategoryID
GROUP BY ProductName
ORDER BY ProductName;

#2

There seems to be one-to-many relationship between product and category and so you should normalize the category details into a different table as follows:

产品与类别之间似乎存在一对多的关系，所以你应该将类别细节归一化，形成一个不同的表格，如下所示:

• product: product_id, product_name.
• 产品:product_id product_name。
• product_categories: product_id, category_id
• product_categories:product_id category_id添加

Then your product_categories table would be

那么您的product_categories将是

product_id | category_id
         1 | 1
         1 | 2
         1 | 3
         2 | 4
         2 | 5
         2 | 6

Then a simple select statement JOINING the product table with product_categories table will give you all products in a given category.

然后，将product表与product_categories表连接的一个简单的select语句将为您提供给定类别中的所有产品。

#3

Make a separate table for categories:

为类别制作单独的表格:

table_categories

table_categories

product_id| category   |
    1     |  1         |
    1     |  2         |
    1     |  3         |
    2     |  4         |
    2     |  5         |
    2     |  6         |

Then you can search like this:

然后你可以这样搜索:

SELECT p.product_id, p.product_name
FROM table_products p JOIN table_categories c
ON p.product_id = c.product_id
WHERE c.category = 4

#4

Assuming those are strings in the form of <item>|<item>|<item>, you can, but it would be a better idea to give each category its own row relating to product_id and product_name. That way you can get better performance and have a more 'natural' structure so that you wouldn't have to do queries like:

假设这些字符串以 | | 的形式存在，您可以这样做，但是最好为每个类别指定与product_id和product_name相关的行。这样你就能获得更好的性能，并拥有一个更“自然”的结构，这样你就不必做这样的查询了:

SELECT *
FROM Sample
WHERE category LIKE '%4|%' OR category LIKE '%|4|%' or category LIKE '%|4%'

in order to retrieve the data. A slightly better solution would be to surround all fields with pipes - if you have the ability to modify these strings beforehand, creating fields in this manner will make dealing with cases where 4 appears in the middle or at the beginning the same (e.g. |4|5|6| could be queried the same way as |3|4|5).

为了检索数据。稍微更好的解决方案是围绕各个领域与管道——如果你有能力修改这些字符串事先创建字段以这种方式将处理情况4初出现在中间或相同(例如| 4 | 5 | 6 |可以查询一样| 3 | 4 | 5)。

A more natural structure would be something like:

更自然的结构应该是:

product_id|   product_name| category |
    1     |   Sample1     |  1       |
    1     |   Sample1     |  2       |
    1     |   Sample1     |  3       |
    2     |   sample2     |  4       |
    2     |   sample2     |  5       |
    2     |   sample2     |  6       |

#5

You need another table to represent the many-to-many relation. You should not insert multiple values into one column.

您需要另一个表来表示多对多关系。不应该在一列中插入多个值。

CREATE TABLE products (product_id, product_name)
CREATE TABLE product_category (product_id, category_id)

INSERT INTO products (product_id, product_name) VALUES (1, 'Sample1');
INSERT INTO products (product_id, product_name) VALUES (2, 'Sample2');

INSERT INTO product_category (product_id, category_id) VALUES (1, 1);
INSERT INTO product_category (product_id, category_id) VALUES (1, 2);
INSERT INTO product_category (product_id, category_id) VALUES (1, 3);
INSERT INTO product_category (product_id, category_id) VALUES (2, 4);
INSERT INTO product_category (product_id, category_id) VALUES (2, 5);
INSERT INTO product_category (product_id, category_id) VALUES (2, 6);

To retrieve all products in category 4:

检索第4类的所有产品:

SELECT 
  products.* 
FROM 
  products 
INNER JOIN 
  product_category 
ON 
  products.product_id = product_category.product_id 
WHERE 
  product_category.category_id = 4;

#6

Yes you can, in fact we do this by making 'category' (as an example) a BLOB, and thus allowing the ability to store very large subsets of data on one row. This allows a single connection and one query, which is far more optimized.

是的，实际上我们可以将“category”(例如)设置为BLOB，从而允许在一行中存储非常大的数据子集。这将允许一个连接和一个查询，这将更加优化。

The only way to process it then - would be to actually do so on the 'Client Side'. You may not want to use LIKE if the data in the field becomes to much.

那时处理它的唯一方法就是在“客户端”上进行。如果字段中的数据变得太多，您可能不希望使用LIKE。

#7

I think you should create two tables;
Table one

我认为你应该创建两个表;表一

product_id|   product_name|
    1     |   Sample1     |
    2     |   sample2     |

Table two

表2

category_id| product_id|  category  |
    1      |     1     |      1     |
    2      |     1     |      2     |
    3      |     1     |      3     |
    4      |     2     |      4     |
    5      |     2     |      5     |
    6      |     2     |      6     |