[MySQL] 각 행에 같은 컬럼을 다른 값으로 업데이트하기
동기
특정 테이블에 있는 모든 데이터를 수정해줘야 하는 일이 생겼었다. 다행히도 수정해야하는 컬럼이 정해져 있었는데, 각 행마다 해당
컬럼 값을 모두 다르게 업데이트해줘야 했다. 다같이 +1을 하는 등 공통으로 작업을 해주는 거라면 단순히 UPDATE 쿼리 한방으로
해결할 수 있지만, 각 행마다 해당 컬럼을 다른 값으로 업데이트 해준다는 건 쿼리 하나로 처리하는게 상상하기가 어려웠다.
찾아보다가 좋은 자료를 발견해서 기록하고 싶었다.
MySQL
일단 여기서는 MySQL에서 지원하는 ROW_NUMBER와 SUBSTRING_INDEX 다룬다. 각 설명만 간단히 듣고 시작해보자.
ROW_NUMBER
ROW_NUMBER는 각 행에 연속적인 순서를 부여하는 윈도우 함수다.
아래처럼 사용할 수 있고, OVER 안에는 어떤 순서대로 순위를 부여하고 싶은지 설정하면 된다.
여기서는 id 내림차순 기준으므로 [14, 37, 88]이 있다면 ROW_NUMBER()는 [1, 2, 3]이 되겠다.
SELECT id, ROW_NUMBER() OVER(ORDER BY id) AS rank_id
FROM my_table;
SUBSTRING_INDEX
SUBSTRING_INDEX는 주어진 String에서 설정한 index에 있는 String 일부를 얻는 함수다.
아래처럼 사용할 수 있다.
# 앞에서 1~2번째
SELECT SUBSTRING_INDEX('aa,bb,cc,dd', ',', 2);
>> aa,bb
# 뒤에서 1번째
SELECT SUBSTRING_INDEX('aa,bb,cc,dd', ',', -1);
>> dd
이를 중첩해서 사용하면 해당 문자열에 원하는 위치에 있는 String을 얻을 수 있다.
# aa,bb -> bb
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('aa,bb,cc,dd', ',', 2), ',', -1);
>> bb
적용 방법
ROW_NUMBER과 SUBSTRING_INDEX를 통해서 다음 조건에도 각 행에 원하는 값을 업데이트할 수 있게됐다.
- 변경할 데이터들의 id가 반드시 연속적이지 않아도 됨
- 각 행에 압데이트할 필드값이 어떤 규칙성을 띄지 않아도 됨 (다같이 +1, -1 등등)
my_table에 들어있는 데이터를 살펴보자.
v에 담긴 값은 id 순서대로 말해보면 [a, b, c]다. 이걸 위에서 말한 것처럼 다른 값 [AA, BB, CC]로 수정하고 싶다고 하자!
SELECT * FROM my_table;
+-----+---+
| id | v |
+-----+---+
| 14 | a |
| 37 | b |
| 88 | c |
+-----+---+
그러면 UPDATE 쿼리를 각 행마다 날려서 총 3번의 쿼리를 사용하는 것도 방법이지만, 아래처럼 ROW_NUMBER, SUBSTRING_INDEX를 사용한다면 깔끔하게(?) 해결할 수 있다.
UPDATE my_table t1
JOIN (
SELECT id, ROW_NUMBER() OVER(ORDER BY id) AS rank_id
FROM my_table
) AS t2 ON t1.id = t2.id
SET t1.v = SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC', ',', t2.rank_id), ',', -1);
# 결과
+-----+----+
| id | v |
+-----+----+
| 14 | AA |
| 37 | BB |
| 88 | CC |
+-----+----+
- t2는 원래 id와 id 기준 내림차순 인덱스인
rank_id를 가짐 -> 각 행에 새로운 인덱스 부여- 14 -> 1
- 37 -> 2
- 88 -> 3
적용 후 느낀점
Spring에서 작업을 하다보니 JPA를 활용해서 짜게되는 경우가 많은데, ROW_NUMBER와 SUBSTRING_INDEX는 MySQL에서만 지원하는 함수여서
JPA는 쓸 수 없어서 가지고 네이티브 쿼리를 사용했다. 네이티브 쿼리쓸 때 활용할 데이터가 담긴 DTO를 바로 사용할 수 있다면 더 깔끔했을텐데 그러지 못해서 짜면 짤수록 지저분해지는 느낌을 많이 받았다.
위 쿼리와 같은 효과를 내려고 처음엔 각 row마다 업데이트 하주는, N개 있으면 쿼리를 N번 날리는 로직을 짰었는데 이게 사실 DB에
좋지 않은 영향을 미칠거 같아서 쿼리 수를 어떻게든 줄여보고 싶었다. 하지만 위 쿼리의 경우 함수도 사용하면서 성능이 좀 떨어졌는지
N번 쿼리를 날리는 케이스가 1만 건당 20초가 넘었고, 위 쿼리는 1만 건에 40초가 넘게 걸렸다!
퀴리 발생 수를 줄이는 것도 방법이지만, 무조건 줄이는 것도 답은 아니라고 생각이 들었다.
사용 전에 함수 성능도 알아보는게 좋을 것 같다.
댓글남기기