為 MySQL 創建部分唯一約束

我有與上一篇文章中提出的相同的問題：PostgreSQL multi-column unique constraint and NULL values。但是那裡的解決方案不適用，因為我使用的是 MySQL 而不是 PostgreSQL。

我的表如下所示：

CREATE TABLE `my_table` (
 `id` bigint(20) NOT NULL,
 `col1` bigint(20) NOT NULL,
 `col2` bigint(20) NOT NULL,
 `col3` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

我想要三列col1，col2並且col3是獨一無二的。但是一個正常的唯一約束

UNIQUE KEY `unique_constraint` (`col1`,`col2`,`col3`)

仍然允許我插入相同的行，其col3為NULL. 我想禁止這樣的條目：

1  | 1 | 1 | NULL
2  | 1 | 1 | NULL

我正在按照上面文章中的建議在 MySQL 中搜尋部分唯一約束，但顯然 MySQL 5.5 不支持。

有人可以給我一個提示如何在mysql中最好地解決這個問題，我也希望會有一個高性能的解決方案！

感謝 mysql 的新手（來自 PostgreSQL）！

想到的唯一選擇是BEFORE INSERT觸發器。在 5.5 中，您可以使用SIGNAL：

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
 IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
    WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
 THEN
   SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint', 
        MYSQL_ERRNO = 1001;
 END IF;
END$$
DELIMITER ;

$$ sqlfiddle $$

但是在 5.5 之前，你會這樣做：

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
 IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
    WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
 THEN
   SET NEW='Error: Cannot insert this item due to unique constraint';
 END IF;
END$$
DELIMITER ;

$$ sqlfiddle $$

引用自：https://dba.stackexchange.com/questions/41030