如何在 MySQL 数据库中存储一对多关系?
我正在制作一个网站，我需要在我的数据库中存储随机数量的数据.
I'm making a website and I need to store a random number of data in my database.
例如，用户 john 可能有一个电话号码，而 jack 可能有 3 个.
For example, User john may have one phone number where jack can have 3.
我需要能够为每个用户存储无限数量的值.
I need to be able to store an infinite number of values per user.
推荐答案
您为电话号码创建一个单独的表(即 1:M 关系).
You create a separate table for phone numbers (i.e. a 1:M relationship).
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`
相关文章