如何在我的 sql 数据库中存储一对多关系？(MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12402422/
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 one to many relation in my sql database ? (MySQL)
提问by Weacked
I'm making a website and I need to store a random number of data in my database.
我正在制作一个网站，我需要在我的数据库中存储随机数量的数据。
for example: User john may have one phone number where Hyman can have 3.
例如：用户 john 可能有一个电话号码，而 Hyman 可能有 3 个。
I need to be able so store an infinite number of values per user.
我需要能够为每个用户存储无限数量的值。
I couldn't find how to do this anywhere, Hope you can help me! :)
我在任何地方都找不到如何做到这一点，希望你能帮助我！:)
I am a novice in Relational databases.
我是关系数据库的新手。
回答by Bj?rn
You create a separate table for phone numbers (i.e. a 1:M relationship).
您为电话号码创建一个单独的表（即 1:M 关系）。
create table `users` (
`id` int unsigned not null auto_increment,
`name` varchar(100) not null,
primary key(`id`)
);
create table `phone_numbers` (
`id` int unsigned not null auto_increment,
`user_id` int unsigned not null,
`phone_number` varchar(25) not null,
index pn_user_index(`user_id`),
foreign key (`user_id`) references users(`id`) on delete cascade,
primary key(`id`)
);
Now you can, in an easily manner, get a users phone numbers with a simple join;
现在，您可以通过简单的连接轻松获取用户的电话号码；
select
pn.`phone_number`
from
`users` as u,
`phone_numbers` as pn
where
u.`name`='John'
and
pn.`user_id`=u.`id`
回答by Fábio N Lima
I think you need to create a one to many relationship table.
我认为您需要创建一个一对多的关系表。
You can see more infos here: http://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html
您可以在此处查看更多信息：http: //dev.mysql.com/doc/workbench/en/wb-relationship-tools.html
