SQL 如何将列表存储在 db 列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/444251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store a list in a db column
提问by Nifle
I would like to store an object FOOin a database. Lets say FOO contains three integers and a list of "Fruits".
我想在数据库中存储一个对象FOO。假设 FOO 包含三个整数和一个“水果”列表。
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?
我可以将水果列表存储在列中吗？
回答by Mehrdad Afshari
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.
在规范化的关系数据库中，这种情况是不可接受的。您应该有一个联结表，为 FOO 对象的每个不同 ID 和 Fruit 的 ID 存储一行。存在这样的一行意味着水果在 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)
)
To add Apple fruit to the list of a specific FOO object with ID=5, you would:
要将 Apple 水果添加到 ID=5 的特定 FOO 对象的列表中，您需要：
INSERT FOOFruits(FooID, FruitID)
SELECT 5, ID FROM Fruits WHERE name = 'Apple'
回答by AKX
If you're quite sure of what you're doing (ie. you won't need to look up the list's values, for example), you could also serialize your object, or just the list object, and store it in a binary column.
如果您非常确定自己在做什么（例如，您不需要查找列表的值），您还可以序列化您的对象，或仅序列化列表对象，并将其存储在二进制文件中柱子。
Just character-separating the values may be fine too, and cheaper in terms of saving and loading, but be careful your data doesn't contain the separator character, or escape it (and handle the escapes accordingly while loading, etc... Your language of choice may do a better job at this than you, though. ;) )
仅对值进行字符分隔也可以，并且在保存和加载方面更便宜，但请注意您的数据不包含分隔符，或对其进行转义（并在加载时相应地处理转义等...您的不过，选择的语言在这方面可能比你做得更好。;) )
However, for a "proper" solution, do what Mehrdad described above.
但是，对于“正确”的解决方案，请执行上述 Mehrdad 的操作。
回答by E.J. Brennan
Its technically possible but would be very poor design, imo.
它在技术上是可行的，但设计非常糟糕，imo。
You could do it by building the string and storing it in a nvarchar(max) field (if using sql server or its equivalent).
您可以通过构建字符串并将其存储在 nvarchar(max) 字段中（如果使用 sql server 或其等效项）来实现。
回答by Diodeus - James MacFarlane
You can, but it will likely treated as text, making searching in this column difficult and slow. You're better of using a related table.
您可以，但它可能会被视为文本，从而使在此列中搜索变得困难且缓慢。您最好使用相关表。
回答by Andru Luvisi
Some databases allow multiple values to be stored in a single column of a single row, but it is generally more convenient to use a separate table.
一些数据库允许将多个值存储在单行的单列中，但通常使用单独的表更方便。
Create a table with two columns, one that contains pointers to the primary key of the objects table, and one that contains pointers to the primary key of the fruit table. Then, if an object has three fruit, there are three rows in the object_fruit table that all all point to the same object, but to three different fruit.
创建一个包含两列的表，一列包含指向对象表主键的指针，另一列包含指向水果表主键的指针。然后，如果一个对象有三个水果，那么 object_fruit 表中有三行都指向同一个对象，但指向三个不同的水果。
回答by Andru Luvisi
INSERT FOOFruits (FooID, FruitID)
SELECT 5, ID
FROM Fruits
WHERE name IN ('Apple', 'Orange');
