如何在MySQL中使用NULL值来做唯一的约束 [英] How to do unique constraint works with NULL value in MySQL

问题描述

MySQL不应允许多个空值。

员工：

  id |名称
 --- | ----- 
 1 | null 
 2 | null  - >在插入第二行时应该给出错误。 
  

解决方案

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; 
查询OK，0行受影响（0.03秒）
  

使用软删除，很好，如果你可以只有一行，一个 deleted_at = NULL 每个约束。

 code>的MySQL> ALTER TABLE`null_test` ADD`vconst` int（1）GENERATED ALWAYS AS（（（NULL =`deleted_at`）或（NULL'="`deleted_at`）））VIRTUAL; 
查询OK，0行受影响（0.01秒）
记录：0重复：0警告：0 
  

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

  mysql> ALTER TABLE`null_test` ADD UNIQUE KEY`nullable_index`（`const`，`vconst`）; 
查询OK，0行受影响（0.01秒）
记录：0重复：0警告：0 
  

而不是将 deleted_at 添加到唯一约束中，添加虚拟列 vconst 。

  mysql> INSERT INTO`null_test` SET`const` ='Ghost'; 
查询OK，1行受影响（0.00秒）
 
 mysql> SELECT * FROM`null_test` WHERE`const` ='Ghost'; 
 + -------- + ------- + ------------ + -------- + 
 | id | const | deleted_at | vconst | 
 + -------- + ------- + ------------ + -------- + 
 | 999901 |鬼| NULL | 1 | 
 + -------- + ------- + ------------ + -------- + 
 1行在设置（0.01秒）
  

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

  mysql> INSERT INTO`null_test` SET`const` ='Ghost'; 
错误1062（23000）：密钥'nullable_index'的重复条目'Ghost-1'
  

再次插入会抛出重复输入错误。

  mysql> UPDATE`null_test` SET`deleted_at` = NOW（）WHERE`const` ='Ghost'; 
查询OK，1行受影响（0.00秒）
匹配的行：1已更改：1警告：0 
  

与设置 delete_at 相同，不需要触摸 vconst ，它将自动翻转。 / p>

  mysql> SELECT * FROM`null_test` WHERE`const` ='Ghost'; 
 + -------- + ------- + --------------------- + ------ -  + 
 | id | const | deleted_at | vconst | 
 + -------- + ------- + --------------------- + ------ -  + 
 | 999901 |鬼| 2017-02-16 22:07:45 | NULL | 
 + -------- + ------- + --------------------- + ------ -  + 
 1行（0.00秒）
 
 mysql> INSERT INTO`null_test` SET`const` ='Ghost'; 
查询确定，1行受影响（0.00秒）
  

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

  mysql> SELECT * FROM`null_test` WHERE`const` ='Ghost'; 
 + -------- + ------- + --------------------- + ------ -  + 
 | id | const | deleted_at | vconst | 
 + -------- + ------- + --------------------- + ------ -  + 
 | 999901 |鬼| 2017-02-16 22:07:45 | NULL | 
 | 999903 |鬼| NULL | 1 | 
 + -------- + ------- + --------------------- + ------ -  + 
 2行集（0.01秒）
  

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

I am looking for how to implement unique constraints with NULL check.

MySQL shouldn't allow multiple null value.

Employee:

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

解决方案

MySQL 5.7 does allow for a workaround:

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)

With soft deletes, it would be nice if you could have just one row with a with a deleted_at = NULL per constraint.

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

So I created a virtual column that will flip from 1 to null when deleted_at gets set.

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

Instead of including deleted_at to the unique constraint add the virtual column, 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)

No need to insert the vconst (but you cannot, anyhow).

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

Inserting it again throws the Duplicate entry error.

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

Same with setting delete_at, no need to touch vconst, it will flip automatically.

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)

Now you are free to insert a new row with the same constraints!

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)

In this case, depending on how much you soft delete, setting deleted_at, you might want to include deleted_at to the index, or a new index with it, but I will let my load tests decide.

这篇关于如何在MySQL中使用NULL值来做唯一的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！