停止MySQL容忍UNIQUE约束中的多个NULL

Question

我的SQL模式是

CREATE TABLE Foo (
 `bar` INT NULL ,
 `name` VARCHAR (59) NOT NULL ,
 UNIQUE ( `name`, `bar` )
) ENGINE = INNODB;

MySQL允许重复以下语句，导致重复。

INSERT INTO Foo (`bar`, `name`) VALUES (NULL, 'abc');

尽管有

UNIQUE ( `name`, `bar` )

为什么可以容忍这种情况，我该如何阻止它？

Answer 1

警告：这个答案已经过时了。从MySQL 5.1开始，不支持BDB。

这取决于MySQL Engine Type. BDB不允许使用UNIQUE但MyISAM多个NULL值，即使使用UNIQUE，InnoDB也允许多个NULL。

Answer 2

通常，根据存储引擎的不同，NULL可能会也可能不会被视为唯一值。您必须使用不将NULL识别为唯一值的存储引擎，例如。InnoDB或MyISAM。

要解决此问题，您可以创建一个"null 值"，例如 99999999，您可以将其识别为NULL因为无法更改存储引擎决定如何处理唯一键中的 null。

Answer 3

更新：您应该改用@greenoldman在下面的评论中提出的想法。创建一个带有触发器的布尔字段，以根据可为 null 字段是否为 NULL 来设置值，然后将唯一约束中的布尔字段与定义唯一性的其他字段组合在一起。

---

如果您必须强制执行唯一约束，但还需要在列上有一个外键，因此要求它可为空，我找到了解决此问题的方法。我的解决方案就是由此衍生出来的，需要一点额外的空间。这是一个带有数字 id 字段的示例。

基本概念是，您必须创建另一个不可为空的字段，该字段将具有可空字段的值，并通过触发器将外键复制到其中。然后，将对不可为空的重复字段强制实施唯一约束。为此，您需要定义一个默认值为 0 的不可为空字段，如下所示：

ALTER TABLE `my_table` ADD  `uniq_foo` int(10) UNSIGNED NOT NULL DEFAULT '0';

然后，您只需要定义一些触发器，如下所示：

DROP TRIGGER IF EXISTS `my_table_before_insert`;
DELIMITER ;;
CREATE TRIGGER `my_table_before_insert` BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
    SET NEW.uniq_foo = IFNULL(NEW.foo_id, 0);
END;;
DELIMITER ;
DROP TRIGGER IF EXISTS `my_table_before_update`;
DELIMITER ;;
CREATE TRIGGER `my_table_before_update` BEFORE UPDATE ON `my_table`
FOR EACH ROW
BEGIN
    SET NEW.uniq_foo = IFNULL(NEW.foo_id, 0);
END;;
DELIMITER ;

Answer 4

BDB 不允许使用 UNIQUE 使用多个 NULL 值。但是MySQL放弃了BDB引擎(http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-12.html(。

所以现在：http://dev.mysql.com/doc/refman/5.5/en/create-index.html

对于所有引擎，UNIQUE 索引允许对可以包含 NULL 的列使用多个 NULL 值。 如果为 UNIQUE 索引中的列指定前缀值，则列值在前缀中必须是唯一的。