MySQL好友关系表中如何实现账号ID组合的无序唯一性约束？

这个问题在社交类应用的好友关系设计里太常见了，我来给你两种靠谱的解决方案，分别从约束层面和存储设计层面入手：

方案一：用生成列+唯一约束适配现有结构 #

如果不想改动现有的表结构逻辑，可以借助MySQL的**生成列（Generated Column）**来实现约束。核心思路是：不管插入的两个账号ID顺序如何，我们都生成一个“标准化”的好友对（小的ID在前，大的在后），然后给这个标准化的好友对加唯一约束，就能阻止重复记录。

具体SQL代码如下：

CREATE TABLE friend (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    accountID1 INT NOT NULL,
    accountID2 INT NOT NULL,
    -- 自动生成两个账号中较小的ID
    lower_account_id INT GENERATED ALWAYS AS (LEAST(accountID1, accountID2)) STORED,
    -- 自动生成两个账号中较大的ID
    higher_account_id INT GENERATED ALWAYS AS (GREATEST(accountID1, accountID2)) STORED,
    -- 给标准化的好友对加唯一约束
    UNIQUE KEY unique_friend_pair (lower_account_id, higher_account_id),
    -- 可选：添加外键关联账号表，确保账号ID有效
    FOREIGN KEY (accountID1) REFERENCES account(id),
    FOREIGN KEY (accountID2) REFERENCES account(id),
    -- 可选：禁止用户添加自己为好友
    CHECK (accountID1 != accountID2)
);

当你插入(1,2)或者(2,1)时，lower_account_id和higher_account_id都会是1和2，唯一约束会直接拦截第二次的重复插入请求，完美解决重复记录问题。

方案二：从存储规则根源规避（更推荐） #

另一种思路是约定存储规则：强制要求accountID1必须小于accountID2，这样所有好友关系都只会以一种顺序存储，从根源上避免重复。这种方案表结构更简洁，不需要额外的生成列。

具体SQL代码：

CREATE TABLE friend (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    accountID1 INT NOT NULL,
    accountID2 INT NOT NULL,
    -- 给固定顺序的好友对加唯一约束
    UNIQUE KEY unique_friend_pair (accountID1, accountID2),
    FOREIGN KEY (accountID1) REFERENCES account(id),
    FOREIGN KEY (accountID2) REFERENCES account(id),
    -- 强制accountID1 < accountID2，MySQL 8.0.16+支持CHECK约束生效
    CHECK (accountID1 < accountID2)
);

注意事项： #

1. 插入逻辑处理：应用层在处理好友请求时，要先比较两个账号ID的大小，把较小的ID存到accountID1，较大的存到accountID2。比如用户2请求加用户1为好友，插入时要存(1,2)而不是(2,1)。
2. 查询逻辑调整：查询某个用户的好友时，需要同时匹配accountID1和accountID2，示例SQL：

-- 查询用户1的所有好友ID
SELECT 
    CASE WHEN accountID1 = 1 THEN accountID2 ELSE accountID1 END AS friend_id
FROM friend
WHERE accountID1 = 1 OR accountID2 = 1;

如果是MySQL老版本（不支持CHECK约束），可以用触发器来替代CHECK的逻辑，或者在应用层严格控制插入的ID顺序，同样能达到效果。

内容的提问来源于stack exchange，提问作者Mercury