如何在MySQL中使用唯一约束处理NULL值？

Question

如何在MySQL中使用唯一约束处理NULL值？

9

我正在寻找如何实现对NULL值进行唯一性约束的方法。

MySQL不应允许多个NULL值。

员工：

id | name
---|-----
1  | null
2  | null -> should give error during inserting  2nd row.

- kabinarayan dalei

你能分享一下你得到的错误吗？ - Sadikhasan

我正在询问如何实现它。现在它正在向数据库中插入多个空值。 - kabinarayan dalei

3个回答

9

不，根据SQL-99规范，MySQL做得很正确。

https://mariadb.com/kb/en/sql-99/constraint_type-unique-constraint/

一个唯一约束条件使得不可能提交任何操作，该操作会导致唯一键包含任何非空重复值。(多个空值是允许的，因为空值从未等于任何东西，甚至是另一个空值。)

如果您使用唯一约束条件但不希望有多个具有NULL的行，请将列声明为NOT NULL并禁止任何行具有NULL。

- Bill Karwin

5

我的问题是我想在两列中定义一个唯一键，只允许存在唯一的组合。然而，这种情况允许多次出现 (NULL, NULL)，而这并不符合我的需求。所以这种情况有点棘手。 - Jan Krupa

1

alter table yourtable add column `virtual_null` varchar(20)  GENERATED ALWAYS AS (if(isnull(`your_nullable_column`),'null',`your_nullable_column`))) VIRTUAL;

alter table yourtable add constraint unique(virtual_null);

做到这一点，你会很开心的，在幕后，MySQL的null是一个哈希值。因为这样就不可能比较两个null值...

抱歉我的英语不好，祝你好运

- David Meth

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nothus · Accepted Answer

MySQL 5.7提供了一种解决方法：

mysql> CREATE TABLE `null_test` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `const` varchar(255) NOT NULL DEFAULT '',
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

使用软删除时，最好每个约束条件只有一行数据，deleted_at=NULL。

mysql> ALTER TABLE `null_test` ADD `vconst` int(1) GENERATED ALWAYS AS (((NULL = `deleted_at`) or (NULL <=> `deleted_at`))) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

所以我创建了一个虚拟列，当deleted_at被设置时，它会从1翻转到null。

mysql> ALTER TABLE `null_test` ADD UNIQUE KEY `nullable_index` (`const`,`vconst`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

不要将deleted_at包含在唯一约束中，而是添加虚拟列vconst。

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+------------+--------+
| id     | const | deleted_at | vconst |
+--------+-------+------------+--------+
| 999901 | Ghost | NULL       |      1 |
+--------+-------+------------+--------+
1 row in set (0.01 sec)

不需要插入vconst（但无论如何都不能插入）。

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
ERROR 1062 (23000): Duplicate entry 'Ghost-1' for key 'nullable_index'

再次插入会引发重复条目错误。

mysql> UPDATE `null_test` SET `deleted_at` = NOW() WHERE `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

设置delete_at与之相同，无需触碰vconst，它将自动翻转。

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+---------------------+--------+
| id     | const | deleted_at          | vconst |
+--------+-------+---------------------+--------+
| 999901 | Ghost | 2017-02-16 22:07:45 |   NULL |
+--------+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)

现在，您可以自由地插入一个具有相同约束条件的新行！

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+---------------------+--------+
| id     | const | deleted_at          | vconst |
+--------+-------+---------------------+--------+
| 999901 | Ghost | 2017-02-16 22:07:45 |   NULL |
| 999903 | Ghost | NULL                |      1 |
+--------+-------+---------------------+--------+
2 rows in set (0.01 sec)

在这种情况下，根据您软删除的程度，设置 deleted_at ，您可能希望将 deleted_at 包含在索引中，或者使用新索引，但我会让我的负载测试来决定。