[MySQL] 여러개의 UPSERT(UPADTE+ INSERT) 요청 하나로 합치기

키가 존재하면 업데이트하고, 존재하지 않으면 삽입하는 동작이 필요했다. 하지만 대용량의 요청을 보내면서 무리를 덜 줄 방법을 생각하다가 스택오버플로우에 물어봤는데 금방 답변을 해주었다.

질문했던 내용을 자세히 보려면 이 링크를 참조하면 된다.

 

기존의 방식

1. 1번 키에 prop_a, prop_b에 데이터 삽입

 

INSERT INTO example_table (id, prop_a, prop_b) VALUES (1,1,7) 
ON DUPLICATE KEY UPDATE prop_a=1,prop_b=7;

-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       7       0
-------------------------------------------------------------

 

2. 1번 키에 prop_b, prop_c 값 업데이트

INSERT INTO example_table (id, prop_b, prop_c) VALUES (1,5,8) 
ON DUPLICATE KEY UPDATE prop_b=5,prop_c=8;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
-------------------------------------------------------------

3. 7번 키에 prop_a, prop_b 데이터 삽입

INSERT INTO example_table (id, prop_a, prop_b) VALUES (7,3,3) 
ON DUPLICATE KEY UPDATE prop_a=3,prop_b=3;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
7                3       3       0
-------------------------------------------------------------

 

하나로 합친 방식

INSERT INTO example_table (id, prop_a, prop_b, prop_c) 
SELECT 1,1,7,0 UNION ALL
SELECT 1,5,0,8 UNION ALL
SELECT 7,3,3,0
ON DUPLICATE KEY UPDATE 
prop_a=CASE WHEN VALUES(prop_a) = 0 
            THEN COALESCE(prop_a, 0)
            ELSE VALUES(prop_a) END,
prop_b=CASE WHEN VALUES(prop_b) = 0 
            THEN COALESCE(prop_b, 0)
            ELSE VALUES(prop_b) END,
prop_c=CASE WHEN VALUES(prop_c) = 0 
            THEN COALESCE(prop_c, 0)
            ELSE VALUES(prop_c) END;


-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
7                3       3       0
-------------------------------------------------------------

1. SELECT와 UNION ALL을 사용해 임시 테이블을 생성한다.

2. 삽입을 하는데 만약에 중복된 키가 있을 경우 ON DUPLICATE KEY UPDATE 이후의 코드를 실행한다.

3. 만약 생성한 임시 테이블의 prop_a가 0일 경우, 기존의 값을 사용하거나 기존의 값이 없다면 0을 사용한다. 임시 테이블의 prop_a가 0이 아닐 경우 임시 테이블의 값을 사용한다. prop_b, prop_c도 같은 방식으로 처리한다.