[MySQL] UPSERT를 처리하는 방법 (feat. auto_increment PK 주의)

[jaejlf]

Note

이 글에서는 MySQL에서 UPSERT(UPDATE or INSERT) 작업을 수행하는 방법에 대해 설명합니다. 또한 UPSERT 대상 테이블의 PK가 auto_increment인 경우에 주의해야 할 사항에 대해서도 다룹니다.

#️⃣ Intro

애플리케이션을 개발하다보면 UPSERT(데이터가 있으면 UPDATE, 없으면 INSERT) 작업이 필요한 경우가 많습니다.

MySQL에서는 INSERT ... ON DUPLICATE KEY UPDATE구문을 통해 UPSERT 작업을 지원합니다.

#️⃣ INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...)
ON DUPLICATE KEY UPDATE -- PK나 UK가 겹치는 경우!
    컬럼1 = VALUES(컬럼1),
    컬럼2 = VALUES(컬럼2), ...;

INSERT ... ON DUPLICATE KEY UPDATE 구문은 INSERT 구문을 실행하고, 만약 지정된 키(PK or UK)에 충돌이 발생하면 UPDATE 구문을 실행합니다.

충돌 = Duplicate Key 오류 (PK or UK)

(주의!) UPSERT 대상 테이블의 키 PK가 auto_increment인 경우

INSERT ... ON DUPLICATE KEY UPDATE 구문은 항상 INSERT를 먼저 수행하기 떄문에, 만약 대상 테이블의 PK가 auto_increment로 설정되어 있다면, 실행 횟수만큼 auto_increment가 증가하므로 주의가 필요합니다. (실제로 UPDATE 작업만 이루어졌더라도, 최초 INSERT 시도에 의해 auto_increment 값이 증가하게 됩니다.)

따라서 PK가 auto_increment로 설정된 테이블에서 UPSERT 작업이 필요한 경우에는,

1. auto_increment가 꼭 필요한지 재검토해 보고
2. 그럼에도 auto_increment를 유지해야 한다면, INSERT ... ON DUPLICATE KEY UPDATE 구문을 사용하는 대신, INSERT/UPDATE를 명시적으로 구분하여 문제를 해결할 수 있습니다.

#️⃣ Summary

1. INSERT ... ON DUPLICATE KEY UPDATE 구문은 INSERT 구문을 실행하고, 만약 지정된 키(PK or UK) 에 충돌이 발생하면 UPDATE 구문을 실행합니다.
2. INSERT ... ON DUPLICATE KEY UPDATE 구문은 항상 INSERT를 먼저 수행하기 떄문에, 만약 대상 테이블의 PK가 auto_increment로 설정되어 있다면, 실행 횟수만큼 auto_increment가 증가하므로 주의가 필요합니다.

#️⃣ 참고 자료

• https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html