[MySQL] ON DUPLICATE KEY UPDATE 사용하기

참고 : https://sdevstudy.tistory.com/12

■ ON DUPLICATE KEY UPDATE 사용하기

MySQL에서는 대량의 파일을 등록 및 수정하는경우

기존 데이터는 UPDATE하고 신규 데이터는 INSERT 시키는 ON DUPLICATE KEY UPDATE 기능을 제공한다.

해당 기능을 잘 사용하게 되면  대량의 데이터 처리하는데 많은 도움이 된다.

먼저 ON DUPLICATE KEY UPDATE 기능을 알아볼 테스트 테이블을 하나 생성해 보자.

# 테이블 생성

CREATE TABLE onepiece (       seq_num INT( 11 ) NOT NULL AUTO_INCREMENT     , name VARCHAR( 20 ) NOT NULL     , age INT( 2 ) NOT NULL     , stature INT( 3 ) DEFAULT NULL     , prize_money VARCHAR( 20 ) DEFAULT NULL     , blood VARCHAR( 2 ) DEFAULT NULL     , PRIMARY KEY ( seq_num )   ) ENGINE=INNODB DEFAULT CHARSET=utf8

테이블이 정상적으로 생성되었다면 초기값을 먼저 세팅해 보자.

# 데이터 입력 - 초기값 설정

INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Monkey D. Luffy', '17', '172', '3억', 'F');   INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Roronoa Zoro', '19', '178', '1억 2천만', 'XF');   INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Sanji', '19', '177', '7천 7백만', 'S');

초기값을 INSERT 하였다면 SELECT 하여 내용을 한번 확인해 보자.

# 데이터 출력 - 입력내역 확인

SELECT seq_num, name, age, stature, prize_money, blood FROM onepiece

이제 해당 샘플 테이블에서 절대로 변경되지 않을 name 컬럼을

UNIQUE KEY로 지정해야 한다.

# 테이블 변경 - UNIQUE KEY 적용

ALTER TABLE onepiece ADD UNIQUE ( name );

UNIQUE KEY 지정이 완료되었다면 

본격적으로 ON DUPLICATE KEY UPDATE 사용하였을때 어떤 결과를 얻을 수 있는지

새로운 데이터를 INSERT 하여 확인해보자.

# 데이터 입력 - 변경 및 추가 데이터 입력

-- 업데이트 할 기존 데이터    INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Monkey D. Luffy', '19', '174', '15억', 'F' )    ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Roronoa Zoro', '21', '181', '3억 2천만', 'XF' )    ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Sanji', '21', '180', '3억 3천만', 'S' )   ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   -- 신규 데이터   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Usopp', '19', '176', '2억', 'S' )   ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Nami', '20', '170', '6천 6백만', 'X' )   ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Tony Tony Chopper', '17', '90', '백', 'X' )   ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );   INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Nico Robin', '30', '188', '1억 3백', 'S' )   ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money );

ON DUPLICATE KEY UPDATE를 사용하는 기존 데이터와 신규 데이터의 입력이 완료되었다면

다시 SELECT 문으로 테스트 테이블의 내용을 확인해 보자.

# 데이터 출력 - 변경 내역 확인

SELECT seq_num, name, age, stature, prize_money, blood FROM onepiece

ON DUPLICATE KEY UPDATE 를 사용한 출결결과를 보면

여기서 age = age + 2 는 기존 값이 존재한다면 기존 값에서 +2를 더해주었다.

완전히 새로운 데이터로 초기화 하기 위해서는 stature = VALUES( stature ), prize_money = VALUES( prize_money ) 와 같이

VALUES를 활용하게 되면 완전히 새로운 데이터로 치환되는것을 확인 할 수 있다.

한가지 더 눈여겨 봐야 할 것은 데이터가 추가될 때마다 자동 증가되는 seq_num 컬럼 시퀀스 값이다.

1, 2, 3 다음에 4, 5, 6이 생략되고 7, 8, 9 10이 추가되는 것을 볼 수 있는데

이를 통해 ON DUPLICATE KEY UPDATE 작동방식을 유추해 볼 수 있다.

① INSERT 문을 실행하여 신규 데이터를 입력한다.   ② UNIQUE KEY( name컬럼 )의 에 중복되는 값을 확인한다.   ③ 중복된 UNIQUE KEY값이 존재하면 ON DUPLICATE KEY UPDATE 지정된 컬럼( age, stature, prize_money )컬럼을 UPDATE 한다.   ④ 마지막으로 새로 INSERT된 중복되는 UNIQUE KEY값을 가진 항목을 DELETE 한다.

필자는 항상 ON DUPLICATE KEY UPDATE 를 알기 전까지는 항상

if( UNIQUE KEY == TRUE ) { console.log( UPDATE문 실행. ); } else { console.log( INSERT문 실행. ); }

의 선택지 외에는 존재하지 않았다.

물론 이와 같은 방법으로도 해결은 가능하지만, 

---

참고 : https://mssun.tistory.com/entry/MySQL-replace-into

REPLACE INTO 관련내용 정리