[mysql] MySQL에서 열 값 교환

Program / 글쓴이

좌표가있는 MySQL 테이블이 있고 열 이름은 X와 Y입니다. 이제 X에서 Y가되고 Y가 X가되도록이 테이블의 열 값을 바꾸고 싶습니다. 가장 확실한 해결책은 열의 이름을 바꾸는 것이지만 내가 할 수있는 권한이 없기 때문에 구조를 변경하고 싶지 않습니다.

이것은 어떤 방식 으로 UPDATE 와 관련이 있습니까? 업데이트 테이블 SET X = Y, Y = X는 분명히 내가 원하는 것을하지 않습니다.

편집 : 위에서 언급 한 권한에 대한 제한으로 인해 ALTER TABLE 또는 테이블 / 데이터베이스 구조를 변경하는 다른 명령을 효과적으로 사용할 수 없습니다. 열 이름을 바꾸거나 새 열을 추가하는 것은 불행히도 옵션이 아닙니다.

답변

방금 똑같은 문제를 해결해야했으며 내 연구 결과를 요약하겠습니다.

  1. 이 UPDATE table SET X=Y, Y=X방법은 두 값을 모두 Y로 설정하기 때문에 분명히 작동하지 않습니다.

  2. 임시 변수를 사용하는 방법은 다음과 같습니다. “IS NOT NULL”조정에 대한 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ 의 의견에서 Antony에게 감사드립니다 . 이것이 없으면 쿼리가 예기치 않게 작동합니다. 게시물 끝에있는 테이블 스키마를 참조하십시오. 이 방법은 값 중 하나가 NULL 인 경우 값을 교환하지 않습니다. 이 제한이없는 방법 # 3을 사용하십시오.

    UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

  3. 이 방법은 Dipin에서 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/에 대한 의견으로 제공되었습니다 . 가장 우아하고 깨끗한 솔루션이라고 생각합니다. NULL 값과 NULL이 아닌 값 모두에서 작동합니다.

    UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

  4. 내가 생각해 낸 또 다른 접근법은 효과가있는 것 같습니다.

    UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

기본적으로 첫 번째 테이블은 업데이트되는 테이블이고 두 ​​번째 테이블은 이전 데이터를 가져 오는 데 사용됩니다.
이 방법을 사용하려면 기본 키가 있어야합니다.

이것은 내 테스트 스키마입니다.

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);

답변

X와 Y를 사용하여 합을 취하고 반대 값을 뺄 수 있습니다.

UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;

다음은 샘플 테스트입니다 (음수와 함께 작동).

mysql> use test
Database changed
mysql> drop table if exists swaptest;
Query OK, 0 rows affected (0.03 sec)

mysql> create table swaptest (X int,Y int);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO swaptest VALUES (1,2),(3,4),(-5,-8),(-13,27);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|   -5 |   -8 |
|  -13 |   27 |
+------+------+
4 rows in set (0.00 sec)

mysql>

스왑이 수행되고 있습니다.

mysql> UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    2 |    1 |
|    4 |    3 |
|   -8 |   -5 |
|   27 |  -13 |
+------+------+
4 rows in set (0.00 sec)

mysql>

시도 해봐 !!!

답변

다음 코드는 빠른 테스트의 모든 시나리오에서 작동합니다.

UPDATE swap_test
   SET x=(@temp:=x), x = y, y = @temp

답변

업데이트 테이블 SET X = Y, Y = X 는 원하는대로 정확하게 수행합니다 (편집 : MySQL이 아닌 PostgreSQL에서 아래 참조). 이전 행에서 값을 가져와 같은 행의 새 사본에 할당 한 다음 이전 행을 바꿉니다. 임시 테이블, 임시 열 또는 다른 스왑 트릭을 사용하지 않아도됩니다.

@ D4V360 : 알겠습니다. 충격적이고 예상치 못한 일입니다. PostgreSQL을 사용하고 답변이 올바르게 작동합니다 (시도했습니다). SET 절의 오른쪽에있는 표현식이 명시 적으로 이전 열 값을 사용한다고 언급하는 PostgreSQL UPDATE 문서 (매개 변수, 표현식 아래)를 참조하십시오 . 해당 MySQL UPDATE 문서가 “단일 테이블 UPDATE 할당이 일반적으로 왼쪽에서 오른쪽으로 평가됩니다”라는 문장이 포함되어 .

알아 둘만 한.

답변

좋아, 그냥 재미로, 당신은 이것을 할 수 있습니다! (문자열 값을 교환한다고 가정)

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 6    | 1    |
| 5    | 2    |
| 4    | 3    |
+------+------+
3 rows in set (0.00 sec)

mysql> update swapper set
    -> foo = concat(foo, "###", bar),
    -> bar = replace(foo, concat("###", bar), ""),
    -> foo = replace(foo, concat(bar, "###"), "");

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 1    | 6    |
| 2    | 5    |
| 3    | 4    |
+------+------+
3 rows in set (0.00 sec)

MySQL에서 왼쪽에서 오른쪽으로 평가 프로세스를 남용하는 것은 재미있다.

또는 숫자 인 경우 XOR을 사용하십시오. 좌표를 언급 했으므로 멋진 정수 값이나 복잡한 문자열이 있습니까?

편집 : XOR 물건은 다음과 같이 작동합니다.

update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;

답변

다음과 같은 방법으로 중간 교환 변수가 가장 좋습니다.

update z set c1 = @c := c1, c1 = c2, c2 = @c

첫째, 항상 작동합니다. 둘째, 데이터 유형에 관계없이 작동합니다.

둘 다에도 불구하고

update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2

과

update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2

일반적으로 숫자 데이터 형식에 대해서만 작동하며 오버플로를 방지하는 것은 사용자의 책임입니다. 부호가있는 것과 부호가없는 사이에 XOR을 사용할 수 없으며 오버플로 가능성에 대해 합계를 사용할 수 없습니다.

과

update z set c1 = c2, c2 = @c where @c := c1

c1이 0 또는 NULL이거나 길이가 0 인 문자열이거나 공백 일 경우 작동하지 않습니다.

우리는 그것을 바꿔야한다

update z set c1 = c2, c2 = @c where if((@c := c1), true, true)

스크립트는 다음과 같습니다.

mysql> create table z (c1 int, c2 int)
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into z values(0, 1), (-1, 1), (pow(2, 31) - 1, pow(2, 31) - 2)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.02 sec)

mysql> update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 2
mysql> update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 3

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.02 sec)

mysql> update z set c1 = c2, c2 = @c where @c := c1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          1 |          0 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> update z set c1 = @c := c1, c1 = c2, c2 = @c;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>update z set c1 = c2, c2 = @c where if((@c := c1), true, true);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          1 |          0 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)

답변

두 가지 대안 1. 임시 테이블 사용 2. XOR 알고리즘 조사