MySql查询从Friends表中获取好友列表

时间:2022-10-21 23:37:12

I have a table named friends with following structure:

我有一个名为friends的表,结构如下:

id, from_id, to_id, confirmed, ....

This table contains the list of all friends of all users. The from_id and to_id contain ids of the users where from_id is the one who actually sent the friend request.

此表包含所有用户的所有朋友的列表。 from_id和to_id包含用户的id,其中from_id是实际发送好友请求的用户。

Now, I need to select the list of all the friends of a particular user into.

现在,我需要选择特定用户的所有朋友的列表。

Is is better to do it using the following query or a query with union:

最好使用以下查询或带有union的查询来执行此操作:

"select (from_id + to_id)- $user_id as friend_id, ... from friends where from_id=$user_id or to_id=$user_id"

3 个解决方案

#1


1  

Although I love your mathematic-fan solution I think it is not considering overflow issues. For example: what would happen if you add two extremely big numbers?

虽然我喜欢你的数学风扇解决方案,但我认为它并没有考虑溢出问题。例如:如果添加两个非常大的数字会发生什么?

Apart from that, your solution relies on a numeric user_id. Using a union might take a bit more (you should test that to make sure) but will work with any data type.

除此之外,您的解决方案依赖于数字user_id。使用联合可能需要更多(您应该测试以确保)但可以使用任何数据类型。

#2


1  

I would use a union (make sure you have indexes on from_id and to_id):

我会使用union(确保你有from_id和to_id的索引):

select from_id from friends where to_id = $user_id
union
select to_id from friends where from_id = $user_id

It's more clear this way in my opinion.

在我看来,这种方式更清晰。

#3


1  

assuming you have keys on the ids the union will certainly be better, then performing the arithmetic operations.

假设你有关于id的键,联合肯定会更好,然后执行算术运算。

#1


1  

Although I love your mathematic-fan solution I think it is not considering overflow issues. For example: what would happen if you add two extremely big numbers?

虽然我喜欢你的数学风扇解决方案,但我认为它并没有考虑溢出问题。例如:如果添加两个非常大的数字会发生什么?

Apart from that, your solution relies on a numeric user_id. Using a union might take a bit more (you should test that to make sure) but will work with any data type.

除此之外,您的解决方案依赖于数字user_id。使用联合可能需要更多(您应该测试以确保)但可以使用任何数据类型。

#2


1  

I would use a union (make sure you have indexes on from_id and to_id):

我会使用union(确保你有from_id和to_id的索引):

select from_id from friends where to_id = $user_id
union
select to_id from friends where from_id = $user_id

It's more clear this way in my opinion.

在我看来,这种方式更清晰。

#3


1  

assuming you have keys on the ids the union will certainly be better, then performing the arithmetic operations.

假设你有关于id的键,联合肯定会更好,然后执行算术运算。