MySQL - 2路查询检查 [英] MySQL - 2 way query check

问题描述

我正在我的网络应用中实施添加好友，因此用户可以将其他用户添加为恶意软件。

I am implementing "Add Friend" in my web app, so users could add other users as fiends.

我们有2个表： tbl_users 和 tbl_relations ，tbl_users为注册用户提供唯一的ID，tbl_relations将存储是朋友的用户，例如tbl_relations中的某些行为：

We have 2 tables: tbl_users and tbl_relations, tbl_users have an unique ID for the registered users and tbl_relations will store the users which are friends, for example some rows in tbl_relations are:

id     user_id_1     user_id_2
1      4             6
2      4             8
3      8             23
4      12            84
5      3             4
...

在上述结果中， id是tbl_relations的唯一ID，user_id_1是tbl_users的外键，user_id_2是tbl_users的外键，现在想象一下，查询并检查id为4的用户是否为id为9的用户的朋友，这里我们需要以两种方式发送查询，我的意思是：

In the above results, the id is the unique id for the tbl_relations, user_id_1 is foreign key to tbl_users and user_id_2 is foreign key to tbl_users, now imagine we want to query and check if user with id "4" is friend with user with id "9" or not, here we need to send the query in 2 ways, I mean:

SELECT * FROM tbl_relations WHERE (user_id_1 = '4' AND user_id_2 = '9') OR (user_id_1 = '9' AND user_id_2 = '4')

上面的查询对我来说似乎有点奇怪，应该有另一种方式来实现这个我猜，也许是一个不同的数据库结构？

The above query seems a little weird to me, there should be another way for implementing this I guess, maybe a different database structure?

或另一个查询，我们想得到在4和8的用户之间共同的朋友，我应该在这种情况下获得共同的朋友吗？有没有更好的数据库结构？

Or another query, we want to get the mutual friends between users with id "4" and "8", how I'm supposed to get the mutual friends in this scenario? is there any better database structure for this?

我会感谢任何帮助。

推荐答案

我会将关系取消归一化，使其对称。也就是说，如果1和2是朋友，我会有两行（1,2）和（2,1）。

I would de-normalize the relation such that it's symmetric. That is, if 1 and 2 are friends, i'd have two rows (1,2) and (2,1).

缺点是它是两行大小，你在形成和打破友谊时必须做2次写作。所有读取查询的优点是更简单。这可能是一个很好的权衡，因为大多数时候你正在阅读而不是写作。

The disadvantage is that it's twice the size, and you have to do 2 writes when forming and breaking friendships. The advantage is all your read queries are simpler. This is probably a good trade-off because most of the time you are reading instead of writing.

这有另外的优势，如果你最终超过一个数据库，并决定做用户分片，你不必遍历每个其他的db分片，以确定一个人的朋友是谁。

This has the added advantage that if you eventually outgrow one database and decide to do user-sharding, you don't have to traverse every other db shard to find out who a person's friends are.

这篇关于MySQL - 2路查询检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！