INSERT IGNORE & REPLACE

MySQL 5.5 INSERT IGNORE & REPLACE v1.0

Date	Ver	Etc.
12.11.28	1.0

<!--[if !supportLists]-->1. <!--[endif]-->INSERT IGNORE & REPLACE

기존 rows 를overwrite 하기 위해서는 insert 대신해replace 를 사용해야 한다.

replace 와 insert ignore 는 duplicate 상태의 entry 를 처리하는 방법이 다르다.

insert ignore 의 경우 duplicated entry 를 단순히 폐기하지만,replace 의 경우는 기존의 duplicated entry 를 새 값이 대체한다. 최신의 값으로 update 된다고 생각하면 된다.

매뉴얼에서는 insert ignore 를 사용한 경우 중첩된 entry 가 발생할 때 warning 이 발생한다고 하나, 실제로 확인해 보면 아무런 error / warning 이 없다. 참고로 ignore 가 없는 경우 위 에러가 발생하면 duplicate-key error 와 함께 statement 수행이 중지된다.

insert ignore 의 효과는 partitioned table 에서 주어진 값에 맵핑되는 파티션이 없을 때도 비슷한 효과를 가진다. 이를테면 insert 구문안의 값이 들어갈 파티션이 있으면 정상수행되고 없으면 해당 rows 는 입력에서 제외된다.

<!--[if !supportLists]-->2. <!--[endif]-->Sample

예제를 만들기에 앞서 다음과 같이 테이블과 데이터를 구성했다.

mysql> create table insert_ign( -> id1 int, -> id2 int, -> primary key (id1)); Query OK, 0 rows affected (0.01 sec) mysql> insert into insert_ign values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0

위 상황에서 (3,4)데이터를 넣으면 당연히도 Duplicate error 가 발생한다.

하지만 IGNORE 옵션을 사용하면 에러없이 수행이 성공한다.

mysql> insert into insert_ign values(3,4); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql> insert ignore into insert_ign values(3,4); Query OK, 0 rows affected (0.00 sec) mysql> select * from insert_ign; +-----+------+ | id1 | id2 | +-----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +-----+------+ 3 rows in set (0.00 sec)

위와같이 쿼리 수행을 성공했으나 실제로 테이블 내용을 보면 변경 된 rows 가 없다. Duplicate 난 row 에 대해 실제 적용이 되지 않았음을 알 수 있다. 또 쿼리 수행에 에러가 없었고, alert log 에도 아무런 메시지가 남지 않았다.

위와 같이duplicated entry 가 discard 되는게 아니라 새로운 값으로 update 되길 원한다면 replace 명령어를 사용해야 한다.

mysql> replace into insert_ign values(1,10),(2,20),(3,30); Query OK, 6 rows affected (0.02 sec) Records: 3 Duplicates: 3 Warnings: 0 mysql> select * from insert_ign; +-----+------+ | id1 | id2 | +-----+------+ | 1 | 10 | | 2 | 20 | | 3 | 30 | +-----+------+ 3 rows in set (0.00 sec)

replace 명령을 사용하는 경우 duplicated entry 에 대해 새로운 값으로 update 된다. 특이한 점은 3개의 rows 에 대해 변경이 있었는데 ‘6 rows affected’ 로 나오는 부분이다. 1개의 duplicated entry 에 대해 replace 하는 경우엔 ‘2 rows affected’ 가 결과로 나온다.

하나의duplicated entry 와 새로운 entry 를 입력하는 replace 경우 다음과 같은 결과가 나온다.

mysql> replace into insert_ign values(3,3),(4,40); Query OK, 3 rows affected (0.00 sec) Records: 2 Duplicates: 1 Warnings: 0 mysql> select * from insert_ign; +-----+------+ | id1 | id2 | +-----+------+ | 1 | 10 | | 2 | 20 | | 3 | 3 | | 4 | 40 | +-----+------+

위 결과로 보아 ?rows affected 의 ? 부분의 값은Records + Duplicates 값으로 보인다.

<!--[if !supportLists]-->3. <!--[endif]-->References

<!--[if !supportLists]-->A. <!--[endif]-->INSERT syntax | http://dev.mysql.com/doc/refman/5.5/en/insert.html

<!--[if !supportLists]-->B. <!--[endif]-->REPLACE | http://dev.mysql.com/doc/refman/5.5/en/replace.html