假设我有一个用户表。每个用户都可以与最多 3 个其他用户组成一个团队。所以现在我为团队中的每个位置都有一列(总共 4 列,所以你自己的 id 填写了一个位置,所以你知道你在团队中的位置)。我将 ID 放在其他每列中的团队其他成员。最后,一个团队中的每个人都会在这 4 列中具有相同的值。
我将如何查询 sql 以查看这些 ID 并提取团队中所有其他用户的信息(因此通过查看一个用户,我可以提取所有 4 个团队成员行)?这是存储数据的最有效方法吗?
从一开始就规范化数据。从长远来看,它将带来丰厚的回报。这样,您就可以正常维护和查询数据。
简化形式的建议架构可能如下所示
CREATE TABLE users
(
`user_id` int not null auto_increment primary key,
`user_name` varchar(5)
);
CREATE TABLE teams
(
`team_id` int not null auto_increment primary key,
`team_name` varchar(5)
);
CREATE TABLE team_users
(
`team_id` int,
`user_id` int,
primary key (team_id, user_id),
foreign key (team_id) references teams (team_id),
foreign key (user_id) references users (user_id)
);
如果您需要为名为"team2"的团队拉取所有成员
SELECT t.team_id, t.team_name, u.user_id, u.user_name
FROM team_users tu JOIN teams t
ON tu.team_id = t.team_id JOIN users u
ON tu.user_id = u.user_id
WHERE t.team_name = 'team2'
如果您需要获取具有user_id = 2
的用户是成员的团队的所有成员
SELECT t.team_id, t.team_name, u.user_id, u.user_name
FROM team_users tu JOIN team_users tu2
ON tu.team_id = tu2.team_id JOIN teams t
ON tu.team_id = t.team_id JOIN users u
ON tu.user_id = u.user_id
WHERE tu2.user_id = 2
示例输出:
|TEAM_ID |TEAM_NAME |USER_ID |USER_NAME ||---------|-----------|---------|-----------|| 2 | 团队2 | 2 | 用户2 || 2 | 团队2 | 4 | 用户4 || 2 | 团队2 | 5 | 用户5 |
这是 SQLFiddle 演示
我认为
首先,您不应该关心这是存储此类数据的最有效方式,而是最合乎逻辑的方式。MySQL通常非常擅长成为关系数据库,因此以下内容应该表现得非常好:
制作两张桌子。一个用于用户(带 ID),一个用于团队。
在团队表中,您放置用户的 4 个 ID。如果您愿意,您可以输入团队ID和名称或任何内容,但不必这样做。
然后,您会找到如下所示的团队条目:
SELECT * FROM team WHERE u1 == ? OR u2 == ? OR u3 == ? or u4 == ?;
然后单独查询用户。
为了提高性能,您可以考虑表联接,将用户的数据联接到团队条目中:
SELECT * from team
LEFT JOIN user user1 ON u1 == user1.id
LEFT JOIN user user2 ON u2 == user2.id
LEFT JOIN user user3 ON u3 == user3.id
LEFT JOIN user user4 ON u4 == user4.id;
这将为每个团队获取一行,其中包含所有用户详细信息。
甚至更好:多对多
多对多关系有两个表(用户和团队)和一个关系表(team_users),其中包含 ID 对和潜在的其他值(例如在团队中的位置)。
然后,您可以将用户映射到他的团队,并从中获取所有用户(和其他值),所有这些都仅使用关系表。使用联接,您可以再次获取信息以及映射,从而减少查询数量。MySQL真的很擅长这个!