Mysql временно подавляет уникальный индекс

У меня есть таблица с уникальным индексом для двух столбцов, 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 временно игнорировать этот индекс? Как начать специальную транзакцию, в которой индексы будут вычисляться только до ее совершения?

Ответ 1

Насколько я знаю, это невозможно.

Единственный раз, когда я видел что-то подобное, можно отключить не уникальные ключи в таблицах myisam. Но не на InnoDB, а не на уникальных ключах.

Однако, чтобы сохранить обновление или два, нет необходимости иметь точные числа 1, 2 и 3. У вас также могут быть 4, 5 и 6. Правильно? Вы использовали бы его в порядке и ничего больше, поэтому точные цифры не важны. Это даже спасет вас от обновления, если вы умны. Из вашего примера

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

Новый порядок сортировки - 2, 3, 1. И всего за одно обновление.

Ответ 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, и я считаю, что поведение соответствует стандартам.

Ответ 3

Примечание.. В некоторых комментариях упоминалось, что приведенный ниже ответ не всегда подавляет уникальные ограничения, особенно в InnoDB. Если вы можете улучшить этот ответ, нажмите "Изменить" и сделайте улучшения.

Вы можете просто добавить эту строку в начале вашего script:

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