Обновление таблицы с уникальным столбцом

Question

Обновление таблицы с уникальным столбцом

Таблица содержит следующие данные, использует INNODB, имеет UNIQUE ограничение на position/fkи не позволяет NULL за 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}, ... ], так далее.

+----+----------+-----+
| 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; не позволит временно отключить уникальные проверки, и не хочет фактически удалять уникальный индекс, обновлять таблицу и повторно применять уникальный индекс.

Как можно обновить эту таблицу?

Если нет простых способов сделать это, я подумал о нескольких вариантах, но они мне не нравятся:

позволяющий NULL в position, Есть ли способ временно разрешить NULL похоже на то, как SET FOREIGN_KEY_CHECKS=0; можно отключить внешние ключи?
Сначала удалите все записи, а затем вставьте их заново. Это может привести к проблемам с производительностью, поскольку в таблице есть индексы, которые необходимо будет воссоздать.

1

mysql php reorderlist unique-constraint

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Все, что я могу подумать, это то, что вам нужно сначала изменить все позиции на некоторые другие значения, которые не находятся в диапазоне новых значений позиции, которые вам в конечном итоге нужно установить, но которые все еще уникальны в строках.

Простой способ сделать это, предполагая, что ваш столбец позиции представляет собой целое число со знаком, — установить для всех позиций их противоположное (отрицательное) значение. Они останутся уникальными, но их не будет в наборе новых значений.

Вы можете сделать это в транзакции вместе с вашими последующими обновлениями, поэтому никакая другая параллельная транзакция никогда не увидит отрицательные значения.

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

Это взломать Знаковый бит целого числа используется для целей, отличных от показа отрицательных чисел.

2