INSERT ON DUPLICATE UPDATE

mysql 쿼리를 짜다보면 가끔씩 꼭 필요한 쿼리 기능이다.

기존에 데이터가 이미 있다면 update를 하고 없다면 insert를 하는 쿼리로 이전에 네이트뉴스 랭킹뉴스의 5개의 컴포넌드들의 값을 하나의 리스트로 정리하면서 사용했던 기억이 있다.

자체적으로 성능적인 면도 검사 했는데 정확한 수치가 기억은 나지 않지만 변경한 쿼리가 성능면에서도 더 좋았던 것으로 기억한다.

(변경전) select후 결과값에 따라 insert,update 쿼리 실행 => 2개의 쿼리 실행

(변경후) insert on duplicate 구문으로 쿼리 실행 => 1개의 쿼리 실행

쿼리 문 자체는 굉장히 간단한 구문이고 중복 체크가 되는 키값이 해당 테이블의 Unique index 이거나 Primary Key이기만 하면 된다.

1. 테이블 생성

CREATE TABLE animals 
  ( 
     name     VARCHAR(100) NOT NULL PRIMARY KEY,
    legs_cnt TINYINT DEFAULT 0
 ); 

2. 테이블 데이터 삽입

insert into animals(name,legs_cnt) values('햄스터',4);
insert into animals(name,legs_cnt) values('사자',4);
insert into animals(name,legs_cnt) values('인간',2);
insert into animals(name,legs_cnt) values('닭',0);
insert into animals(name,legs_cnt) values('뱀',0);
insert into animals(name,legs_cnt) values('말',4);
insert into animals(name,legs_cnt) values('침팬지',4);

mysql> select * from animals;
+--------+----------+
| name   | legs_cnt |
+--------+----------+
| 햄스터 |        4 |
| 사자   |        4 |
| 인간   |        2 |
| 닭     |        0 |
| 뱀     |        0 |
| 말     |        4 |
| 침팬지 |        4 |
+--------+----------+
7 rows in set (0.00 sec)

3. 데이터 추가 입력! 근데? 이미 값이 있을지도 모른다!! (실제로 있었다.)

- 침팬지가 2족 보행 동물로 지정되면서 다리의 갯수가 2개로 변경이 되었다. 근데 기억력이 떨어지는 개발자가 이전에 침팬지가 이전에 있는 지 알수가 없는 상황이다. 

4. INSERT ON DUPLICATE UPDATE 구문 사용

INSERT INTO animals (name,legs_cnt) VALUES ('침팬지',2) 
ON DUPLICATE KEY UPDATE legs_cnt = 2;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from animals;
+--------+----------+
| name   | legs_cnt |
+--------+----------+
| 햄스터 |        4 |
| 사자   |        4 |
| 인간   |        2 |
| 닭     |        0 |
| 뱀     |        0 |
| 말     |        4 |
| 침팬지 |        2 |
+--------+----------+
7 rows in set (0.01 sec)

4. 침팬지의 다리의 갯수가 2개로 변경되었다!!

 - 근데 자세히 보면 쿼리 후 2 rows affected로 나온다. (추가 조사가 필요해 보인다.)

5. 데이터 추가 입력! 근데? 이미 값이 있을지도 모른다!! (실제로 없었다.)

INSERT INTO animals (name,legs_cnt) VALUES ('오랑우탄',2)
ON DUPLICATE KEY UPDATE legs_cnt = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+----------+----------+
| name     | legs_cnt |
+----------+----------+
| 햄스터   |        4 |
| 사자     |        4 |
| 인간     |        2 |
| 닭       |        0 |
| 뱀       |        0 |
| 말       |        4 |
| 침팬지   |        2 |
| 오랑우탄 |        2 |
+----------+----------+
8 rows in set (0.00 sec)

 - 정상적으로 추가가 되었다.

6. insert, update 차이

 - insert 시에는 1개의 행에만 영향이 가기 때문에 1 row affected로 결과가 나오고 update 시에는 insert 실패 후 update가 되어 2 rows affected가 나오는 것으로 예상된다. 좀더 찾아 봐야 할듯 하다.