[Upsert] 값이 없으면 Insert 하고, 값이 있으면 update 하기

Info

데이터베이스에서 대량의 데이터를 등록할때 기존에 값이 저장되어 있는 경우 기존에 저장된 값이 존재하거나 Unique 키 값은 같은데 데이터가 다를 경우들이 있다. 이런 경우 insert 문만 계속 요청하면 오류가 발생하기도 하고, 이미 값이 저장되어 있으면 새로운 값으로 변경하여 저장해야하는 경우들이 있다.

이럴 때 Unique 키 값이 존재할 때  새로운 값이 들어오면 update 를 실행해주도록 하는  INSERT INTO ON DUPLICATE KEY UPDATE 쿼리를 사용하여 원활이 작업을 수행할 수 있다.

테스트를 위한 테이블 생성과 사용 방법을 알아보자

테스트를 위한 테이블 준비

테스트를 위해 users 라는 테이블을 생성 후 name 이 중복으로 저장될 수 없도록 name 를 Unique 키로 등록하자.

DROP TABLE users;

CREATE TABLE users (

name VARCHAR(25),

email VARCHAR(255)

);

ALTER TABLE users ADD UNIQUE (name);

생성 직후 테이블 데이터를 조회하면 아무 값도 등록되어 있지 않은 상태가 된다.

INSERT INTO ON DUPLICATE KEY UPDATE 사용

이제 값이 없으면 insert, 있으면 update 하는 쿼리를 사용해보자.

# Query

INSERT INTO users (NAME, email) VALUES ('tez', 'tez@tez.kr') ON DUPLICATE KEY UPDATE name='tez', email='tezpark@tez.kr';

# mysql 에서 사용

mysql> INSERT INTO users (NAME, email) VALUES ('tez', 'tez@tez.kr') ON DUPLICATE KEY UPDATE name='tez', email='tezpark@tez.kr';

Query OK, 1 row affected (0.00sec)

mysql>

위의 쿼리를 실행한 후 users 테이블을 조회하면 아래와 같이 새로운 값이 등록된다.

mysql> select * from users;

+------+------------+

| name | email |

+------+------------+

| tez | tez@tez.kr |

+------+------------+

1 row in set (0.00sec)

mysql>

다시 한번 쿼리를 사용하면 tez 라는 이름이 등록되어 있어서 tez@tez.kr 이라는 이메일 주소가 tezpark@tez.kr 로 변경된다.

# mysql 에서 사용

mysql> INSERT INTO users (NAME, email) VALUES ('tez', 'tez@tez.kr') ON DUPLICATE KEY UPDATE name='tez', email='tezpark@tez.kr';

Query OK, 2 row affected (0.00sec)

mysql> select * from users;

+------+----------------+

| name | email |

+------+----------------+

| tez | tezpark@tez.kr |

+------+----------------+

1 row in set (0.00sec)

mysql>