如何在db列中存储列表 [英] How to store a list in a db column

问题描述

我想在数据库中存储对象 FOO 。
假设FOO包含三个整数和一个水果列表。

I would like to store an object FOO in a database. Lets say FOO contains three integers and a list of "Fruits".

列表可以有任何长度，唯一的东西我知道是所有允许的水果都存储在另一个表中。

The list can have any length, the only thing I know is that the all the fruits allowed are stored in another table.

我可以将水果列表存储在列中吗？

Can I store the fruit list in a column?

推荐答案

在规范化的关系数据库中，这种情况是不可接受的。您应该有一个连接表，为FOO对象的每个不同ID和水果的ID存储一行。存在这样一行意味着果实在FOO的列表中。

In a normalized relational database, such a situation is unacceptable. You should have a junction table that stores one row for each distinct ID of the FOO object and the ID of the Fruit. Existence of such a row means the fruit is in that list for the FOO.

CREATE TABLE FOO ( 
  id int primary key not null,
  int1 int, 
  int2 int, 
  int3 int
)

CREATE TABLE Fruits (
  id int primary key not null,
  name varchar(30)
)

CREATE TABLE FOOFruits (
  FruitID int references Fruits (ID),
  FooID int references FOO(id),
  constraint pk_FooFruits primary key (FruitID, FooID)
)

<要将苹果果实添加到ID = 5的特定FOO对象的列表中，您将：

To add Apple fruit to the list of a specific FOO object with ID=5, you would:

INSERT FOOFruits(FooID, FruitID)
SELECT 5, ID FROM Fruits WHERE name = 'Apple'

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