INSERT ... ON DUPLICATE KEY UPDATE란?
MySQL의 INSERT ... ON DUPLICATE KEY UPDATE 구문은 새로운 행을 삽입하려 할 때 UNIQUE 키 또는 PRIMARY KEY에서 중복이 발생하면, 오류를 반환하는 대신 기존 행을 자동으로 업데이트(UPDATE)하는 방식으로 동작합니다. 즉, 실제 업데이트는 중복 값이 존재하는 경우에만 수행됩니다.
이 구문은 데이터를 조건에 따라 삽입하거나 갱신하는 작업(UPSERT)을 하나의 쿼리로 처리할 수 있어 매우 유용합니다.
1. 예제 테이블 생성
먼저 StudentName 컬럼에 UNIQUE 제약조건이 설정된 테이블을 생성해 보겠습니다.
mysql> create table DemoTable733 (
StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudentName varchar(100),
StudentMarks int,
UNIQUE KEY Un_Name (StudentName)
);
Query OK, 0 rows affected (0.60 sec)
여기서 StudentName 컬럼에 UNIQUE KEY(Un_Name)가 지정되어 있으므로, 동일한 학생 이름이 다시 삽입되면 중복으로 판단됩니다.
2. 레코드 삽입 및 중복 처리 확인
이제 INSERT 명령어에 ON DUPLICATE KEY UPDATE 절을 함께 사용하여 레코드를 삽입해 보겠습니다.
mysql> insert into DemoTable733(StudentName,StudentMarks) values('John',45)
ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable733(StudentName,StudentMarks) values('Adam',65)
ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable733(StudentName,StudentMarks) values('Carol',75)
ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable733(StudentName,StudentMarks) values('John',45)
ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 2 rows affected (0.12 sec)주목할 부분은 마지막 쿼리입니다. 'John'이라는 이름이 이미 테이블에 존재하기 때문에 새로운 행이 추가되지 않고, 대신 기존 행의 StudentMarks 값이 86으로 업데이트되었습니다. 이때 영향받은 행 수가 2로 표시되는데, 이는 MySQL이 중복 발생 시 1행 삽입 + 1행 업데이트로 계산하기 때문입니다.
3. 결과 확인
SELECT 문을 사용하여 테이블의 전체 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable733;
위 쿼리는 다음과 같은 결과를 출력합니다.
+-----------+-------------+--------------+
| StudentId | StudentName | StudentMarks |
+-----------+-------------+--------------+
| 1 | John | 86 |
| 2 | Adam | 65 |
| 3 | Carol | 75 |
+-----------+-------------+--------------+
3 rows in set (0.00 sec)
정리
'John'의 StudentMarks가 최초 삽입된 45가 아니라 86으로 저장된 것을 확인할 수 있습니다. 이는 네 번째 INSERT 문 실행 시 UNIQUE 키 중복이 감지되어, ON DUPLICATE KEY UPDATE 절에 정의된 대로 기존 행이 업데이트되었기 때문입니다.
이처럼 INSERT ... ON DUPLICATE KEY UPDATE를 활용하면 애플리케이션 코드에서 중복 여부를 미리 확인할 필요가 없어 로직이 간결해지고, 동시성 환경에서 발생할 수 있는 경쟁 상태(race condition)로 인한 오류도 효과적으로 방지할 수 있습니다.