MySQL MySQL好友表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3009190/
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
MySQL friends table
提问by asmo
I have a MySQL DB in which I store data about each user.
我有一个 MySQL 数据库，我在其中存储有关每个用户的数据。
I would like to add a list of friends for each user. Should I create a table of friends for each user in the DB or is there a better way?
我想为每个用户添加一个朋友列表。我应该为数据库中的每个用户创建一个朋友表还是有更好的方法？
采纳答案by Cruachan
Assuming all your friends are also in the user table you will need a friends table which defines a simple one-to-many relationship - linking the users table back to itself. So
假设您所有的朋友也在用户表中，您将需要一个朋友表，它定义了一个简单的一对多关系 - 将用户表链接回自身。所以
User Table
UserID int identity not null
[other attribute fields]
Friends Table
UserIDLink1 int
UserIDLink2 int
[other attribute field]
Where both UserIDLink1 and UserIDLink2 are foreign keys on the Users table.
其中 UserIDLink1 和 UserIDLink2 都是用户表上的外键。
So for instance if I have three users
例如，如果我有三个用户
1 Joe
2 Bill
3 Jane
and Joe and Jane are friends then the Friends table would contain a single row
并且 Joe 和 Jane 是朋友，那么 Friends 表将包含一行
1 3
The above implicitly assumes that if A is a friend of B then B is a friend of A - if this isn't the case you'd probably want to rename UserIDLink1 and UserIDLink2 to UserID and FriendID or similar - in which case you'd have up to double the records too.
以上隐含假设，如果 A 是 B 的朋友，那么 B 是 A 的朋友 - 如果不是这种情况，您可能希望将 UserIDLink1 和 UserIDLink2 重命名为 UserID 和 FriendID 或类似名称 - 在这种情况下，您会也有多达一倍的记录。
Also for the bi-directional configuration (A is a friend of B if B is a friend of A) you should set up indexes on the Friends table for (UserIDLink1,UserIDLink2) and (UserIDLink2,UserIDLink1) to ensure access is always efficient if we were searching either for friends of joe or friends of jane (if you didn't set up the second index then the first query would be an efficient index lookup but the second would require a full table scan).
同样对于双向配置（如果 B 是 A 的朋友，则 A 是 B 的朋友）您应该在 Friends 表上为 (UserIDLink1,UserIDLink2) 和 (UserIDLink2,UserIDLink1) 设置索引以确保访问始终有效，如果我们正在搜索 joe 的朋友或 jane 的朋友（如果您没有设置第二个索引，那么第一个查询将是一个有效的索引查找，但第二个将需要全表扫描）。
If your links were not bidirectional this wouldn't be necessary to find out who A's friends are, but you would still probably most require it as you'll likely also need to find out who B is a friend of.
如果您的链接不是双向的，就没有必要找出 A 的朋友是谁，但您可能仍然最需要它，因为您可能还需要找出 B 的朋友。
回答by OMG Ponies
Assuming your USERtable has a primary key called idor something similar, use the following table:
假设您的USER表有一个名为id或类似的主键，请使用下表：
DROP TABLE IF EXISTS `friends`;
CREATE TABLE `friends` (
`user_id` int(10) unsigned NOT NULL,
`friend_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`friend_id`),
KEY `FK_FRIENDS_2` (`friend_id`),
CONSTRAINT `FK_FRIENDS_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
CONSTRAINT `FK_FRIENDS_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This setup supports that Peter is a friend of Mary, but Mary doesn't think of Peter like that. But the data exists to infer that Peter is an acquaintance for Mary...
这种设置支持 Peter 是 Mary 的朋友，但 Mary 并不这么认为 Peter。但数据的存在是为了推断彼得是玛丽的熟人……
The primary key being both columns also stops duplicates.
两列的主键也停止重复。
回答by Juha Syrj?l?
You are looking for M-to-N or many-to-many join table.
您正在寻找 M-to-N 或多对多连接表。
Table Users:
表用户：
USER_ID integer primary key,
NAME varchar
Table Friendships
表友谊
USER_ID integer not null,
FRIEND_ID integer not null,
Both USER_ID and FRIEND_ID are foreign keys that reference Users table (Users.user_id).
USER_ID 和 FRIEND_ID 都是引用用户表 (Users.user_id) 的外键。
If user 123 is friend of user 921. Add row (123, 921) to Friendships table.
如果用户 123 是用户 921 的朋友。将行 (123, 921) 添加到 Friendships 表中。
回答by Josh Mein
Create a table that contains all friends Each row in the table will contain the ID of the user and the id of their friend
创建一个包含所有朋友的表 表中的每一行都将包含用户的 ID 和他们朋友的 ID
回答by geshafer
Create a single table for all the friends and give each friend a UsersID which is equal to their respective users key
为所有朋友创建一个表，并给每个朋友一个 UsersID，它等于他们各自的用户密钥
