MySQL의 열 값 스왑
좌표가 있는 MySQL 테이블이 있는데 열 이름은 X와 Y입니다.이제 이 표의 열 값을 바꾸면 X가 Y가 되고 Y가 X가 됩니다.가장 확실한 해결책은 열의 이름을 변경하는 것이지만, 반드시 권한이 있는 것은 아니기 때문에 구조를 변경할 필요는 없습니다.
UPDATE와 어떤 관계가 있습니까?UPDATE 테이블 SET X=Y, Y=X는 확실히 내가 원하는 대로 되지 않는다.
편집: 위에서 설명한 권한에 대한 제한은 테이블/데이터베이스 구조를 변경하는 ALTER TABLE 또는 기타 명령어의 사용을 효과적으로 방해합니다.열 이름을 바꾸거나 새 열을 추가하는 것은 안타깝게도 옵션이 아닙니다.
저도 같은 일을 해야 했고, 제 조사 결과를 요약하겠습니다.
UPDATE table SET X=Y, Y=X값을 모두만 하면 때문에 분명히하지 않습니다.다음은 임시 변수를 사용하는 방법입니다."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;이 방법은 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;제가 생각해낸 또 다른 접근법은 효과가 있는 것 같습니다.
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
UPDATE 테이블 SET X=Y, Y=X는 원하는 대로 동작합니다(편집: Postgre에서).MySQL이 아닌 SQL. 아래 참조).값은 이전 행에서 가져와 동일한 행의 새 복사본에 할당되고 이전 행이 교체됩니다.임시 테이블, 임시 열 또는 기타 스왑 트릭을 사용할 필요가 없습니다.
@D4V360: 알겠습니다.그것은 충격적이고 예상 밖이다.Postgre 사용SQL과 내 답변은 거기서 올바르게 동작합니다(시도했습니다).포스트그어 참조SQL UPDATE 문서(Parameters, expression 아래)에서는 SET 구 오른쪽에 있는 식에서 열의 이전 값을 명시적으로 사용함을 나타냅니다.대응하는 MySQL UPDATE 문서에는 사용자가 설명하는 동작을 나타내는 "Single-table UPDATE assignments are generically left to right"라는 문구가 포함되어 있습니다.
알기 좋은.
좋아, 그럼 재미삼아 이렇게 하면 되잖아! (문자열 값을 교환한다고 가정하면)
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, 제로렝스 문자열 또는 공백일 경우 동작하지 않습니다.
로 변경해야 합니다.
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 알고리즘 조사
ALTER TABLE table ADD COLUMN tmp;
UPDATE table SET tmp = X;
UPDATE table SET X = Y;
UPDATE table SET Y = tmp;
ALTER TABLE table DROP COLUMN tmp;
편집: Greg의 코멘트에 대해서:아니요, 이것은 동작하지 않습니다.
mysql> select * from test; +------+------+ | x | y | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec)mysql> update test set x=y, y=x; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test; +------+------+ | x | y | +------+------+ | 2 | 2 | | 4 | 4 | +------+------+ 2 rows in set (0.00 sec)
안 먹어봤는데
UPDATE tbl SET @temp=X, X=Y, Y=@temp
할 수 있을 것 같아.
마크.
이것은 확실히 효과가 있다!유로와 SKK 가격 컬럼을 교환하기 위해 필요했을 뿐입니다.:)
UPDATE tbl SET X=Y, Y=@temp where @temp:=X;
위는 동작하지 않습니다(ERROR 1064(42000):SQL 구문에 오류가 있습니다.)
SQL Server에서는 다음 쿼리를 사용할 수 있습니다.
update swaptable
set col1 = t2.col2,
col2 = t2.col1
from swaptable t2
where id = t2.id
^ 연산자의 결과는 MySQL에서 부호 없는 64비트 정수이므로 컬럼에 서명된 정수가 있다고 가정하면 CAST(a ^ b AS SIGNED)를 사용해야 합니다.
다른 사람에게 도움이 되는 경우, 같은 열을 두 행 간에 스왑하는 방법을 다음에 나타냅니다.
SELECT BIT_XOR(foo) FROM table WHERE key = $1 OR key = $2
UPDATE table SET foo = CAST(foo ^ $3 AS SIGNED) WHERE key = $1 OR key = $2
$1과 $2는 두 줄의 키이고 $3은 첫 번째 쿼리의 결과입니다.
열 이름을 바꿀 수도 있지만 이건 해킹에 가까워요그러나 이러한 열에 있을 수 있는 모든 인덱스에 주의하십시오.
테이블명은 customer 입니다. 필드는 a와 b입니다.값을 b;로 바꿉니다.
고객 SET a=(@set:=a), a = b, b = @seta 업데이트
이게 잘 작동하는 걸 확인했어요.
아래 질문에서 신청하시면 됩니다. 저에게 딱 맞습니다.
Table name: studentname
only single column available: name
update studentnames
set names = case names
when "Tanu" then "dipan"
when "dipan" then "Tanu"
end;
or
update studentnames
set names = case names
when "Tanu" then "dipan"
else "Tanu"
end;
다른 답변에서 지적되었듯이 단순 스왑은 열 2를 처리하기 직전에 열 1의 값을 캐시하여 두 열 모두 열 2의 값으로 설정되기 때문에 MySQL에서는 작동하지 않습니다.
MySQL에서는 작업 순서가 보장되지 않기 때문에 임시 변수를 사용하는 것도 신뢰할 수 없습니다.
테이블 구조를 변경하지 않고 2개의 열을 스왑하는 안전한 방법은 프라이머리 키가 필요한 내부 조인뿐입니다.id(이 경우)를 참조해 주세요.
UPDATE table1 t1, table2 t2
SET t1.column1 = t1.column2,
t1.column2 = t2.column1
WHERE t1.id = t2.id;
이것은 문제없이 동작합니다.
단일 쿼리를 사용한 열 값 스왑
업데이트 my_table SET a=@tmp:=a, a=b, b=@tmp;
건배!
한 열에서 다른 열로(아카이브 등) 값을 이동하고 원래 열의 값을 재설정해야 했습니다.
아래(위 답변에서 #3 참조)가 통했습니다.
Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999;
CREATE TABLE Names
(
F_NAME VARCHAR(22),
L_NAME VARCHAR(22)
);
INSERT INTO Names VALUES('Ashutosh', 'Singh'),('Anshuman','Singh'),('Manu', 'Singh');
UPDATE Names N1 , Names N2 SET N1.F_NAME = N2.L_NAME , N1.L_NAME = N2.F_NAME
WHERE N1.F_NAME = N2.F_NAME;
SELECT * FROM Names;
이 예에서는 날짜가 잘못된 레코드에 대해 start_date와 end_date를 스왑합니다(ETL을 메이저리라이트 실행 시 종료일보다 늦은 시작일이 발견되었습니다).쓰러져라, 나쁜 프로그래머!)
그 상황에서는 퍼포먼스상의 이유로 MEDIOMINTs를 사용하고 있기 때문에(Julian days와 같이 1900-01-01 root의 0 루트를 가지고 있기 때문에 WHERE mdu.start_date > mdu.end_date의 조건을 만족할 수 있었습니다.
PK는 3개의 열 모두에 개별적으로 배치되어 있습니다(작동/인덱스상의 이유로).
UPDATE monitor_date mdu
INNER JOIN monitor_date mdc
ON mdu.register_id = mdc.register_id
AND mdu.start_date = mdc.start_date
AND mdu.end_date = mdc.end_date
SET mdu.start_date = mdu.end_date, mdu.end_date = mdc.start_date
WHERE mdu.start_date > mdu.end_date;
tb_user에서 성과 이름의 값을 스왑한다고 가정합니다.
가장 안전한 것은 다음과 같습니다.
- tb_user를 복사합니다.tb_user와 tb_user_copy의 2개의 테이블이 있습니다.
- UPDATE INSER JOIN 쿼리 사용
UPDATE tb_user a
INNER JOIN tb_user_copy b
ON a.id = b.id
SET a.first_name = b.last_name, a.last_name = b.first_name
x에서 y로, y에서 x로 모든 열을 스왑하려면 이 쿼리를 사용합니다.
UPDATE table_name SET column_name = CASE column_name WHERE 'value of col is x' THEN 'swap it to y' ELSE 'swap it to x' END;
이 표를 상상하고 '섹스' 표의 m과 f를 바꿔보겠습니다.
| 아이디 | 이름. | 섹스. | 급여 |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
UPDATE sex
SET sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
따라서 업데이트된 테이블은 다음과 같습니다.
| 아이디 | 이름. | 섹스. | 급여 |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
언급URL : https://stackoverflow.com/questions/37649/swapping-column-values-in-mysql
'source' 카테고리의 다른 글
| 데이터베이스의 행 수가 너무 많습니까? (0) | 2022.09.08 |
|---|---|
| Django & MariaDB / MySQL : select_for_update lock 행을 서브쿼리에서 선택합니까?교착상태의 원인? (0) | 2022.09.08 |
| 빈 결과 확인(PHP, PDO 및 MySQL) (0) | 2022.09.08 |
| 문자열에서 하위 문자열의 마지막 항목 색인 찾기 (0) | 2022.09.08 |
| 함수 이름을 문자열로 가져오려면 어떻게 해야 합니까? (0) | 2022.09.08 |