MySql 의 ON DUPLICATE KEY UPDATE 예제 (UPSERT)

예전에 MongoDB 책을 보다가 UPSERT 기능을 본 적이 있습니다.

UPSERT 기능은 INSERT와 UPDATE를 결합 한 기능으로 데이터를 INSERT 하다 중복 오류가 발생하면 지정한 컬럼을 UPDATE 해주는 기능입니다.

해당 기능이 편해 보여서 다른 DB도 유사한 기능을 제공하는지 확인 했었는데 MySql, Oracle, MSSQL에서도 제공 하는 기능 이었습니다.

아래는 DB별 UPSERT 명령어 입니다.

MySql => ON DUPLICATE KEY UPDATE

Oracle => MERGE INTO 오라클은 여기 참고 하세요. http://huskdoll.tistory.com/195

MSSQL => IF NOT EXISTS

프로젝트 시 해당 기능을 유용하게 사용한 적이 있습니다.

UPSERT 기능을 프로그래밍으로 처리 하자면..

1. 입력하려는 데이터의 KEY를 조건절로 SELECT 구문을 실행

2. 해당 결과 값 확인

3. 결과 값에 따라서 있다면 UPDATE, 없다면 INSERT 작업

로 말씀 드릴수 있습니다.

하지만 UPSERT는 위의 부분을 간단하게 쿼리로 처리해주는 기능 입니다.

자세한 설명은 해당 링크를 참고하여 주시기 바랍니다.

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

여기서는 MySql의 UPSERT 기능인 ON DUPLICATE KEY UPDATE 예제를 작성해 보겠습니다. (MySql 4.1 버전 이상부터 지원합니다.)

일단 BOOKS 테이블을 생성 하겠습니다.

CREATE TABLE BOOKS (
  ID VARCHAR(5) NOT NULL,
  NAME VARCHAR(20) NOT NULL,
  WRITER VARCHAR(20) NOT NULL,
  PRICE DECIMAL(10,2) DEFAULT 0,
  GENRE VARCHAR(20) NOT NULL,
  PUBLISHER VARCHAR(20) NOT NULL,
  CNT DECIMAL(10,0) DEFAULT 0,
  PRIMARY KEY  (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

그리고 ON DUPLICATE KEY UPDATE 를 이용하여 입력해 보겠습니다. (여러번)

INSERT INTO BOOKS(
  ID,
  NAME,
  WRITER,
  PRICE,
  GENRE,
  PUBLISHER
) VALUES (
  'A1',
  '홍길동전',
  '허균',
  300,
  '소설',
  '허균 출판사'
)
 ON DUPLICATE KEY UPDATE
 CNT = CNT+1

입력을 2번 이상 해보시면 CNT 의 숫자가 올라가는걸 확인하실 수 있습니다.

일반적인 INSERT 문과 동일하고 INSERT문 뒤에 ON DUPLICATE KEY UPDATE 를 붙인 후에 업데이트 할 컬럼을 작성 합니다.

저는 주로 데이터 업데이트 진행한 날짜를 기록하거나 카운트를 기록하는데 주로 사용 합니다.