MySQL Unique约束，更新时，将exiting设置为NULL

Question

我确信有一种方法可以搜索它来找到现有的答案，但大多数结果要么通过阻塞更新来强制使用唯一列，要么在两行之间交换唯一值(这显然在MySQL中是错误的)。我不想交换，我只想将当前具有唯一值的行更新为NULL。

下面是我如何使用两个查询(使用一个包含两列的表，其中第二列person_id必须是唯一的)：

UPDATE bathroom_occupant
SET person_id = NULL
WHERE person_id = '$person_id';

UPDATE bathroom_occupant
SET person_id = '$person_id' 
WHERE bathroom_id = '$new_bathroom_id';

上面的代码将person_id从当前的bathroom_id配对中删除，然后将person_id放入新的bathroom_id配对中。我希望只有一个查询，它要么使用某种形式的COALESCE将一行设置为NULL，另一行设置为person_id，要么使用某种表级规则，我认为这类似于unique约束，其中单独运行第二个查询会自动将已经设置为唯一值的任何其他行的列值设置为NULL。

举一个更简单的例子:我知道可以使用唯一的约束来阻止学生注册同一班级的两个部分。但是，与其通过阻塞来阻止，还有可能通过“移动”来阻止吗？

Answer 1

这比我想象的要棘手；首先，我按照acfrancis在注释中的建议研究了触发器的使用，但在MySQL触发器中，您不能更新触发器所在的表-因此INSERT触发器和update触发器都被排除在外。如果您的DBMS支持它，这将是一种可能性。

因此，您需要使用约束概念，并且您需要考虑您正在限制的是什么。如果您希望确保一个人不会被记录为在多个浴室中，则可以添加相关的约束，如下所示：

mysql> DROP TABLE IF EXISTS bathroom_occupant;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bathroom_occupant (bathroom_id INT, person_id INT);
Query OK, 0 rows affected (0.12 sec)

mysql> ALTER TABLE bathroom_occupant ADD CONSTRAINT
    -> UNIQUE INDEX person_id (person_id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后1号人进入1号浴室，2号人进入2号浴室

mysql> INSERT INTO bathroom_occupant (bathroom_id, person_id) VALUES (1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO bathroom_occupant (bathroom_id, person_id) VALUES (2, 2);
Query OK, 1 row affected (0.01 sec)

然后，当您想要更新一个人所在的卫生间时，可以使用INSERT查询并添加一个ON DUPLICATE KEY update语句。下面的人1移动到浴室2

mysql> INSERT INTO bathroom_occupant (bathroom_id, person_id) VALUES (2, 1)
    -> ON DUPLICATE KEY UPDATE bathroom_id = VALUES(bathroom_id);
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT * FROM bathroom_occupant;
+-------------+-----------+
| bathroom_id | person_id |
+-------------+-----------+
|           2 |         1 |
|           2 |         2 |
+-------------+-----------+
2 rows in set (0.00 sec)

正如您所看到的，有两个关于浴室2的记录-因为我们的约束只是一个人不能在多个浴室中。在现实世界中，这是可以的--两个人可以进入同一个浴室。我认为它适用于你的另一个例子-学生注册到同一班级的两个部分(许多学生可以在同一部分，但一个学生只能在一个部分)。

然而，您将您的桌子称为bathroom_occupant -对我来说，这个名称意味着这个表格显示了每个浴室的占有者-也许您希望每个浴室只有一个占有者。如果这是你需要的--即你限制每个人只能在一个浴室里，并且每个浴室只能容纳一个人-那么这就变得更加困难了。我认为，当人1进来时，把人2踢出浴室2的逻辑超出了MySQL在内部所能做的事情。如果要在表上添加另一个约束，这一次是针对bathroom_id，那么插入将会失败，因为on DUPLICATE KEY UPDATE语句只修复了一条记录的问题。

如果你很高兴多个人可以在一个浴室里，那么无论什么时候你想把人放进浴室，你都可以使用下面的代码：

INSERT INTO bathroom_occupant (bathroom_id, person_id) 
VALUES ('$new_bathroom_id', '$person_id')
ON DUPLICATE KEY UPDATE bathroom_id = VALUES(bathroom_id);

这只是一个查询--根据请求。:-)