代码之家 › 专栏 › 技术社区 › Jason Swett

具有空值的唯一键

relational-model null database mysql

Jason Swett · 技术社区 · 14 年前

这个问题需要一些假设的背景。让我们考虑 employee 具有列的表 name , date_of_birth title , salary ,使用MySQL作为RDBMS。因为如果某个人的名字和出生日期与另一个人相同,那么根据定义,他们就是同一个人(除非我们有两个叫亚伯拉罕·林肯的人在1809年2月12日出生,这是惊人的巧合),我们会把一把独特的钥匙和这意味着“不要存储同一个人两次”。现在考虑以下数据:

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

我的问题是: 我怎样才能防止重复 出生日期 不是一直都知道吗? 到目前为止我能想到的最好的办法就是搬家 出生日期 换一张桌子。不过,问题是,我可能最终会遇到两个收银员,他们的名字、头衔和薪水都是一样的,出生日期也不一样,如果没有副本,就无法存储这两个数据。

8 回复 | 直到 14 年前

NealB 14 年前

基本性质是吗它一定是独一无二的。使该键的一部分可为空会破坏此属性。

有两种可能的解决方案:

一种方法,错误的方法,是用一些神奇的日期来表示未知。你就这样过去了预计两个“John Smith”条目的日期未知时会出现问题如果你知道他们是不同的,那么你又回到了原来的问题上- 你唯一的钥匙不是唯一的。甚至不要考虑指定一系列神奇的约会代表“未知”-这才是真正的地狱之路。
分配给您的个人的任意标识符是独一无二的。这个标识符通常只是一个整数值。关键)在这种情况下,你认为是受抚养人的归属姓名和出生日期(其中任何一个都可能无效)。使用EmployeeId代理项密钥以前用过的名字/出生日期。这将向您的系统添加一个新表,但是以稳健的方式解决未知值问题。

Mark Byers 14 年前

我想MySQL就是在这里做的。其他一些数据库(例如Microsoft SQL Server)将NULL视为只能插入唯一列一次的值,但我个人认为这是一种奇怪和意外的行为。

HLGEM 14 年前

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

如果要插入员工数据以唯一地标识每个员工,最好的方法是使用员工ID。然后检查用户界面中的uniquename,如果有一个或多个匹配项,询问用户是否是指这些匹配项,如果他拒绝,则插入记录。然后构建一个deupping进程,如果有人意外地被分配了两个id,则修复问题。

Mike Lue 14 年前

我们将列命名为 出生日期 并创建唯一的约束雇员(姓名、出生日期)。因此,当两个recored具有相同的名称和空的出生日期值时,唯一约束仍然有效。

但对于同一意义的两个栏目的维护力度,以及新栏目的性能危害,都应慎重考虑。

marc_s 7 年前

我建议创建其他表列 checksum 它将包含 name date_of_birth . 删除唯一键 (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;

Paul 13 年前

完美的解决方案是支持基于函数的UK,但这变得更加复杂,因为mySQL还需要支持基于函数的索引。这将避免使用“假”值代替空值,同时也允许开发人员决定如何在英国处理空值。不幸的是,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() 唯一键定义中的函数)

kingledion 8 年前

我有一个类似的问题,但有一个扭曲。在你的情况下,每个员工都有一个生日,尽管这可能是未知的。在这种情况下,系统为生日未知但信息完全相同的员工分配两个值是合乎逻辑的。尼尔布公认的答案非常准确。

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

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

romor A K 5 年前

您可以添加生成的列,其中 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
);

-2

Lordferrous 10 年前

简而言之 唯一约束 是使字段或列。这个 无效的 销毁此属性,因为数据库将空视为

为了避免重复并允许空值:

将唯一键设为