如何确保复合主键的值组合唯一？附MySQL表创建语句

嘿，这个需求我太熟悉了！你当前的compare表虽然设置了(id_1, id_2)作为复合主键，但它只能保证有序的组合唯一——也就是说(1,2)和(2,1)会被当成两条不同的记录存储，这显然不符合你“仅允许复合主键的值组合唯一”（不管顺序）的要求。下面给你几种可行的解决方案：

方法1：插入/更新时强制ID顺序（应用层或SQL层控制） #

这是最直接的方案，核心思路是始终让id_1存储较小的书ID，id_2存储较大的书ID，这样不管传入的顺序如何，最终存储的组合都是唯一的，靠复合主键约束就能阻止重复。

• 如果是在应用代码中插入数据，直接在代码里判断两个ID的大小，把小的赋值给id_1，大的赋值给id_2即可。
• 如果是直接写SQL插入，可以用MySQL的LEAST()和GREATEST()函数自动处理顺序：

-- 不管传入(1,2)还是(2,1)，都会存储为(1,2)
INSERT INTO compare (id_1, id_2) VALUES (LEAST(2, 1), GREATEST(2, 1));

方法2：用生成列+唯一约束（数据库层面自动保证） #

如果希望在数据库层面自动维护无序组合的唯一性，可以借助MySQL的生成列（MySQL 5.7及以上版本支持）。我们可以新增两个存储最小和最大ID的生成列，然后给这两个列加唯一约束：

-- 修改compare表结构，添加生成列和唯一约束
ALTER TABLE compare
ADD COLUMN min_id BIGINT UNSIGNED GENERATED ALWAYS AS (LEAST(id_1, id_2)) STORED,
ADD COLUMN max_id BIGINT UNSIGNED GENERATED ALWAYS AS (GREATEST(id_1, id_2)) STORED,
ADD UNIQUE KEY unique_book_pair (min_id, max_id);

这样一来，不管你插入(1,2)还是(2,1)，min_id和max_id的组合都会是(1,2)，唯一约束会直接阻止重复插入，无需在应用层额外处理。

方法3：用触发器自动调整ID顺序 #

触发器可以在插入或更新数据前自动调整id_1和id_2的顺序，确保小ID在前，大ID在后，这样原有的复合主键就能发挥作用：

创建插入前触发器 #

DELIMITER //
CREATE TRIGGER compare_before_insert
BEFORE INSERT ON compare
FOR EACH ROW
BEGIN
    -- 如果id_1大于id_2，交换两者的值
    IF NEW.id_1 > NEW.id_2 THEN
        SET @temp = NEW.id_1;
        SET NEW.id_1 = NEW.id_2;
        SET NEW.id_2 = @temp;
    END IF;
END //
DELIMITER ;

创建更新前触发器（防止更新时打乱顺序） #

DELIMITER //
CREATE TRIGGER compare_before_update
BEFORE UPDATE ON compare
FOR EACH ROW
BEGIN
    IF NEW.id_1 > NEW.id_2 THEN
        SET @temp = NEW.id_1;
        SET NEW.id_1 = NEW.id_2;
        SET NEW.id_2 = @temp;
    END IF;
END //
DELIMITER ;

方案对比 #

• 推荐方法1：如果应用层可以控制插入逻辑，实现最简单，性能也最好。
• 推荐方法2：如果希望把唯一性逻辑放在数据库层，生成列的方式比触发器更简洁，维护成本更低。
• 方法3（触发器）：虽然能实现需求，但触发器会增加数据库的复杂度，后续维护和排查问题时需要额外注意。

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