带有 NULL 的唯一键

Question

这个问题需要一些假设的背景。让我们考虑一个 employee 表，其中包含列 name、date_of_birth、title、salary ，使用 MySQL 作为 RDBMS。因为如果任何给定的人与另一个人具有相同的名字和出生日期，那么根据定义，他们是同一个人（除非有令人惊奇的巧合，我们有两个人都名叫亚伯拉罕·林肯，出生于 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 code> 到不同的表。然而，这样做的问题是，我最终可能会遇到两个收银员，他们的姓名、头衔和工资相同，出生日期不同，而且无法在不重复的情况下存储他们。

Answer 1

唯一密钥的一个基本属性是
它必须是独一无二的。将该键的一部分设置为 Null 会破坏此属性。

您的问题有两种可能的解决方案：

• 一种方法，错误的方法，是使用一些神奇的日期来表示未知。这只会让你过去
  DBMS 的“问题”，但并没有从逻辑意义上解决问题。
  预计两个日期未知的“John Smith”条目会出现问题
  出生的。这些人是同一个人还是独一无二的个体？
  如果你知道它们是不同的，那么你又回到了同样的老问题 -
  您的唯一密钥并不唯一。甚至不要考虑分配整个范围的神奇日期
  代表“未知”——这确实是通往地狱的道路。

• 更好的方法是创建 EmployeeId 属性作为代理键。这只是一个
  您分配给您知道唯一的个人的任意标识符。这
  标识符通常只是一个整数值。
  然后创建一个 Employee 表来关联 EmployeeId（唯一的、不可为空的
  key）到您认为的依赖属性，在这种情况下
  姓名和出生日期（其中任何一项都可以为空）。在您所在的任何地方使用 EmployeeId 代理键
  以前使用过姓名/出生日期。这会向您的系统添加一个新表，但是
  以稳健的方式解决了未知值的问题。

Answer 2

我认为 MySQL 就在这里做到了。其他一些数据库（例如 Microsoft SQL Server）将 NULL 视为只能在 UNIQUE 列中插入一次的值，但我个人认为这是奇怪且意外的行为。

但是，由于这是您想要的，因此您可以使用一些“神奇”值而不是 NULL，例如过去很长时间的日期

Answer 3

我建议创建额外的表列 checksum，其中包含 name 和 date_of_birth 的 md5 哈希值。删除唯一键 (name, date_of_birth) 因为它不能解决问题。在校验和上创建一个唯一的密钥。

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;

Answer 4

您的基于名称不重复的问题无法解决，因为您没有自然键。为出生日期未知的人输入虚假日期并不能解决您的问题。出生于 1900/01/01 的约翰·史密斯与出生于 1960/03/09 的约翰·史密斯仍然是不同的人。

我每天都会处理来自大大小小的组织的姓名数据，我可以向您保证，他们始终有两个不同的人具有相同的名字。有时具有相同的职称。出生日期也不能保证唯一性，许多约翰·史密斯出生在同一天。当我们使用医生办公室数据时，我们经常有两名医生具有相同的姓名、地址和电话号码（父子组合）。

如果您要插入员工数据来唯一地识别每个员工，最好的选择是拥有员工 ID。然后检查用户界面中的唯一名称，如果有一个或多个匹配项，则询问用户是否是这个意思，如果他说不是，则插入该记录。然后构建一个 deupping 流程来修复某人意外分配了两个 id 时出现的问题。

Answer 5

还有另一种方法可以做到这一点。添加一列（不可为空）来表示 date_of_birth 列的字符串值。如果 date_of_birth 为 null，则新列值将为“”（空字符串）。

我们将该列命名为date_of_birth_str，并创建一个唯一约束employee(name, date_of_birth_str)。因此，当两条记录具有相同名称且 date_of_birth 值为空时，唯一约束仍然有效。

但对于两个相同含义的色谱柱的维护工作量以及新色谱柱的性能危害，应慎重考虑。

Answer 6

您可以添加一个生成列，其中 NULL 值被未使用的常量（例如零）替换。然后您可以对该列应用唯一约束：

CREATE TABLE employee ( 
  name VARCHAR(50) NOT NULL, 
  date_of_birth DATE, 
  uq_date_of_birth DATE AS (IFNULL(date_of_birth, '0000-00-00')) UNIQUE
);

Answer 7

完美的解决方案是支持基于函数的 UK，但这会变得更加复杂，因为 mySQL 还需要支持基于函数的索引。这将防止需要使用“假”值来代替 NULL，同时还允许开发人员决定如何处理 UK 中的 NULL 值。不幸的是，据我所知，mySQL 目前不支持此类功能，因此我们只能采取解决方法。

CREATE TABLE employee( 
 name CHAR(50) NOT NULL, 
 date_of_birth DATE, 
 title CHAR(50), 
 UNIQUE KEY idx_name_dob (name, IFNULL(date_of_birth,'0000-00-00 00:00:00'))
);

（注意在唯一键定义中使用 IFNULL() 函数）

Answer 8

我遇到了与此类似的问题，但有所不同。就您而言，每个员工都有生日，尽管可能未知。在这种情况下，系统为生日未知但其他信息相同的员工分配两个值是合乎逻辑的。 NealB 接受的答案非常准确。

然而，我遇到的问题是数据字段不一定有值。例如，如果您在表中添加了“name_of_spouse”字段，则表的每一行不一定都有一个值。在这种情况下，NealB 的第一个要点（“错误的方式”）实际上是有道理的。在这种情况下，对于没有已知配偶的每一行，应在 name_of_spouse 列中插入字符串“None”。

我遇到这个问题的情况是编写一个带有数据库的程序来对 IP 流量进行分类。目标是创建专用网络上的 IP 流量图表。每个数据包都被放入一个数据库表中，该表具有基于其 IP 源和目标、端口源和目标、传输协议和应用程序协议的唯一连接索引。然而，许多数据包根本没有应用程序协议。例如，所有没有应用协议的 TCP 数据包应归为一类，并且应在连接索引中占据一个唯一的条目。这是因为我希望这些数据包形成我的图的单个边。在这种情况下，我采纳了上面的建议，并在应用程序协议字段中存储了一个字符串“None”，以确保这些数据包形成一个唯一的组。

Answer 9

我正在寻找一种解决方案，Alexander Yancharuk 建议对我来说是个好主意。但就我而言，我的列是外键，employee_id 可以为空。

我有这样的结构：

+----+---------+-------------+
| id | room_id | employee_id |
+----+---------+-------------+
|  1 |       1 | NULL        |
|  2 |       2 | 1           |
+----+---------+-------------+

并且带有employee_id NULL的room_id不能重复

我解决了在插入之前添加触发器的问题，如下所示：

DELIMITER $
USE `db`$
CREATE DEFINER=`root`@`%` TRIGGER `db`.`room_employee` BEFORE INSERT ON `room_employee` FOR EACH ROW
BEGIN
    IF EXISTS (
            SELECT room_id, employee_id
            FROM room_employee
            WHERE (NEW.room_id = room_employee.room_id AND NEW.employee_id IS NULL AND room_employee.employee_id IS NULL)
        ) THEN
        CALL `The room Can not be duplicated on room employee table`;
    END IF;
END$
DELIMITER ;

我还添加了room_id和<的唯一约束强>employee_id

Answer 10

我认为这里的根本问题是你真正的意思

INSERT INTO 员工（姓名、职务、薪水）值（“Jim Johnson”、“办公室经理”、“40,000”）

您自己对人的定义是姓名和出生日期，那么此语句在该上下文中意味着什么？我想说，解决你的问题的方法是通过在你的 name 和 date_of_birth 列上添加 NOT NULL 来禁止插入一半身份，就像上面的那样。这样，该语句将失败并强制您输入完整的身份，并且唯一密钥将发挥作用以防止您两次输入同一个人。

Answer 11

简单来说，唯一约束的作用就是使字段或列。
null 会破坏此属性，因为数据库将 null 视为未知

为了避免重复并允许 null：

将唯一键设为主键