Statement insert ignore into dan insert on duplicate key update

Di MySQL kita tidak dapat menyimpan data yang sama (duplikasi) pada kolom yang memiliki primary key atau unique constraint tentunya karena dua hal tersebut bertentangan dengan aturan yang mengharuskan primary key dan unique constraint untuk menyimpan data secara unik, yaitu tidak ada duplikasi (kesamaan) data. Dalam hal ini penulis menanggap bahwa kalian telah memahami hal ini, namun disini yang akan dibahas adalah sejumlah statement yang tersedia pada mysql untuk menghindari pesan error duplicate entry ketika menambahkan duplikasi data ke sebuah tabel. Ada beberapa statement yang dapat digunakan, diantaranya:

• Insert... On Duplicate Key Update
• Insert Ignore

Berikut adalah penjelasannya.

INSERT... ON DUPLICATE KEY UPDATE

Untuk menghindari kesamaan data / duplicate entry saat menambahkan data, sejumlah dbms seperti oracle salah satunya memiliki statement MERGE INTO namun pada MySQL tidak mendukung pernyataan MERGE sehingga digunakan insert... on duplicate key update, hanya DBMS dengan ANSI SQL 2003 yang mendukung penggunaan statement MERGE, untuk memahami MERGE dapat melihat artikel berikut Merge (SQL). 

Konsep penggunaan statemen ini mirip dengan merge into yaitu data akan ditambahkan jika data yang akan ditambahkan tidak memiliki duplikasi (kesamaan) dari data yang sudah ada, namun data akan terupdate pada data yang sudah ada jika pernyataan insert yang dijalankan ternyata menghasilkan duplikasi data. Agar lebih jelas, berikut adalah format dan contohnya:

Format:

Insert into nama_tabel (kolom1, kolom2,...., kolomN) values (nilai_kolom_1, nilai_kolom_2.... nilai_kolomN) on duplicate key update nama_kolom = value; 




Contoh:

mysql> insert into contoh (kolom2, kolom3) values (1, "sepuluh") on duplicate key update kolom2 = 1;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from contoh;
+--------+--------+----------+
| kolom1 | kolom2 | kolom3   |
+--------+--------+----------+
|      1 |      1 | satu     |
|      2 |      2 | dua      |
|      3 |      3 | tiga     |
|      4 |      4 | empat    |
|      5 |      5 | lima     |
|      6 |      6 | enam     |
|      7 |      7 | tujuh    |
|      8 |      8 | delapan  |
|      9 |      9 | sembilan |
+--------+--------+----------+
9 rows in set (0.00 sec)

Dari contoh diatas dapat dilihat bahwa tidak ada data yang ditambahkan karena memiliki duplikasi data pada kolom2 yang juga memiliki constraint unique serta tidak adanya parameter lain pada statement on duplicate key update, sementara itu pesan error 1063 tidak ditampilkan. Lalu perhatikan contoh berikut:

mysql> insert into contoh (kolom2, kolom3) values (10, "sepuluh") on duplicate key update kolom2 = 1;
Query OK, 1 row affected (0.10 sec)

mysql> select * from contoh;
+--------+--------+----------+
| kolom1 | kolom2 | kolom3   |
+--------+--------+----------+
|      1 |      1 | satu     |
|      2 |      2 | dua      |
|      3 |      3 | tiga     |
|      4 |      4 | empat    |
|      5 |      5 | lima     |
|      6 |      6 | enam     |
|      7 |      7 | tujuh    |
|      8 |      8 | delapan  |
|      9 |      9 | sembilan |
|     11 |     10 | sepuluh  |
+--------+--------+----------+
10 rows in set (0.01 sec)

Dapat dilihat bahwa data dapat ditambahkan karena nilai pada kolom2 tidak memiliki duplikasi data dimana nilai untuk kolom2 sebelumnya hanya sampai 9, contoh lainnya adalah sebagai berikut:

mysql> insert into contoh (kolom2, kolom3) values (10, "sepuluh") on duplicate key update kolom2 = 11, kolom3 = "sebelas";
Query OK, 2 rows affected (0.05 sec)

mysql> select * from contoh;
+--------+--------+----------+
| kolom1 | kolom2 | kolom3   |
+--------+--------+----------+
|      1 |      1 | satu     |
|      2 |      2 | dua      |
|      3 |      3 | tiga     |
|      4 |      4 | empat    |
|      5 |      5 | lima     |
|      6 |      6 | enam     |
|      7 |      7 | tujuh    |
|      8 |      8 | delapan  |
|      9 |      9 | sembilan |
|     11 |     11 | sebelas  |
+--------+--------+----------+
10 rows in set (0.00 sec)

Pada contoh mysql tidak menambahkan data baru karena nilai 10 pada kolom2 sudah ada sehingga dengan query tersebut yang terjadi adalah mengupdate data sesuai dengan parameter on duplicate key update yang dibuat. Dari semua contoh diatas sebenarnya insert... on duplicate key update masih dapat menampilkan error duplicate entry dengan contoh sebagai berikut:

mysql> insert into contoh (kolom2, kolom3) values (2, "sepuluh") on duplicate key update kolom2 = 1;
ERROR 1062 (23000): Duplicate entry '1' for key 'contoh.kolom2'
mysql> insert into contoh (kolom2, kolom3) values (2, "sepuluh") on duplicate key update kolom2 = 1, kolom3 = "satusatu";

Berdasarkan contoh diatas apakah kalian tahu mengapa query diatas bisa menghasilkan error 1062? Jika 'ya' maka silahkan jabarkan di kolom komentar jawaban kalian dengan tujuan bahwa kalian telah memahami konesep dari insert on duplicate key update.

Insert Ignore Into

Pernyataan insert ignore into hampir mirip dengan insert on duplicate key update hanya saja pada insert ignore into tidak akan memperbaharui (update) data yang sudah ada, insert ignore akan menambahkan data jika tidak adanya kesamaan data namun menghilangkan pesan error 1062 ketika menambahkan data yang sama dan tidak akan menyimpan data tersebut, contohnya seperti berikut:

Format:

insert ignore into nama_tabel (kolom1, kolom2,...., kolomN) values (nilai1, nilai2, nilai3,..., NilaiN);

Contoh:

mysql> insert into contoh (kolom2, kolom3) values (1, "sepuluh");
ERROR 1062 (23000): Duplicate entry '1' for key 'contoh.kolom2'
mysql> insert ignore into contoh (kolom2, kolom3) values (1, "sepuluh");
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> insert ignore into contoh (kolom2, kolom3) values (10, "sepuluh");
Query OK, 1 row affected (0.01 sec)

mysql> select * from contoh;
+--------+--------+----------+
| kolom1 | kolom2 | kolom3   |
+--------+--------+----------+
|      1 |      1 | satu     |
|      2 |      2 | dua      |
|      3 |      3 | tiga     |
|      4 |      4 | empat    |
|      5 |      5 | lima     |
|      6 |      6 | enam     |
|      7 |      7 | tujuh    |
|      8 |      8 | delapan  |
|      9 |      9 | sembilan |
|     12 |     10 | sepuluh  |
+--------+--------+----------+
10 rows in set (0.08 sec)

Dari contoh diatas dapat dilihat ketika menggunakan insert ignore into maka pesan error tidak akan ditampilkan namun akan menghasilkan pesan warning yang sebetulnya berisi pesan error 1062, selain itu dapat masih dapat ditambahkan ketika tidak ada duplikasi data.

Pada dasarnya kedua statement diatas tidak membuat mysql benar-benar dapat menyimpan data duplikasi pada kolom yang memiliki primary key atau unique constraint melainkan hanya menghilangkan pesan error duplicate entry for key atau error 1062. Dengan insert on duplicate key update mysql akan menambahkan data baru ataupun mengupdate data yang sudah ada berdasarkan parameter on duplicate key yang di buat, sementara statement insert ignore into hanya akan menambahkan data jika memang tidak menghasilkan duplikasi data dan tidak akan menampilkan pesan error ataupun menyimpan data jika menghasilkan duplikasi.