更新具有唯一列的表 [英] Updating table with unique column

问题描述

一个表包含以下数据，正在使用INNODB，在position/fk上具有UNIQUE约束，并且不允许NULL用于position.

A table contains the following data, is using INNODB, has a UNIQUE constraint on position/fk, and doesn't allow NULL for position.

+----+----------+-----+
| id | position | fk  |
+----+----------+-----+
|  1 |        1 | 123 |
|  2 |        2 | 123 |
|  3 |        3 | 123 |
|  4 |        4 | 123 |
|  5 |        5 | 123 |
|  6 |        6 | 123 |
|  7 |        7 | 123 |
|  8 |        8 | 123 |
|  9 |        9 | 123 |
| 10 |       10 | 123 |
+----+----------+-----+

PHP收到将表更新为以下内容的请求.可以提供最方便的请求格式，例如[2,1,4,3,6,5,8,7,10,9]或[{"id":1, "position":2}, ... ]等.

PHP receives a request to update the table to the following. The format of the request can be provided how ever is most convenient such as [2,1,4,3,6,5,8,7,10,9] or [{"id":1, "position":2}, ... ], etc.

+----+----------+-----+
| id | position | fk  |
+----+----------+-----+
|  1 |        2 | 123 |
|  2 |        1 | 123 |
|  3 |        4 | 123 |
|  4 |        3 | 123 |
|  5 |        6 | 123 |
|  6 |        5 | 123 |
|  7 |        8 | 123 |
|  8 |        7 | 123 |
|  9 |       10 | 123 |
| 10 |        9 | 123 |
+----+----------+-----+

我已经确认SET unique_checks=0;不允许暂时禁用唯一检查，并且不希望实际删除唯一索引，更新表并重新应用唯一索引.

I've confirmed that SET unique_checks=0; will not allow unique checks to be temporarily disabled, and don't wish to actually remove the unique index, update the table, and reapply the unique index.

该表如何更新?

如果没有简单的方法可以做到，我想到了几个选择，但不喜欢它们:

If there is no simple means to do so, I thought of a couple of options, but don't like them:

1. 允许position中的NULL.有什么方法可以像SET FOREIGN_KEY_CHECKS=0;禁用外键一样临时允许NULL?
2. 首先删除所有记录，然后重新插入它们.这可能会导致性能问题，因为表上有一些索引需要重新创建.

1. Allowing NULL in position. Is there a way to temporarily allow NULL similar to how SET FOREIGN_KEY_CHECKS=0; can disable foreign keys?
2. First delete all the records and then reinsert them. This might result in performance issues as there are indexes on the table which will need to be recreated.

推荐答案

我能想到的是，您需要首先将所有头寸更改为其他一些最终不在新头寸值范围内的值设置，但在行中仍然是唯一的.

All I can think is that you need to first change all the positions to some other values that aren't in the range of new position values you ultimately need to set, but are still unique within the rows.

假设位置列是有符号整数，一种简单的方法是将所有位置设置为相反(负)值.它们将保持唯一，但不会出现在新值的集合中.

An easy way to do this, assuming your position column is a signed integer, is to set all the positions to their opposite (negative) value. They'll remain unique, but they won't be in the set of the new values.

您可以在事务中以及随后的更新中执行此操作，因此，其他任何并发事务都不会看到负值.

You can do this in a transaction along with your subsequent updates, so no other concurrent transaction will ever see the negative values.

BEGIN;
UPDATE MyTable SET position = -position;
UPDATE MyTable SET position = 2 WHERE id = 1;
...etc... 
COMMIT;

这是一个hack.整数的符号位用于显示负数以外的目的.

This is a hack. The sign bit of the integer is being used for a purpose other than showing negative numbers.

这篇关于更新具有唯一列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！