SQL을 사용하다 보면 새로운 데이터를 넣으려다가 이미 같은 값이 있어서 에러가 나는 경우가 있다. PRIMARY KEY나 UNIQUE KEY가 걸린 컬럼에서 이런 상황이 생긴다.
단순히 에러를 피하는 것이 아니라, 상황에 따라서는 "없으면 추가하고, 있으면 갱신"하는 동작이 필요할 때가 있다. MySQL은 이런 요구를 충족하기 위해 ON DUPLICATE KEY UPDATE라는 구문을 제공한다. 흔히 이런 패턴을 UPSERT(Insert + Update) 라고 부른다.
동작 방식
먼저 INSERT를 시도한다. 만약 같은 키 값이 이미 존재한다면 에러 대신 UPDATE를 실행한다. 따라서 “없으면 INSERT, 있으면 UPDATE”라는 로직을 간단하게 표현할 수 있다.
즉, 같은 SQL 구문이 상황에 따라 삽입과 갱신 두 가지 역할을 모두 수행한다. 이를 활용하면 중복 키 에러를 막고, 조건 분기 로직을 따로 작성하지 않아도 된다.
INSERT INTO users (id, name, last_login)
VALUES (1, 'jin', NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
last_login = NOW();id=1 사용자가 없다면 새로 추가되고, 이미 있다면 name과 last_login 값이 갱신된다.
비슷한 기능은 MySQL 이외에 다른 데이터베이스에도 존재한다. PostgreSQL에서는 ON CONFLICT (key) DO UPDATE, SQLite에서는 INSERT OR REPLACE 구문을 사용한다. 문법은 조금 다르지만 모두 "중복이면 업데이트"라는 같은 개념을 담고 있다.
즉, 데이터베이스마다 표현 방식은 다르지만, UPSERT라는 개념은 보편적으로 제공되고 있다.
주의할 점
ON DUPLICATE KEY UPDATE는 값이 실제로 변하지 않더라도 UPDATE를 무조건 실행한다.
예를 들어 이미 name='jin'인 행에 대해 같은 값을 다시 넣더라도 UPDATE가 발생한다. 이 경우 데이터는 달라지지 않지만 디스크 쓰기와 락이 걸리고, 트리거나 binlog에는 UPDATE 이벤트가 기록된다. 트래픽이 많은 환경에서는 불필요한 부하가 될 수 있다.
MySQL 8.0.19 이상에서는 VALUES() 대신 VALUES(col)이 excluded.col로 바뀌면서 조건식을 활용할 수 있다. 이렇게 하면 값이 실제로 달라질 때만 UPDATE가 실행되도록 만들 수 있다.
INSERT INTO users (id, name, last_login)
VALUES (1, 'jin', NOW())
ON DUPLICATE KEY UPDATE
name = IF(VALUES(name) <> name, VALUES(name), name),
last_login = IF(VALUES(last_login) <> last_login, VALUES(last_login), last_login);VALUES(name)은 새로 삽입하려는 값
name은 기존 테이블에 있는 값
UPDATE ... SET col = col 같이 기존 값과 완전히 같은 값을 넣는 경우
MySQL은 "행이 변경되지 않았다(no-op)"로 간주하고 실제 쓰기를 하지 않는다.
이렇게 작성하면 값이 똑같은 경우에는 불필요한 UPDATE가 일어나지 않아 성능과 binlog 부하를 줄일 수 있다.
💡 binlog 부하란?
MySQL은 데이터 변경(INSERT, UPDATE, DELETE)을 binlog(Binary Log)에 기록한다.
이 로그는 복제(Replication)나 복구(Point-in-Time Recovery)에 쓰인다.
문제는 ON DUPLICATE KEY UPDATE처럼 실제로 값이 안 바뀌었는데도 UPDATE가 실행되면,
데이터는 그대로인데 binlog에는 UPDATE 이벤트가 기록된다는 점이다.
이런 불필요한 기록이 많아지면 binlog 파일이 커지고,
Master → Slave 복제 시 불필요한 이벤트 전송이 늘어나 서버와 네트워크에 부담이 생긴다.
이것을 흔히 binlog 부하라고 부른다.
MySQL의 ON DUPLICATE KEY UPDATE는 UPSERT를 구현하는 대표적인 방법이다.
중복 키 에러를 처리하면서 삽입과 갱신을 한 번에 해결할 수 있는 기능이다. 회원 정보, 환경 설정, 구독 상태처럼 존재 여부에 따라 데이터를 덮어써야 하는 상황에서 특히 유용하다.
