Как поменять значения двух строк в MySQL без нарушения уникального ограничения?

Ответ 1

Можно ли выполнить это в MySQL без использования фиктивных значений и нескольких запросов?

Нет (ничего, о котором я не могу думать).

Проблема в том, как MySQL обрабатывает обновления. MySQL (в отличие от других СУБД, которые реализуют UPDATE правильно), обрабатывает обновления сломанным образом. Он принудительно проверяет ограничения UNIQUE (и другие) после каждого обновления каждой строки, а не - как и должно быть, после завершения инструкции UPDATE. Вот почему у вас нет этой проблемы с (большинством) других СУБД.

Для некоторых обновлений (например, увеличения всех или некоторых идентификаторов, id=id+1) это можно решить, используя другую нестандартную функцию - ORDER BY в обновлении.

Для замены значений из двух строк этот трюк не может помочь. Вам нужно будет использовать NULL или фиктивное значение (которое не существует, но разрешено в вашем столбце) и 2 или 3 оператора.

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

Итак, если уникальный столбец представляет собой целое число со знаком и нет отрицательных значений, вы можете использовать 2 оператора, завернутые в транзакцию:

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;

Ответ 2

Я столкнулся с тем же вопросом. Пробовал каждый возможный запрос с одним оператором, используя CASE WHEN и TRANSACTION - не повезло. Я придумал три альтернативных решения. Вам нужно решить, какой из них имеет больше смысла для вашей ситуации. В моем случае я обрабатываю реорганизованную коллекцию (массив) небольших объектов, возвращенных из front-end, новый порядок непредсказуем (это не сделка с swap-two-items), и, помимо всего прочего, изменение порядок (обычно делается на английском языке) должен распространяться на 15 других языках.

• 1-й метод: Полностью УДАЛИТЬ существующие записи и повторно заполнить всю коллекцию, используя новые данные. Очевидно, что это может работать только в том случае, если вы получаете от front-end все, что вам нужно, чтобы восстановить то, что вы только что удалили.

• 2-й метод: это решение аналогично использованию фиктивных значений. В моей ситуации моя переупорядоченная коллекция также включает исходную позицию позиции перед ее перемещением. Кроме того, я должен был сохранить исходное значение индекса каким-то образом, пока UPDATE выполняются. Хитрость заключалась в том, чтобы манипулировать бит-15 столбца индекса, который в моем случае является UNSIGNED SMALLINT. Если у вас есть (подписанный) тип данных INT/SMALLINT, вы можете просто инвертировать значение индекса вместо побитовых операций.

Сначала UPDATE должен запускаться только один раз за вызов. Этот запрос вызывает 15-й бит текущих полей index (у меня есть unsigned smallint). Предыдущие 14 бит по-прежнему отражают исходное значение индекса, которое никогда не приблизится к диапазону 32K.

UPDATE *table* SET `index`=(`index` | 32768) WHERE *condition*;

Затем итерация вашей коллекции, извлекая оригинальные и новые значения индекса, и ОБНОВЛЯЙТЕ каждую запись отдельно.

foreach( ... ) {
    UPDATE *table* SET `index`=$newIndex WHERE *same_condition* AND `index`=($originalIndex | 32768);
}

Этот последний UPDATE должен также запускаться только один раз за вызов. Этот запрос очищает 15-й бит полей index, эффективно восстанавливая исходное значение индекса для записей, где он не изменился, если таковые имеются.

UPDATE *table* SET `index`=(`index` & 32767) WHERE *same_condition* AND `index` > 32767;

3. Третий метод - это переместить соответствующие записи во временную таблицу, у которой нет первичного ключа, UPDATE все индексы, а затем переместить все записи обратно в первую таблицу.

Ответ 3

Значение значения Bogus:

Хорошо, поэтому мой запрос аналогичен, и я нашел способ обновления в "одном" запросе. Мой столбец id PRIMARY и position является частью группы UNIQUE. Это мой первоначальный запрос, который не работает для обмена:

INSERT INTO `table` (`id`, `position`)
  VALUES (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

.. но позиция - это целое число без знака, и оно никогда не равно 0, поэтому я изменил запрос на следующее:

INSERT INTO `table` (`id`, `position`)
  VALUES (2, 0), (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

.. и теперь это работает! По-видимому, MYSQL обрабатывает группы значений по порядку.

Возможно, это сработает для вас (не проверено, и я почти ничего не знаю о MYSQL):

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=3 THEN 0 
    WHEN priority=2 THEN 3 
    WHEN priority=0 THEN 2 
END 

WHERE priority IN (2,3,0);

Удачи.

Ответ 4

Не уверен, что это нарушит ограничения, но я пытался сделать что-то подобное и в итоге придумал этот запрос, объединив несколько ответов, которые я нашел:

UPDATE tasks as T1,tasks as T2 SET T1.priority=T2.priority,T2.priority=T1.priority WHERE (T1.task_id,T2.task_id)=($T1_id, $T2_id)

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

Ответ 5

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

CREATE TABLE `tasks` (   `id` int(11) NOT NULL,   `name` varchar(200) DEFAULT NULL,   `priority` varchar(45) DEFAULT NULL,   PRIMARY KEY (`id`,`priority`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

У этого будет индекс первичного ключа как комбинация id и приоритета. вы cna затем меняете значения.

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

Я не вижу здесь каких-либо переменных пользователя или временных переменных. Надеюсь, это решит вашу проблему:)