mysql唯一约束对空值失败？

19 浏览
0 Comments

mysql唯一约束对空值失败？

这个问题需要一些假设的背景。让我们考虑一个employee表，它有name、date_of_birth、title、salary这些列，使用MySQL作为关系数据库管理系统。由于如果任何一个人的姓名和出生日期与另一个人相同，根据定义，它们是同一个人（除非有两个人名叫亚伯拉罕·林肯，出生于1809年2月12日的惊人巧合），我们将在name和date_of_birth上设置一个唯一键，意思是“不要存储同一个人两次”。现在考虑以下数据：

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000

如果我现在尝试运行以下语句，它应该并且将会失败：

INSERT INTO employee (name, date_of_birth, title, salary)
VALUES ('Tim Smith', '1899-04-11', 'Janitor', '95,000')

如果我尝试以下语句，它将成功：

INSERT INTO employee (name, title, salary)
VALUES ('Jim Johnson', 'Office Manager', '40,000')

现在我的数据将会变成这样：

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000
 5 Jim Johnson NULL          Office Manager  40,000

这不是我想要的，但我不能说我完全不同意发生的事情。如果我们用数学集合的术语来讨论，

{'Tim Smith', '1899-04-11'} = {'Tim Smith', '1899-04-11'} <-- TRUE
{'Tim Smith', '1899-04-11'} = {'Jane Doe', '1982-05-05'} <-- FALSE
{'Tim Smith', '1899-04-11'} = {'Jim Johnson', NULL} <-- UNKNOWN
{'Jim Johnson', NULL} = {'Jim Johnson', NULL} <-- UNKNOWN

我猜MySQL说："由于我不知道带有NULL出生日期的Jim Johnson是否已经在这个表中，所以我会添加他。"

我的问题是：我如何防止重复，即使date_of_birth不总是已知的？我目前想到的最好的方法是将date_of_birth移到另一个表中。然而，这个方法的问题是，我可能会遇到两个收银员，他们的姓名、职位和薪水相同，出生日期不同，没有办法在没有重复的情况下存储它们两个。

0

3 答案

0 Comments

问题的原因是：在MySQL中，唯一约束（unique constraint）要求列中的值是唯一的，但是如果有列的值为NULL，就会导致唯一约束失败。

解决方法是：创建一个额外的列checksum，该列包含name和date_of_birth的MD5哈希值。删除(name, date_of_birth)的唯一约束，因为它不能解决问题。然后在checksum列上创建一个唯一约束。具体的操作如下：

ALTER TABLE employee 
    ADD COLUMN checksum CHAR(32) NOT NULL;
UPDATE employee 
SET checksum = MD5(CONCAT(name, IFNULL(date_of_birth, '')));
ALTER TABLE employee 
    ADD UNIQUE (checksum);

这个解决方案会带来一些技术开销，因为对于每个插入的数据对，都需要生成哈希值（对于每个搜索查询也是如此）。为了进一步改进，可以添加一个触发器，在每次插入时自动生成哈希值，具体操作如下：

CREATE TRIGGER before_insert_employee 
BEFORE INSERT ON employee
FOR EACH ROW
    IF new.checksum IS NULL THEN
      SET new.checksum = MD5(CONCAT(new.name, IFNULL(new.date_of_birth, '')));
    END IF;

还可以考虑将checksum作为一个生成列（generated column）存储。具体操作如下：

ALTER TABLE employee 
    ADD COLUMN checksum CHAR(32) AS (MD5(CONCAT(name, IFNULL(date_of_birth, '')))) STORED;

在我遇到类似需要生成checksum的情况下，使用生成列对我来说效果很好。

0
0 Comments

在MySQL中，当在一个UNIQUE约束列中插入NULL值时，会出现唯一约束失败的问题。然而，有些数据库（例如Microsoft SQL Server）将NULL视为只能在UNIQUE列中插入一次的值，但是个人认为这种行为很奇怪且不符合预期。

然而，如果你确实需要这种行为，可以使用一些“魔法”值来代替NULL，比如一个很久以前的日期。

我并不完全不同意MySQL在处理这个问题时的正确性。然而，最终的结果并不符合我的期望：我最终会得到重复的值，这是不可接受的。对我来说，“魔法”值只是一个“假NULL”。不是有什么冒犯的意思，但我发现很难接受这是正确的做法。

此外，我关心的并不是NULL出现两次的问题，而是"Jim Johnson"出现两次的问题。

PostgreSQL也和MySQL一样（null值是非唯一的），并且声称这种行为符合SQL标准。

这并没有一种方式是对的或错误的。根据具体情况，你需要选择一种方式。这应该是可以配置的。在Java中也犯了类似的设计错误，所有的引用类型都可以为null，而实际上它们需要两种方案。人们不断进行这些拉扯战，变得荒谬。最终，你只会得到出厂时只有一个极性的电池和一些非常尴尬的面孔。

对我来说，使用魔法值理论上是不可行的，尤其是如果字段是一个外键。实际上，外键约束通常不允许这样做。此外，对我来说，通过表中的另一个字段来驱动可为空字段的适用性是可以接受的。请参见我对问题的评论。

0
0 Comments

问题的出现原因：

一个唯一键的基本属性是必须是唯一的。如果将该键的一部分设置为可空，则会破坏这个属性。如果在唯一键中存在空值，将导致MySQL报错"mysql unique constraint fails with null values"。

解决方法：

有两种可能的解决方法：

1. 第一种方法是使用某个特殊的日期来表示未知值，这只是让DBMS“忽略”了这个问题，但并没有在逻辑上解决问题。在有多个“John Smith”但出生日期未知的情况下，可能会出现问题。你无法确定这些人是同一个人还是不同的人，所以唯一键不再是唯一的。不要考虑给未知值分配一个特定的日期范围，这是一条通向地狱的道路。

2. 更好的方法是创建一个EmployeeId属性作为替代键（surrogate key）。这只是一个你知道是唯一的任意标识符，通常是一个整数值。然后创建一个Employee表，将EmployeeId（唯一、非空键）与你认为是相关属性（如姓名和出生日期）关联起来，这些属性可以是可空的。在以前使用姓名/出生日期的地方都使用EmployeeId替代键。这样可以增加一个新表到系统中，但可以以健壮的方式解决未知值的问题。

通过规范化（Normalization）来解决问题。这在大多数情况下是整个问题的关键。如果你在问这个问题，那就说明你需要规范化。

需要注意的是，并不是总是将null约定为未知状态，而是通常将null用作未提供可选值的状态，它是一种定义好的状态。DBMS应该允许用户决定如何处理null与唯一键的关系。举个例子，考虑一个包含国家、温度和位置的天气表，其中位置是可选的。你可能需要根据国家或更具体的位置收集温度，但不希望对于仅有国家或国家和位置的组合超过一个条目。在MySQL中，只用一个表很难实现这一点，这令人失望。

实际上，如果已知数据集存在冲突并且至少有一个未知数据点，那么决定记录是否唯一的问题是无法解决的。添加额外的ID（已经有一个“id”列）并将其作为键的一部分并不能改变这个问题，只是将决策转移到分配该ID的人那里。

0