[MySQL] 오라클(Oracle) Merge into 구문 따라하기 - 중복키 (Duplicate key) 처리

Primary Key 혹은 Unique Index가 생성되어 있는 테이블 데이터를 Insert 할 때, "Error Code: 1062. Duplicate entry '??' for key PRIMARY" 같은 에러를 자주 보게 된다. 테이블에 테이터를 Insert 하려는데 동일한 Key를 갖는 Row가 이미 테이블에 존재하는 경우 만나게 되는 에러다. 

테이블에 데이터가 없으면 insert 하고 있으면 update 하는 동작이 필요한 경우가 있다. 이 때, select를 해보고 insert, update를 판단하는 동작으로 애플리케이션을 작성할 수도 있다. 최고의 시나리오는 다음과 같을 것이다.

하지만 select 이후 중복된 키의 존재 유무를 판단하고, insert 혹은 update를 수행하면 phantom read가 문제로 다가온다. 

두 개의 세션이 동시에 select & insert 를 수행한다고 가정해보자. 두 세션 모두 select에서 특정 Key를 갖는 row가 없다고 판단할 수 있다. 이 후 두 세션 모두 테이블에 insert 하려고 시도할 수 있고, 둘 중 하나는 결국 똑같은 에러를 보게 된다.

이를 해소하기 위해 클라이언트 쪽에서 Lock 등을 이용한 동기화를 시도해볼 수 있다. 하지만 클라이언트가 서로 다른 서버에서 수행되는 등의 경우 처리가 복잡해진다. 나는 그냥 있으면 update하고 없으면 insert 하고 싶을 뿐인데 말이다. 

클라이언트가 가장 간단하게 사용할 수 있는 방법은 서버에서 제공해주는 문법을 사용하는 것이다. 오라클(Oracle)에서는 Merge Into라는 구문을 제공해서 이런 문제를 해소할 수 있게 해준다. 오라클의 Merge Into 구문은 다음과 같다. 

MERGE INTO [table_name alias]
USING [table | view | subquery]
ON [joint condition]
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2 ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ... ) VALUES (values1, values2, ...);

merge into 구문은 오라클 9i부터 추가된 기능이다. 10g부터는 UPDATE 대신 DELETE도 사용할 수 있게 되었지만 이 포스트에서는 다루지 않겠다.

MySQL에서의 Merge into 구문

오라클에서 지원하는 Merge into 구문은 MySQL에서는 지원되지 않는다. 대신 비슷한 기능을 하는 몇 가지 문법이 있다.

1) INSERT IGNORE

2) REPLACE INTO

3) INSERT ... ON DUPLICATE UPDATE

각각에 대해서 예제와 함께 알아보겠다. 먼저 테스트를 위해 테이블을 하나 만들어 보겠다. 

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `entry_name` varchar(16) DEFAULT NULL,
 `size` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `entry_name_UNIQUE` (`entry_name`)
);

이 테이블에 테스트를 위한 ROW를 하나 INSERT 해보자. 

 INSERT INTO `T1` VALUES (NULL, 'ENTRY1', 20); 

ROW 하나가 테이블에 INSERT 되어 있다. 

mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  1 | ENTRY1     |   20 |
+----+------------+------+
 
1 row in set (0.00 sec) 

이제 이 테이블에 중복된 키를 INSERT 하면서 테스트를 해보겠다. 

1) INSERT IGNORE

INSERT IGNORE 구문은 DUPLICATE KEY 에러를 무시한다. 다시말해서 중복된 데이터가 없으면 INSERT 하고 있으면 아무것도 안한다(IGNORE). 

UNIQUE Constraint를 위반하는 INSERT 구문을 수행해보자.

insert into `t1` values (null, 'ENTRY1', 21);

이 INSERT 구문을 수행하면 "0 row affected, 1 warnings" 라는 결과 메시지가 출력된다. 이 구문으로 영향을 받은 ROW는 없으며 대신 Warning만 하나 발생했을 뿐이라는 결과다.

물론 UNIQUE Constraint를 위반하지 않는 INSERT 구문을 실행해보면 정상적으로 수행된다.

mysql> insert into `t1` values (null, 'ENTRY2', 21);
Query OK, 1 row affected (0.01 sec)

중복되는 KEY가 없으므로 일반 INSERT 구문처럼 정상 수행되며, 1개의 ROW가 입력되었음을 나타낸다. 

테이블에 쿼리를 날려보면 당연히도,

mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  1 | ENTRY1     |   20 |
|  4 | ENTRY2     |   21 |
+----+------------+------+
2 rows in set (0.00 sec) 

정상적으로 ROW가 INSERT 되었다. 

유의해야 할 점은 AUTO_INCREMENT 값은 ERROR가 발생하거나 IGNORE 되었을 때에도 증가한다. 

2) REPLACE INTO

REPLACE INTO 구문은 오라클의 MERGE INTO 구문과 비슷하게 생겼다. MySQL의 REPLACE INTO 구문은 UNIQUE KEY에 해당하는 데이터가 없으면 일반 INSERT처럼 동작하고, 있으면 DELETE & INSERT를 수행한다. 

REPLACE INTO 구문을 테스트해보면, 

mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  1 | ENTRY1     |   20 |
|  4 | ENTRY2     |   21 |
+----+------------+------+
2 rows in set (0.00 sec) 
 
mysql> replace into t1 values (NULL, 'ENTRY3', 22);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  1 | ENTRY1     |   20 |
|  4 | ENTRY2     |   21 |
|  5 | ENTRY3     |   22 |
+----+------------+------+
3 rows in set (0.00 sec) 

존재하지 않는 새로운 ROW에 대해서 일반 INSERT처럼 값을 추가한다. 

mysql> replace into t1 values (NULL, 'ENTRY1', 23);
Query OK, 2 rows affected (0.01 sec)
 
mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  4 | ENTRY2     |   21 |
|  5 | ENTRY3     |   22 |
|  6 | ENTRY1     |   23 |
+----+------------+------+
3 rows in set (0.00 sec) 

이미 존재하는 ROW에 대해서 DELETE & INSERT처럼 동작한다. REPLACE INTO 구문의 결과로 "2 rows affected"라는 메시지를 보면, 

id 컬럼 기준으로 1번 ROW가 지워지고, 6번 ROW가 INSERT된 것을 확인할 수 있다. 기존의 ROW가 지워지고 새로운 ROW가 INSERT 되었으므로 AUTO_INCREMENT 컬럼 값이 증가한 채로 INSERT 되었다. 

3) INSERT ... ON DUPLICATE KEY UPDATE

이 구문은 INSERT를 수행하되 DUPLICATE 값이 존재하면 UPDATE 뒤에 명시한 동작을 취하라는 구문이다. 이어서 테스트를 해보겠다.

mysql> insert into t1  values (NULL, 'ENTRY4', 24) ON DUPLICATE KEY UPDATE size = 24;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  4 | ENTRY2     |   21 |
|  5 | ENTRY3     |   22 |
|  6 | ENTRY1     |   23 |
|  7 | ENTRY4     |   24 |
+----+------------+------+
4 rows in set (0.00 sec) 

이 구문도 DUPLICATE KEY에러가 발생하지 않으면 일반 INSERT 처럼 동작한다.

중복키를 INSERT 하는 테스트를 해보면,

mysql> insert into t1  values (NULL, 'ENTRY3', 25) ON DUPLICATE KEY UPDATE size = 25;
Query OK, 2 rows affected (0.01 sec)
 
mysql> select * from t1;
+----+------------+------+
| id | entry_name | size |
+----+------------+------+
|  4 | ENTRY2     |   21 |
|  5 | ENTRY3     |   25 |
|  6 | ENTRY1     |   23 |
|  7 | ENTRY4     |   24 |
+----+------------+------+
4 rows in set (0.00 sec) 

INSERT 대신 UPDATE 처럼 동작한 것을 확인할 수 있다. ENTRY3에 대해서 INSERT를 수행하면 DUPLICATE KEY에러가 발생한다. 하지만 ON DUPLICATE KEY UPDATE 구문을 이용해서 에러가 발생하는 대신 특정 컬럼을 UPDATE 할 수 있다.

주목해야 할 점은 AUTO_INCREMENT로 지정한 id 값은 변하지 않았다는 것이다. 단순히 특정 컬럼을 UPDATE한 것처럼 동작하기 때문에 id 컬럼의 값은 보존된다. 

REPLACE INTO 구문과 비교해보면 ON DUPLICATE KEY UPDATE 구문을 이용한 경우 컬럼하나만 UPDATE하기 때문에 좀 더 빠르게 수행된다. 

본 포스트에서 수행한 예제 코드들은 MySQL 14.14 버전에서 수행되었다.

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64) using  EditLine wrapper