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

问题描述

我正在寻找如何使用 NULL 检查实现唯一约束.

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

MySQL 不应允许多个空值.

MySQL shouldn't allow multiple null value.

员工:

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

推荐答案

不，根据 SQL-99 规范，MySQL 正在做正确的事情.

No, MySQL is doing the right thing, according to the SQL-99 specification.

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

唯一约束使得不可能提交任何操作将导致唯一键包含任何非空重复值.(允许多个空值，因为空值永远不相等任何东西，甚至是另一个空值.)

A UNIQUE Constraint makes it impossible to COMMIT any operation that would cause the unique key to contain any non-null duplicate values. (Multiple null values are allowed, since the null value is never equal to anything, even another null value.)

如果您使用 UNIQUE 约束但不希望多行具有 NULL，请将列声明为 NOT NULL 并禁止 任何 行具有 NULL.

If you use a UNIQUE constraint but don't want multiple rows with NULL, declare the columns as NOT NULL and prohibit any row from having NULL.

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