MySQL ON DUPLICATE KEY UPDATE — UPSERT 완전 정리

 

 

Web Development · MySQL

MySQL ON DUPLICATE KEY UPDATE —
UPSERT 완전 정리

중복 키 처리 · INSERT vs UPDATE 자동 분기 · VALUES() · REPLACE INTO

시작 오늘 분석할 코드

-- 중복 키이면 UPDATE, 없으면 INSERT INSERT INTO memberTBL VALUES('BBK', '비비코', '미국') ON DUPLICATE KEY UPDATE name = '비비코', addr = '미국'; -- BBK가 없으면 INSERT, 있으면 name='비비코' addr='미국'으로 UPDATE

ON DUPLICATE KEY UPDATE는 INSERT와 UPDATE를 한 쿼리로 처리하는 UPSERT(Update+Insert) 패턴이다. PRIMARY KEY나 UNIQUE 컬럼이 중복될 때 자동으로 UPDATE로 전환된다.

---

1 핵심 개념

UPSERT

Update + Insert 합성어. 중복이면 UPDATE, 없으면 INSERT — 하나의 쿼리로 처리

ON DUPLICATE KEY

PRIMARY KEY 또는 UNIQUE 인덱스가 중복될 때 트리거되는 조건

VALUES(컬럼)

ON DUPLICATE KEY UPDATE 안에서 INSERT하려던 값 참조 — VALUES(name)이 삽입 시도값

AUTO_INCREMENT 영향

중복키로 UPDATE될 때도 AUTO_INCREMENT 카운터가 증가하는 MySQL 특성

-- memberTBL 구조 CREATE TABLE memberTBL ( userID CHAR(8) NOT NULL PRIMARY KEY, name NVARCHAR(10), addr CHAR(2) ); -- 중복 여부에 따른 동작 -- 1) BBK 없음 → INSERT 실행됨 INSERT INTO memberTBL VALUES('BBK', '비비코', '미국') ON DUPLICATE KEY UPDATE name='비비코', addr='미국'; -- 2) BBK 이미 있음 → UPDATE 실행됨 INSERT INTO memberTBL VALUES('BBK', '비비코2', '서울') ON DUPLICATE KEY UPDATE name='비비코2', addr='서울';

---

2 VALUES() 함수 — INSERT 시도값 참조

ON DUPLICATE KEY UPDATE 절 안에서 VALUES(컬럼)를 사용하면 INSERT하려던 값을 그대로 UPDATE에 재사용할 수 있어 코드 중복을 줄인다.

-- VALUES()로 INSERT 시도값을 UPDATE에 재사용 INSERT INTO memberTBL (userID, name, addr) VALUES ('BBK', '비비코', '미국') ON DUPLICATE KEY UPDATE name = VALUES(name), addr = VALUES(addr); -- VALUES(name) = INSERT에서 시도한 '비비코'를 UPDATE에 그대로 사용 -- 카운터 증가 패턴 (방문 횟수 등) INSERT INTO visitlog (userID, cnt) VALUES ('BBK', 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1; -- 없으면 cnt=1로 삽입, 있으면 cnt+1로 업데이트

카운터 누적 패턴: cnt = cnt + 1은 중복 시 카운터를 1씩 늘린다. 방문 횟수, 좋아요 수, 재고 차감 등 누적 집계에 자주 사용한다.

---

3 REPLACE INTO와 비교

비슷한 목적으로 REPLACE INTO도 존재하지만 동작 방식이 완전히 다르다. REPLACE INTO는 중복 시 DELETE 후 INSERT이고, ON DUPLICATE KEY UPDATE는 기존 행을 수정한다.

구분	ON DUPLICATE KEY UPDATE	REPLACE INTO
중복 없을 때	INSERT	INSERT
중복 있을 때	지정 컬럼만 UPDATE	DELETE 후 새 INSERT
기존 데이터 보존	UPDATE 안 한 컬럼은 유지	전체 행이 새로 교체됨
AUTO_INCREMENT	카운터 증가 (MySQL 특성)	새 ID로 증가
트리거 발동	INSERT 또는 UPDATE 트리거	DELETE + INSERT 트리거
적합한 경우	일부 컬럼만 업데이트할 때	전체 행을 새 값으로 대체할 때

REPLACE INTO는 기존 행 전체를 지우고 새 행을 삽입하므로, UPDATE 안 하려던 컬럼도 사라진다. FK(외래키)로 참조되는 테이블이라면 CASCADE DELETE가 발동될 수 있으니 주의해야 한다.

---

 

반응형

 

4 INSERT IGNORE vs ON DUPLICATE KEY UPDATE

중복 키 처리 방법은 여러 가지다. 각 방법의 동작 차이를 알면 상황에 맞는 선택이 가능하다.

-- 1) 기본 INSERT: 중복 시 오류 INSERT INTO memberTBL VALUES('BBK','비비코','미국'); -- BBK 있으면: Duplicate entry 'BBK' for key 'PRIMARY' (오류) -- 2) INSERT IGNORE: 중복 시 조용히 무시 (기존 데이터 유지) INSERT IGNORE INTO memberTBL VALUES('BBK','비비코','미국'); -- BBK 있으면: 아무것도 안 함. 기존 행 유지 -- 3) ON DUPLICATE KEY UPDATE: 중복 시 지정 컬럼 업데이트 INSERT INTO memberTBL VALUES('BBK','비비코','미국') ON DUPLICATE KEY UPDATE name='비비코',addr='미국'; -- BBK 있으면: name, addr만 변경 (다른 컬럼은 유지) -- 4) REPLACE INTO: 중복 시 기존 행 삭제 후 새 행 삽입 REPLACE INTO memberTBL VALUES('BBK','비비코','미국'); -- BBK 있으면: 기존 행 DELETE → 새 행 INSERT (전체 교체)

방법	중복 없을 때	중복 있을 때	특징
INSERT	삽입	오류 (Duplicate entry)	기본 동작
INSERT IGNORE	삽입	무시 (기존 유지)	오류 없이 건너뜀
ON DUPLICATE KEY	삽입	지정 컬럼 UPDATE	정밀 제어 가능
REPLACE INTO	삽입	DELETE + INSERT	전체 행 교체

---

5 실습 — memberTBL UPSERT 흐름

ON DUPLICATE KEY UPDATE의 전형적인 사용 흐름을 memberTBL을 대상으로 단계별로 추적한다.

-- 1단계: memberTBL에 BBK 없음 → INSERT 실행 INSERT INTO memberTBL VALUES('BBK','비비코','미국') ON DUPLICATE KEY UPDATE name='비비코', addr='미국'; -- 결과: BBK | 비비코 | 미국 (새 행 삽입) -- 2단계: 같은 BBK로 다시 실행 (주소 변경) INSERT INTO memberTBL VALUES('BBK','비비코','서울') ON DUPLICATE KEY UPDATE name=VALUES(name), addr=VALUES(addr); -- 결과: BBK | 비비코 | 서울 (기존 행 UPDATE됨) -- 3단계: 상태 확인 SELECT * FROM memberTBL WHERE userID = 'BBK'; -- → BBK | 비비코 | 서울

실제 데이터 동기화 시 이 패턴이 자주 쓰인다. 1) 외부에서 데이터가 들어옴 2) PK가 있으면 최신 정보로 UPDATE 3) 없으면 새 회원으로 INSERT — 단 1개 쿼리로 처리 가능.

---

6 자주 하는 실수

실수	증상	해결
PK/UNIQUE 없이 사용	ON DUPLICATE가 절대 트리거 안 됨 — 항상 INSERT	중복 판단 기준 컬럼에 PK 또는 UNIQUE 추가
VALUES() 없이 하드코딩	INSERT 값과 UPDATE 값을 따로 유지해야 해 오류 위험	VALUES(컬럼) 함수로 INSERT 시도값 재사용
AUTO_INCREMENT 점프	UPDATE돼도 ID가 증가해 ID에 빈 구간 발생	MySQL 설계 특성 — 원하면 sequence 테이블 사용
REPLACE INTO 남용	지정 안 한 컬럼 값이 사라짐	ON DUPLICATE KEY UPDATE로 교체

핵심 한 줄 요약

ON DUPLICATE KEY UPDATE중복 키이면 UPDATE, 없으면 INSERT — 단일 쿼리 UPSERT

UPSERTUpdate + Insert = 있으면 업데이트, 없으면 삽입

트리거 조건PRIMARY KEY 또는 UNIQUE 인덱스 중복 시에만 발동

VALUES(컬럼) 함수ON DUPLICATE KEY UPDATE 안에서 INSERT 시도값 참조

카운터 패턴cnt = cnt + 1 — 없으면 1로 시작, 있으면 1씩 누적

REPLACE INTO 차이REPLACE = DELETE+INSERT (전체 교체), ON DUPLICATE = 수정만

AUTO_INCREMENT 주의UPDATE 분기에서도 AUTO_INCREMENT 카운터 증가 — ID 빈 구간

memberTBL 예시BBK 없음 → INSERT / BBK 있음 → name, addr UPDATE

PK 없으면 안 됨PK/UNIQUE 없으면 ON DUPLICATE가 절대 발동하지 않음

일부 컬럼만 UPDATEON DUPLICATE KEY UPDATE name=..., addr=... — 지정 컬럼만 변경

유지 컬럼UPDATE 절에 포함 안 된 컬럼은 기존 값 유지 — REPLACE와 다름

여러 UNIQUE 중복여러 UNIQUE 컬럼이 있을 때 하나라도 중복이면 발동

영향 행 수INSERT = 1행 영향, UPDATE = 2행 영향 (MySQL 내부적으로 표시)

ROW_COUNT()INSERT=1, UPDATE=2 반환 — 어떤 분기 실행됐는지 확인 가능

실무 활용방문 로그, 회원 정보 갱신, 재고 차감 등 중복 체크 불필요한 패턴

데이터 동기화외부 소스에서 데이터 주기적 갱신 — 없으면 추가, 있으면 업데이트

ON DUPLICATE 구문INSERT INTO tbl VALUES (...) ON DUPLICATE KEY UPDATE col=값

VALUES 안 쓰면ON DUPLICATE KEY UPDATE name='고정값' — 항상 같은 값으로 UPDATE

IGNORE vs ON DUPLICATEINSERT IGNORE = 중복 시 무시(기존 유지), ON DUPLICATE = 중복 시 UPDATE

핵심 한 줄ON DUPLICATE KEY UPDATE = 있으면 UPDATE, 없으면 INSERT 자동 분기

INSERT IGNORE 차이IGNORE = 중복 시 무시(기존 유지), ON DUPLICATE = 중복 시 지정 컬럼 수정

REPLACE INTO 차이REPLACE = 기존 행 삭제 후 새 행, ON DUPLICATE = 기존 행 수정만

VALUES() 문법ON DUPLICATE UPDATE col = VALUES(col) — INSERT 시도값 그대로 사용

LAST_INSERT_ID()UPDATE 분기에서는 LAST_INSERT_ID()가 이전 값 유지 (INSERT만 갱신)

조건부 UPDATEON DUPLICATE KEY UPDATE col = IF(조건, 새값, 기존값) — 조건부 갱신

cnt 리셋 vs 누적cnt = 1 이면 리셋, cnt = cnt+1 이면 누적 — 목적에 따라 선택

AFFECTED_ROWS()INSERT=1, UPDATE=2 (old≠new), 변경없는 UPDATE=0

복합 UNIQUE 처리UNIQUE(col1, col2) 조합이 중복될 때도 ON DUPLICATE 발동

batch UPSERTINSERT INTO ... VALUES (...),(...) ON DUPLICATE KEY UPDATE ... — 다건 일괄

UPDATE 절 다중 컬럼ON DUPLICATE KEY UPDATE col1=val1, col2=val2 — 쉼표로 여러 컬럼

FK 연관 테이블REPLACE INTO는 FK CASCADE DELETE 위험 — ON DUPLICATE가 안전

트랜잭션과 함께BEGIN/COMMIT 안에서도 동일 동작 — 롤백 가능

MySQL 전용 구문ON DUPLICATE KEY UPDATE는 MySQL/MariaDB 전용 — 표준 SQL 아님

표준 SQL 대안MERGE INTO (Oracle/MSSQL) — MySQL 미지원, ON DUPLICATE로 대체

최적화 포인트SELECT 후 INSERT/UPDATE 분기보다 ON DUPLICATE가 락 감소, 빠름

memberTBL 적용userID가 PK이므로 BBK 중복 시 name, addr만 UPDATE 가능

정의 요약INSERT INTO ... VALUES (...) ON DUPLICATE KEY UPDATE 컬럼=값

INSERT 성공 기준PK/UNIQUE 중복 없을 때만 INSERT — 중복이면 ON DUPLICATE 분기

VALUES 함수 MySQL 8.0MySQL 8.0.20+ VALUES() 대신 NEW_VAL() 권장 — 현재는 VALUES() 가능

오류 방지 패턴중복 오류 없이 데이터 동기화 → ON DUPLICATE KEY UPDATE가 정석

UPSERT 활용 실무API로 들어온 데이터를 DB에 저장 — 있으면 갱신, 없으면 추가

addr 변경 예시BBK가 주소 이사 → ON DUPLICATE UPDATE addr='서울' — 단순

배치 UPSERT다건: VALUES(...),(...) ON DUPLICATE — 여러 행을 한 쿼리로 UPSERT

ON DUPLICATE 없을 때중복 처리하려면 SELECT+IF EXISTS+INSERT/UPDATE 3단계 필요 — 비효율

UNIQUE 조건UNIQUE(userID) 또는 PRIMARY KEY(userID) — 둘 다 ON DUPLICATE 트리거

UPDATE 조건 없음ON DUPLICATE KEY UPDATE는 WHERE 조건 없이 무조건 해당 행 UPDATE

성능 이점SELECT→분기→INSERT/UPDATE 패턴 대비 락 충돌 감소 + 쿼리 1회로 끝

누락 방지INSERT IGNORE와 달리 기존 데이터가 최신값으로 갱신 — 동기화 완전

구문 완성형INSERT INTO tbl (a,b,c) VALUES (v1,v2,v3) ON DUPLICATE KEY UPDATE b=VALUES(b)

UPSERT 핵심SELECT 후 분기 없이 → ON DUPLICATE 하나로 INSERT or UPDATE

BBK 시나리오처음=INSERT(미국), 다시=UPDATE(서울) — 쿼리 동일, DB 자동 분기

안전한 갱신충돌 오류 없이 데이터 최신화 — 웹 API→DB 동기화 표준 패턴

VALUES() 요약VALUES(col) = UPDATE절 안에서 INSERT 시도값 참조 — 중복 방지

핵심 요약ON DUPLICATE KEY UPDATE = PK/UNIQUE 중복 시 UPDATE, 아니면 INSERT

REPLACE 비교 요약ON DUPLICATE=수정, REPLACE=삭제+삽입, IGNORE=중복 무시

실무 1줄 요약없으면 넣고, 있으면 바꾸고 — UPSERT의 핵심

구문 위치INSERT INTO ... VALUES (...) ON DUPLICATE KEY UPDATE ... — 가장 마지막에

ON DUPLICATE 전제PK 또는 UNIQUE가 없으면 중복 감지 불가 — 테이블 설계가 먼저

동작 분기 요약중복=0 → INSERT 실행 / 중복=1 → ON DUPLICATE UPDATE 절 실행

테이블 설계 연계UPSERT를 쓰려면 중복 판단 컬럼에 PK나 UNIQUE 선언이 필수

Tags

#MySQL #ON DUPLICATE KEY UPDATE #UPSERT #INSERT #중복키 #PRIMARY KEY #UNIQUE #SQL #데이터베이스 #웹개발 #티스토리

▼ 티스토리 태그 입력란 복사용

MySQL, ON DUPLICATE KEY UPDATE, UPSERT, INSERT, 중복키, PRIMARY KEY, UNIQUE, SQL, 데이터베이스, 웹개발, 티스토리