Dunia MySQL & MariaDB

Panduan Lengkap UPSERT di MySQL: INSERT dan UPDATE dalam Satu Query

Dasar-dasar SQL
目次

1. Apa itu UPSERT?

Ringkasan

“UPSERT” adalah fitur dalam database yang menggabungkan fungsi “INSERT” dan “UPDATE”. Artinya, jika data baru belum ada maka data akan dimasukkan, sedangkan jika data dengan nilai yang sama sudah ada maka data tersebut akan diperbarui. Dengan fitur ini, konsistensi data tetap terjaga sekaligus operasi dapat dilakukan lebih efisien.

Di MySQL, sintaks INSERT ... ON DUPLICATE KEY UPDATE digunakan untuk tujuan ini. Dengan sintaks ini, kita bisa menghindari error duplikasi kunci dan memperbarui data yang sudah ada.

Kapan digunakan

  • Sistem Manajemen Pelanggan (CRM): Menambahkan data pelanggan baru jika belum ada, atau memperbarui informasi pelanggan lama jika ada perubahan.
  • Manajemen Stok Produk: Menambahkan produk baru sekaligus memperbarui jumlah stok produk yang sudah ada.

Keuntungan UPSERT di MySQL

  • Menghindari error duplikasi kunci
  • Sintaks SQL menjadi lebih sederhana
  • Menjaga konsistensi data

2. Cara dasar menggunakan UPSERT di MySQL

Di MySQL, operasi UPSERT dapat dilakukan dengan sintaks INSERT ... ON DUPLICATE KEY UPDATE. Jika terjadi duplikasi kunci, maka data lama akan diperbarui alih-alih menambahkan data baru.

Sintaks dasar

INSERT INTO nama_tabel (kolom1, kolom2)
VALUES (nilai1, nilai2)
ON DUPLICATE KEY UPDATE
kolom1 = nilai1, kolom2 = nilai2;

Penjelasan:

  1. INSERT INTO digunakan untuk menambahkan data baru.
  2. Jika data yang ingin dimasukkan sudah ada di tabel, bagian ON DUPLICATE KEY UPDATE akan dijalankan untuk memperbarui data tersebut.

Contoh:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

Pada contoh di atas, jika sudah ada pengguna dengan user_id bernilai 1, maka field name akan diperbarui menjadi ‘Taro Tanaka’. Jika belum ada, data baru akan dimasukkan.

3. Detail sintaks UPSERT dan contoh

Memperbarui beberapa kolom

Saat menggunakan UPSERT, kita bisa memperbarui hanya kolom tertentu dengan menyebutkannya di bagian ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Pada contoh ini, jika produk dengan product_id 100 sudah ada, hanya price yang diperbarui, sedangkan kolom lain seperti name tidak berubah.

4. Perbedaan dengan database lain

Selain MySQL, database lain juga memiliki fitur serupa. Misalnya PostgreSQL atau SQLite menggunakan sintaks INSERT ... ON CONFLICT atau MERGE sebagai alternatif UPSERT.

Contoh di PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

Di PostgreSQL atau SQLite, kita menggunakan klausa ON CONFLICT untuk mengatur apa yang dilakukan jika terjadi duplikasi kunci. Sementara itu, di MySQL digunakan ON DUPLICATE KEY UPDATE.

Keunikan MySQL

  • MySQL menggunakan INSERT ... ON DUPLICATE KEY UPDATE yang berbeda sintaks dengan database lain. Oleh karena itu, perlu perhatian saat melakukan migrasi antar DBMS.

5. Penggunaan UPSERT tingkat lanjut

Bulk UPSERT (proses banyak record sekaligus)

UPSERT tidak hanya bisa untuk satu baris data, tetapi juga bisa untuk banyak baris sekaligus. Dengan ini, efisiensi operasi database bisa meningkat drastis.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Pada contoh ini, beberapa produk ditambahkan sekaligus, dan jika ada kunci yang sama maka hanya harga produk yang diperbarui.

UPSERT dengan Stored Procedure

Untuk efisiensi, UPSERT juga bisa dibungkus ke dalam stored procedure. Hal ini membuat kode bisa digunakan ulang, lebih mudah dibaca, dan lebih terjaga dalam pemeliharaan.

6. Risiko dan hal yang perlu diperhatikan pada UPSERT

Transaksi dan Deadlock

Jika digunakan pada data dalam jumlah besar, UPSERT bisa menimbulkan risiko deadlock. Terutama jika level isolasi transaksi MySQL disetel ke REPEATABLE READ, kemungkinan gap lock menjadi lebih tinggi.

Cara menghindari gap lock

  • Mengubah level isolasi transaksi ke READ COMMITTED untuk mengurangi kemungkinan deadlock.
  • Memecah operasi UPSERT menjadi beberapa query terpisah jika diperlukan.

7. Kesimpulan

Fitur UPSERT di MySQL sangat berguna untuk menyederhanakan operasi insert dan update sekaligus menghindari error duplikasi kunci. Namun, ada risiko deadlock dan pengaturan transaksi yang harus diperhatikan. Jika digunakan dengan tepat, UPSERT membuat pengelolaan database lebih sederhana dan efisien.