MySQL暂时禁止唯一索引。

Question

我有一张表，其中id_parent和sort_order这两列具有唯一索引。

+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

现在我想一次性更新它们的数据和排序（sort_order）。例如，将 sort_order 从 1 - 2 - 3 更改为 2 - 3 - 1。

但是，当我开始运行更新语句时，唯一索引会阻止我，正如预期的那样，它会说我不能有两行具有 id_parent = 1 and sort_order = 2。好吧，我现在可以将其设置为4，按正确顺序更新其他行，然后再设置这个值。但接下来，我需要运行一条额外的语句，并且很可能需要添加额外的逻辑到我的脚本语言中以确定更新的正确顺序。我还使用 ORM，这使得事情变得更加不方便。

现在我的问题是，是否有一种方法可以使 mysql 暂时忽略此索引？就像启动一个特殊的事务，在提交之前仅计算索引一样？

- Nameless

---

你的表是InnoDB还是MyISAM？ - Pacerier

删除唯一键：https://dev59.com/XGox5IYBdhLWcg3wf0VP#9172188。稍后再添加回去。 - Bishwas Mishra

Answer 1

据我所知，这是不可能的。唯一一次我看到过类似的情况是你可以禁用MyISAM表上的非唯一键，但不能在InnoDB和唯一键上禁用。

然而，为了节省更新操作，你并不需要确切的数字1、2和3。你也可以使用4、5和6。对吧？你只需在order by子句中使用它，因此确切的数字并不重要。如果你足够聪明，甚至可以节省一个更新操作。根据你的例子来说。

update table set sort_order = 4 where sort_order = 1 and id = 1 and id_parent = 1;

新的排序顺序为2、3、1。只需一次更新即可完成。

- Andreas Wederbrand

---

我真丢人，4-5-6这么显而易见的东西都没用到。 - Nameless

同意多次更新是不可能的。但是在单个语句中更新它们可能会起作用。无论哪种方式，对于一个清晰简单的解决方案都是+1。 - Andriy M

Answer 2

“但是当我开始运行更新语句时……”—— 我理解，您尝试使用多个UPDATE语句（例如在循环中）更新值。是这样吗？那么一次性更新它们怎么样呢？就像这样：

UPDATE atable
SET sort_order = CASE sort_order WHEN 3 THEN 1 ELSE sort_order + 1 END
WHERE id_parent = 1
  AND sort_order BETWEEN 1 AND 3

一条语句是原子性的，因此在更新结束时，sort_order 的值虽然已更改，但仍保持唯一。

很抱歉我无法在 MySQL 中进行测试，但它肯定适用于 SQL Server，并且我相信该行为符合标准。

- Andriy M

---

没有考虑过。不过使用ORM会很麻烦。这个回答比Andreas的回答更接近正确答案，但我可能最终会采用他的建议。在这种情况下，我应该选择哪个答案？是选择我要使用的答案，还是更接近主题的答案？ - Nameless

好的，如果有多个答案与“问题”匹配，您应该选择适用于解决“实际问题”的答案。因此，您应该选择Andreas的答案。当然，可能还存在其他考虑因素。无论如何，我都支持您的决定！ - Andriy M

如果行没有按正确顺序更新，那么这种方法是行不通的。在执行期间不能违反唯一键限制。如果没有降序排列，update sorttest set sort = sort+1 order by sort desc; 将无法正常工作，即使第二行即将移动到正确位置，第一行也会违反键限制。 - Andreas Wederbrand

@AndreasWederbrand：感谢您的反馈。关于MySQL，我无法发表任何意见，但我的查询在SQL Server中确实有效。您的示例也可以正常工作，只是没有ORDER BY，因为SQL Server不允许在UPDATE语句中使用ORDER BY。您说的唯一列约束在执行过程中确实被违规了，但是（在SQL Server中），该约束直到语句完成更新后才生效。由于最终数据不包含任何重复项，因此不会出现异常，也不会有回滚。 - Andriy M

一次性更新在Oracle中也很好用。我对Mysql感到沮丧。 - DylanYi

Answer 3

MyISAM

对于 MyISAM 表，你可以在脚本的开头添加以下语句：

SET UNIQUE_CHECKS=0;

通常与此同时使用的是：

SET FOREIGN_KEY_CHECKS=0;

在这里提到了UNIQUE_CHECKS变量：http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html InnoDB：有报告称上述命令在InnoDB引擎中不起作用。如果是这样，您可以尝试暂时删除UNIQUE索引，然后再将其添加回去。

参见：如何从mysql表中删除唯一键。

如果您有代码示例，请随时编辑此帖子并改进它。

- Simon East

---

在5.6上尝试过了，与InnoDB不兼容。重复键仍会抛出错误。 - bluecollarcoder

唯一键位于不引用任何其他表的列上，因此FOREIGN_KEY_CHECKS在这里是无关紧要的。而且，具有唯一键的列绝对不是主键的一部分。在什么情况下，您能够使UNIQUE_CHECKS=0在这种情况下按预期工作？ - bluecollarcoder

来自Mysql文档的引用：unique_checks允许但不要求存储引擎忽略重复键。看起来Innodb不支持此功能。 - bluecollarcoder

@Simon，我也无法使用（Windows 8.1，MySQL 5.6.22）。我们如何强制unique_checks=0的行为？ - Pacerier

SET UNIQUE_CHECKS=0；不会帮助您暂时禁止唯一键。如果设置为0，则允许存储引擎假定输入数据中不存在重复键。如果您确定数据不包含唯一性违规，可以将其设置为0。请注意，将此变量设置为0不需要存储引擎忽略重复键，如果检测到任何重复键仍将发出重复键错误。 - SwR