如何设计一个好友关系链数据库表结构？（双向关注、粉丝模型）

user_relationships

user_relationships

BIGINT

NOT NULL

FOREIGN KEY

users.id

status

VARCHAR(20)

NOT NULL

DEFAULT 'active'

created_at

TIMESTAMP

NOT NULL

DEFAULT CURRENT_TIMESTAMP

updated_at

TIMESTAMP

NOT NULL

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

UNIQUE KEY (follower_id, following_id)

INDEX (follower_id, following_id)

INDEX (following_id, follower_id)

user_relationships

(follower_id = A, following_id = B)

following_id = B

follower_id = B

(follower_id = A, following_id = B)

(follower_id = B, following_id = A)

is_mutual

SELECT u.id, u.username -- 假设有一个users表
FROM users u
JOIN user_relationships ur1 ON u.id = ur1.following_id
JOIN user_relationships ur2 ON u.id = ur2.follower_id
WHERE ur1.follower_id = [用户X的ID]
  AND ur2.following_id = [用户X的ID]
  AND ur1.follower_id = ur2.following_id; -- 确保是互相关注的同一个对象

SELECT T1.following_id AS friend_id
FROM user_relationships T1
JOIN user_relationships T2 ON T1.follower_id = T2.following_id AND T1.following_id = T2.follower_id
WHERE T1.follower_id = [用户X的ID];

SELECT COUNT(*) AS fan_count
FROM user_relationships
WHERE following_id = [用户X的ID] AND status = 'active';

SELECT COUNT(*) AS following_count
FROM user_relationships
WHERE follower_id = [用户X的ID] AND status = 'active';

IN

SELECT ur1.following_id AS common_following_id
FROM user_relationships ur1
JOIN user_relationships ur2 ON ur1.following_id = ur2.following_id
WHERE ur1.follower_id = [用户A的ID]
  AND ur2.follower_id = [用户B的ID]
  AND ur1.status = 'active'
  AND ur2.status = 'active';

follower_id

following_id

(B, A)

is_mutual

true

(B, A)

(A, B)

is_mutual

false

is_mutual

follower_id

following_id

user_relationships

user_relationships

DELETE FROM user_relationships
WHERE follower_id = [用户A的ID] AND following_id = [用户B的ID];

(A, B)

(B, A)

user_blocks

user_relationships

status

user_blocks

id

BIGINT

PRIMARY KEY

AUTO_INCREMENT

blocker_id

BIGINT

NOT NULL

FOREIGN KEY

users.id

blocked_id

BIGINT

NOT NULL

FOREIGN KEY

users.id

created_at

TIMESTAMP

NOT NULL

DEFAULT CURRENT_TIMESTAMP

UNIQUE KEY (blocker_id, blocked_id)

INDEX (blocker_id, blocked_id)

INDEX (blocked_id)

user_blocks

INSERT INTO user_blocks (blocker_id, blocked_id)
VALUES ([用户A的ID], [用户B的ID]);

DELETE FROM user_relationships
WHERE follower_id = [用户A的ID] AND following_id = [用户B的ID];

DELETE FROM user_relationships
WHERE follower_id = [用户B的ID] AND following_id = [用户A的ID];

user_blocks

user_relationships