SQL 如何在SQL数据库的单个字段中存储多个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24865310/
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 multiple values in single field in SQL database?
提问by Krutika Sonawala
My problem is, I want to store a person with multiple phone numbers in the database. for a single variable for number will store only one number for each.
我的问题是，我想在数据库中存储一个有多个电话号码的人。对于 number 的单个变量，每个变量只会存储一个数字。
Now if I want to add another phone number it is creating another new record with the same details but the different number.
现在，如果我想添加另一个电话号码，它会创建另一个具有相同详细信息但号码不同的新记录。
I want to display all these numbers together. Can anyone help?
我想一起显示所有这些数字。任何人都可以帮忙吗？
采纳答案by christiandev
You could use a second table to store the numbers, and link back with a Foreign Key:
您可以使用第二个表来存储数字，并使用外键链接回来：
PersonTable: PersonId, Name, etc..
The second table will hold the numbers...
第二个表将保存数字...
NumbersTable: NumberId, PersonId(fk), Number
You could then get the numbers like this...
然后你可以得到这样的数字......
SELECT p.Name, n.Number from PersonTable p Left Join NumbersTable n
on p.PersonId = n.PersonId
This is a simple example. I have used a LEFT JOINhere in case a person doesn't supply their number. Also, this is just pseudo code, so don't use Tablein the name.
这是一个简单的例子。我在LEFT JOIN这里使用了以防有人不提供他们的号码。另外，这只是伪代码，所以不要在名称中使用Table。
回答by semao
You should create separate tables for Person and PhoneNumber.
您应该为 Person 和 PhoneNumber 创建单独的表。
CREATE TABLE Person(PersonId int IDENTITY(1,1) PRIMARY KEY)
CREATE TABLE Phone(
PersonId int,
PhoneNumber varchar(20),
CONSTRAINT PK_Phone PRIMARY KEY(PersonId,PhoneNumber),
CONSTRAINT FK_PersonId FOREIGN KEY(PersonId) REFERENCES Person(PersonId)
)
回答by Ali Mirza
The best way is:
最好的办法是：
- serialize your phone.no array,
- store in your table,
- deserialize when you want to get phone.no.
- 序列化你的手机。没有数组，
- 存放在你的桌子上，
- 当您想要获取 phone.no 时进行反序列化。
回答by Muhammad Wasim Akram
This is not a best approach because your database will become ambiguous and will fail 1st normal form. Simple you have to make another column and in this your first column primary key act as a foreign key. And then using joins simply you can do it.
这不是最好的方法，因为您的数据库将变得模棱两可，并且无法满足第一范式。很简单，您必须创建另一列，在此列中，您的第一列主键充当外键。然后简单地使用连接就可以做到。
回答by Brunis
You could just add them comma seperated. If you're doing a webapp, you could make your phone column a string and json encode them, then you'd have support for endless amount of phone numbers :)
您可以将它们以逗号分隔。如果你正在做一个 web 应用程序，你可以让你的电话列成为一个字符串和 json 编码它们，然后你就可以支持无限数量的电话号码:)
回答by AshutoshPujari
You can store multiple data as delimiter separated values. Use pipe (|) or tilt (~) as the delimiter. And when we are inserting new value or updating existing value, check if the phone number already exists. If there is already some data present in the phone number column, just append it with delimiter + new phone number.
您可以将多个数据存储为分隔符分隔值。使用竖线 (|) 或倾斜 (~) 作为分隔符。当我们插入新值或更新现有值时，检查电话号码是否已经存在。如果电话号码列中已经存在一些数据，只需将其附加分隔符 + 新电话号码。
