고유한 제약 조건을 위반하지 않고 MySQL에서 두 행의 값을 교환하는 방법은 무엇입니까?

고유한 제약 조건을 위반하지 않고 MySQL에서 두 행의 값을 교환하는 방법은 무엇입니까?

우선 순위 열이 있는 "작업" 테이블이 있는데, 이 테이블에는 고유한 제약 조건이 있습니다.

두 줄의 우선순위 값을 바꾸려고 하는데 계속 제약 조건을 위반하고 있습니다.비슷한 상황에서 이 문장을 봤는데 MySQL에는 없었습니다.

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

WHERE priority IN (2,3);

그러면 다음 오류가 발생합니다.

Error Code: 1062. Duplicate entry '3' for key 'priority_UNIQUE'

MySQL에서 가짜 값과 여러 쿼리를 사용하지 않고 이를 수행할 수 있습니까?

편집:

표 구조는 다음과 같습니다.

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

MySQL에서 가짜 값과 여러 쿼리를 사용하지 않고 이를 수행할 수 있습니까?

아니요. (제가 생각할 수 있는 것은 없습니다.)

문제는 MySQL이 업데이트를 처리하는 방식입니다.(MySQL을 구현하는 )UPDATE제대로), 손상된 방식으로 업데이트를 처리합니다.은 수다니합행의 합니다.UNIQUE(및 기타)행 후 및 (으) - 않는 제약 UPDATE문이 완료되었습니다.다른 DBMS에는 이 .

또는 ID 등)의 경우,id=id+1), 은 - 또 비표준 기능 - , an, an, an, an, an, 사용하여 할 수 있습니다.ORDER BY최신 정보입니다.

두 행의 값을 스왑하는 경우에는 해당 트릭이 도움이 되지 않습니다.당신은 사합니다야해를 해야 할 NULL또는 가짜 값(존재하지 않지만 열에 허용됨)과 2개 또는 3개의 문이 있습니다.

당신은 또한 일시적으로 고유한 제약을 제거할 수 있지만, 저는 그것이 정말 좋은 생각이라고 생각하지 않습니다.

---

따라서 고유 열이 부호 있는 정수이고 음수 값이 없으면 트랜잭션으로 묶인 두 개의 문을 사용할 수 있습니다.

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 ;

저는 같은 문제에 부딪혔습니다.CASE WHEN과 TRANSACTION을 사용하여 가능한 모든 단일 문 쿼리를 시도했지만 전혀 실패했습니다.저는 세 가지 대안을 생각해냈습니다.당신은 어떤 것이 당신의 상황에 더 적합한지 결정해야 합니다.저의 경우 프런트 엔드에서 반환된 작은 개체의 재구성된 컬렉션(어레이)을 처리하고 있으며, 새로운 주문은 예측할 수 없으며(이것은 스왑-2개 항목 거래가 아닙니다), 무엇보다 주문 변경(일반적으로 영어 버전으로 작성됨)이 다른 15개 언어로 전파되어야 합니다.

1. 첫 번째 방법: 기존 레코드를 완전히 삭제하고 새 데이터를 사용하여 전체 컬렉션을 다시 채웁니다.이 기능은 프론트엔드에서 방금 삭제한 내용을 복원하는 데 필요한 모든 정보를 수신하는 경우에만 작동할 수 있습니다.

2. 두 번째 방법:이 솔루션은 가짜 값을 사용하는 것과 유사합니다.제 경우, 재주문된 컬렉션에는 이전의 원래 아이템 위치도 포함되어 있습니다.또한 UPDATE가 실행되는 동안 원래 인덱스 값을 어떤 방식으로든 보존해야 했습니다.비결은 제 경우 UNSIGNED SMILINT인 인덱스 컬럼의 비트-15를 조작하는 것이었습니다.INT/SOLMINT 데이터 유형이 있는 경우 비트 연산 대신 인덱스 값을 반전시킬 수 있습니다.

첫 번째 업데이트는 호출당 한 번만 실행해야 합니다. 이쿼번전 15째비발의 .index필드(서명되지 않은 smallint가 있습니다.이전 14비트는 32K 범위에 결코 근접하지 않는 원래 인덱스 값을 여전히 반영합니다.

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

그런 다음 원본 인덱스 값과 새 인덱스 값을 추출하여 컬렉션을 반복하고 각 레코드를 개별적으로 업데이트합니다.

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

이 마지막 업데이트도 호출당 한 번만 실행해야 합니다.는 이쿼번 15째비의 15번째 .index필드는 변경되지 않은 레코드의 원래 인덱스 값을 효과적으로 복원합니다.

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

3. 세 번째 방법은 기본 키가 없는 임시 테이블로 관련 레코드를 이동하고 모든 인덱스를 업데이트한 다음 모든 레코드를 첫 번째 테이블로 다시 이동하는 것입니다.

가짜 값 옵션:

좋아요, 그래서 제 질문도 비슷하고 "하나" 질문으로 업데이트하는 방법을 찾았습니다.id 열이고 열주 열이 고은고입니다.position는 고유 그룹의 일부입니다.스와핑에 사용할 수 없는 원래 쿼리입니다.

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);

행운을 빌어요.

비슷한 문제가 있었습니다.

저는 다른 테이블에서 FK인 고유의 ID 2개를 교환하고 싶었습니다.

두 개의 고유 항목을 교환할 수 있는 가장 빠른 솔루션은 다음과 같습니다.

1. 내 FK 테이블에 고스트 항목을 만듭니다.
2. ID를 변경할 테이블로 돌아갑니다.
3. FK 체크 해제SET FOREIGN_KEY_CHECKS=0;
4. 내 첫 번째(A) ID를 고스트(X) fk(자유의 A)로 설정합니다.
5. 두 번째(B) ID를 A(자유의 B)로 설정
6. A를 B로 설정(자유의 X)
7. 유령 기록을 삭제하고 검사를 다시 켭니다.SET FOREIGN_KEY_CHECKS=1;

대상 열이 고유한 제약 조건인 경우 테이블 행을 쉽게 전환할 수 있는 또 다른 방법이 있습니다.

UPDATE `tasks` AS a
INNER JOIN `tasks` AS b ON a.id <> b.id
  SET a.priority = b.priority
WHERE a.id IN (2,3) AND b.id IN (2,3)

이것이 제약 조건을 위반하는지는 확실하지 않지만, 저는 비슷한 일을 하려고 노력해 왔고 결국 제가 찾은 몇 가지 대답을 조합하여 이 질문을 생각해냈습니다.

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)

스왑할 열에 고유한 열이 사용되지 않았기 때문에 도움이 될지 모르겠습니다...

키 인덱스를 약간 변경하여 위에서 언급한 업데이트 문으로 값을 스왑할 수 있습니다.

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와 우선 순위의 조합으로 기본 키 인덱스를 가집니다.그런 다음 값을 교환할 수 있습니다.

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

WHERE priority IN (2,3);

여기에는 사용자 변수나 임시 변수가 필요하지 않습니다.이것이 당신의 문제를 해결하기를 바랍니다 :)

언급URL : https://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint