3.5.4 INSERT INTO ... ON DUPLICATE KEY UPDATE, REPLACE

MariaDB의 쿼리 기능 중 Oracle과 다른 기능이 있어 테스트 후 정리하였습니다.

 

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

DML 작업 중 INSERT 시 사용할 수 있는 명령어입니다.

INSERT INTO ... ON DUPLICATE KEY UPDATE 옵션을 사용할 수 있는데, 이 옵션은 중복된 레코드가 이미 존재할 때에는 UPDATE가 실행되고 그렇지 않을 때에는 INSERT가 실행됩니다.

 

간단한 테스트로 확인해보겠습니다.

 

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

FD1=1,FD2='Lee'라는 값을 INSERT하는데, 만약, 같은 값이 있다면 FD2='Lee'라는 값으로 UPDATE한다는 뜻입니다.

테이블에는 일치하는 값이 없어서 INSERT가 수행된것을 확인할 수 있습니다.

 

두번째에는 FD1=3,FD2='Kim'을 INSERT합니다. FD1=3인 값은 방금 하나 INSERT했기 때문에 존재하고 있습니다. 따라서 INSERT 구문이 실행되지 않고 UPDATE fd2='Kim' 이 문장만 실행되게 됩니다. 

 

수행 후 확인하면 값이 INSERT된것이 아니라 Lee -> Kim으로 UPDATE된 것을 확인할 수 있습니다.

MariaDB [test]> insert into tab_test(fd1,fd2) values (3,'Lee') on duplicate key update fd2='Lee';
Query OK, 1 row affected (0.006 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   3 | Lee  |
+-----+------+
1 row in set (0.000 sec)

MariaDB [test]> insert into tab_test(fd1,fd2) values (3,'Kim') on duplicate key update fd2='Kim';
Query OK, 2 rows affected (0.002 sec)


MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   3 | Kim  |
+-----+------+
1 row in set (0.000 sec)

 

REPLACE

같은 기능을 하는 명령어로 REPLACE도 있습니다.

외형적으로는 레코드가 존재하지 않으면 INSERT로 처리되고 레코드가 존재하는 경우에는 UPDATE로 처리 됩니다.

 

위에서 테스트 했던 테이블을 그대로 사용해서 테스트를 진행해보면, 
replace tab_test set fd1=1,fd2='Park';이라는 명령어를 사용했습니다. 이 값은 현재 테이블에 없기 때문에 INSERT가 된것을 확인할 수 있습니다.

 

하지만 한번더 eplace tab_test set fd1=1,fd2='BAE'; 명령어를 사용한 결과, 이미 fd1=1인 값이 있기 때문에 INSERT 되지 않고 UPDATE된 것을 확인할 수 있습니다.

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   3 | Kim  |
+-----+------+
1 row in set (0.001 sec)

MariaDB [test]> replace tab_test set fd1=1,fd2='Park';
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | Park |
|   3 | Kim  |
+-----+------+
2 rows in set (0.001 sec)


MariaDB [test]> replace tab_test set fd1=1,fd2='BAE';
Query OK, 2 rows affected (0.002 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | BAE  |
|   3 | Kim  |
+-----+------+
2 rows in set (0.000 sec)

 

REPLACE문장은 프라이머리 키나 유니크 키가 중복된 레코드가 있다면 먼저 그 레코드를 삭제하고 새로운 레코드로 INSERT를 수행합니다. 실제로는 UPDATE가 된것으로 보이지만 사실 REPLACE문장이 실행되기 전의 레코드와 실행된 후의 레코드는 내부적으로 다른 레코드가 됩니다.

또한 REPLACE문장은 중복된 레코드가 있을 경우 레코드를 삭제후 다시 INSERT하게 되므로 INSERT INTO ... ON DUPLICATE KEY UPDATE ... 문장 보다는 부하도 높고 시스템의 자원을 많이 소모하게 됩니다.

 

 

 

출처 : Real MariaDB