PKやUnique Indexがあるテーブルに対して既に存在するデータをINSERTしようとすると、
Duplicate Key Errorが起きるが、MYSQLでは幾つかDuplicate Key Errorの場合にどのように処理するか対応する方法がある。

dup

—————————————————————————————————-
IGNORE
重複データのINSERTを無視します。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(1,'col2_1','col3_1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert ignore into T_WITH_INDX values(1,'col2_1','col3_1');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.01 sec)

mysql>

ignore

—————————————————————————————————-
REPLACE
既存のデータをDELETEして新しいデータがINSERTされます。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.00 sec)

mysql> replace into T_WITH_INDX values(1,'col2_1','col3_1(2)');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2 |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql>

replace


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 3 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(2,'col2_2','col3_2(2)');
ERROR 1062 (23000): Duplicate entry 'col2_2' for key 'col2'
mysql> replace into T_WITH_INDX values(2,'col2_2','col3_2(2)');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

replace2


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql> replace into T_WITH_INDX values(1,'col2_2','col3_2(3)');
Query OK, 3 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_2 | col3_2(3) |
+------+--------+-----------+
1 row in set (0.00 sec)

mysql>

replace3

—————————————————————————————————-
ON DUPLICATE KEY UPDATE
既存のデータを変更(UPDATE)します。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.01 sec)

mysql> insert into T_WITH_INDX values(2,'col2_2(2)','col3_2(2)');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into T_WITH_INDX values(2,'col2_2(2)','col3_2(2)')
-> ON DUPLICATE KEY UPDATE col1 = col1+1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 3 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql>

dup_key


mysql> select * from T_WITH_INDX;
+------+-----------+-----------+
| col1 | col2 | col3 |
+------+-----------+-----------+
| 2 | col2_2(3) | col3_2 |
| 1 | col2_2 | col3_2(3) |
| 3 | col2_3 | col3_3 |
+------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(1,'col2_2(2)','col3_1')
-> ON DUPLICATE KEY UPDATE col2 = 'col1_1';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+-----------+-----------+
| col1 | col2 | col3 |
+------+-----------+-----------+
| 2 | col2_2(3) | col3_2 |
| 1 | col1_1 | col3_2(3) |
| 3 | col2_3 | col3_3 |
+------+-----------+-----------+
3 rows in set (0.00 sec)

on_dup

One Thought on “Unique Key が存在するテーブルへのデータINSERT処理

  1. Pingback: variable.jp » Blog Archive » 実践MYSQL制御フロー関数 IF

Post Navigation