MySQL 与 FIFO 的限制关系表

Question

目前正在为社交网站开发 MySQL 结构。

我有一个要求:一个用户最多只能有 5 个 friend ，当添加第 6 个 friend 时，它会删除最旧的一个(先进先出)，我不完全确定解决这个问题的最佳方法是什么。

在我的后端代码中，我还在添加用户时添加了一个约束，以便 user1_id < user2_id。

这是相关表格:

CREATE TABLE friendships (
user1_id INT,
user2_id INT,
date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user1_id)
    REFERENCES users (id)
    ON DELETE CASCADE,
FOREIGN KEY (user2_id)
    REFERENCES users (id)
    ON DELETE CASCADE
);

CREATE UNIQUE index single on friendships (user1_id, user2_id);

我考虑过使用触发器并写道:

CREATE TRIGGER inFriendships 
BEFORE INSERT ON friendships
FOR EACH ROW
BEGIN
  DECLARE current_amount_user1 INT DEFAULT 0;
  DECLARE current_amount_user2 INT DEFAULT 0;
  SET current_amount_user1 = ( SELECT COUNT(*) FROM friendships WHERE (user1_id = NEW.user1_id OR user2_id = NEW.user1_id) );
  IF current_amount_user1 = 5
    THEN 
      DELETE FROM friendships WHERE date_added IS NOT NULL AND (user1_id = NEW.user1_id OR user2_id = NEW.user1_id) order by date_added desc LIMIT 1;
  END IF;
  SET current_amount_user2 = ( SELECT COUNT(*) FROM friendships WHERE (user1_id = NEW.user2_id OR user2_id = NEW.user2_id) );
  IF current_amount_user2 = 5
    THEN 
      DELETE FROM friendships WHERE date_added IS NOT NULL AND (user1_id = NEW.user2_id OR user2_id = NEW.user2_id) order by date_added desc LIMIT 1;
  END IF;
END;

但是我遇到了死锁问题，现在我正在尝试寻找不同的解决方案。

欢迎所有解决方案和想法，包括 SQL 和后端伪代码。

Answer 1

(user1_id = NEW.user2_id OR user2_id = NEW.user2_id) 表示一个用户最多只能与 5 个人关联。应该有一种感觉； user1_id 应该是父用户的 ID，user2_id 应该是子用户的 ID。考虑到这一点，要获取用户的好友数量，请执行以下操作:

SELECT user1_id, user2_id, date_updated from friendships where user1_id=NEW.user1_id;

您可以允许向好友插入数据，然后在插入后运行触发器，以在好友数量超过 5 时删除最早的好友。