MySQL问题 - 唯一键不起作用，还是我理解有误？

Question

我正在尝试创建一个关系，其中可能包含四个不同的部分，但是相同部分的任何集合都应该被视为唯一。

例如： 一个任务必须有一个指定的公司，可以选择地拥有一个指定的位置、工作组和程序。 一个任务如果没有位置，就不能有工作组。

假设我们有公司 A、B、C；位置 X、Y、Z；工作组 I、J、K 和程序 1、2、3。

因此，有效的关系可能包括 A - X - I - 1 A - Z - 2 B - Y C C - 3 B - Z - K

但无效的关系将包括 A - K（没有位置的工作组） Y - K - 1（没有公司）

因此，为了创建我的表，我已经创建了

companyID INT NOT NULL,
FOREIGN KEY companyKEY (companyID) REFERENCES company (companyID),
locationID INT,
FOREIGN KEY locationKEY (locationID) REFERENCES location (locationID),
workgroupID INT,
FOREIGN KEY workgroupKEY (workgroupID) REFERENCES workgroup (workgroupID),
programID INT,
FOREIGN KEY programKEY (programID) REFERENCES program (programID),
UNIQUE KEY companyLocationWorkgroupProgramKEY (companyID, locationID, workgroupID, programID)

我认为这个方案能够处理除了如果存在工作组则需要分配位置之外的所有关系（我可以通过编程或触发器来完成这个步骤）

然而，当我测试这个架构时，它允许我输入以下内容...

INSERT INTO test VALUES (1, null, null, null), (1, null, null, null);

...没有投诉。我猜测 (1, null, null, null) 不等于其本身，因为包括了空值。如果是这样的话，有没有办法处理这个关系呢？

感激不尽！

- Drew Arrigoni

Answer 1

这是一个特性（虽然不是我预期的）。

这个主题建议把你的键设为主键，来得到你期望的行为：

这是一个特性 - NULL 值是未定义的值，因此两个 NULL 值并不相同。这可能有点令人困惑，但当你思考它时就会有意义。

UNIQUE 索引确保非 NULL 值是唯一的；你可以指定你的列不接受 NULL 值。

- Michael Haren

---

将列设置为“NOT NULL”会起作用，但也需要像Rob和我建议的添加数据项，以使数据库能够正确匹配所列出的要求-如果不允许NULL，您需要一个在FK引用的FOO表中是有效条目的“NO FOO”值。 - Harper Shelby

关于将索引设为主键：唯一的问题是，除非它是主键的一部分，否则无法创建 AUTO_INCREMENT 列。这就是我遇到的问题。 - Simon East

将可空列作为主键的另一个问题是它们变成了NOT NULL，而默认值变成了0或空字符串。 - Rafa

Answer 2

我能想到的在不需要额外触发器/编程的情况下处理这个问题的唯一方法是，在每个被引用表格中都有一个单独的“无匹配项”值，这样你的测试就会变成：

INSERT INTO test VALUES (1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM),
                        (1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM)

在这里，NO_* 标识符是与您的ID列的类型/长度相匹配的正确类型。如果不匹配，则会失败，正如您所期望的那样。

- Harper Shelby

Answer 3

在 MySQL 中，NULL！= NULL，或其他任何值都不等于 NULL。因此 UNIQUE 不能正常工作。您应该使用另一个默认值来代替空白，比如零。

- Rob

Answer 4

我认为需要注意的是，有一种适当的方式来解释和处理NULL值，而OP表现出的行为正是预期的。你可以忽略这种行为，也可以以任何你想要的方式处理查询，我不会反对，但最好接受一个描述某种最佳实践的答案，而不是非标准的个人偏好。

或者，如果你不同意共识的最佳实践，你可以选择不接受任何答案。

这不是尽快接受答案的竞赛。我认为，审慎和协作也应该是这个过程的一部分。

- le dorfier

Answer 5

我看到这个问题是在2009年提出的。然而，MySQL经常被要求解决此类问题，例如：https://bugs.mysql.com/bug.php?id=8173 和 https://bugs.mysql.com/bug.php?id=17825。人们可以点击“影响我”来尝试引起MySQL的注意。

自从MySQL 5.7以后，我们现在可以使用以下解决方法：

ALTER TABLE test 
ADD generatedLocationID INT AS (ifNull(locationID, 0)) NOT NULL,
ADD generatedWorkgroupID INT AS (ifNull(workgroupID, 0)) NOT NULL,
ADD generatedProgramID INT AS (ifNull(programID, 0)) NOT NULL,
ADD UNIQUE INDEX (companyID, generatedLocationID, generatedWorkgroupID, generatedProgramID);

生成的列是虚拟生成的列，因此它们没有存储空间。当用户插入（或更新）时，唯一索引会导致生成列的值在运行时生成，这是一个非常快速的操作。

- Yoseph