Practical MySQL & MariaDB

MySQL UPSERT Dijelaskan: Panduan INSERT … ON DUPLICATE KEY UPDATE dengan Contoh

Dasar-Dasar SQL
目次

1. Apa Itu UPSERT?

Tinjauan

“UPSERT” merujuk pada fitur database yang menggabungkan operasi “INSERT” dan “UPDATE”. Dengan kata lain, jika data belum ada, data tersebut dimasukkan; jika data yang sama sudah ada, data tersebut diperbarui. Dengan menggunakan fitur ini, Anda dapat melakukan operasi yang efisien sambil menjaga konsistensi data.

Di MySQL, fungsionalitas ini diimplementasikan menggunakan sintaks INSERT ... ON DUPLICATE KEY UPDATE. Fitur ini memungkinkan Anda untuk menghindari kesalahan kunci duplikat dan memperbarui catatan yang ada bahkan ketika kunci duplikat terjadi.

Kasus Penggunaan

  • Sistem Manajemen Pelanggan : Tambahkan data pelanggan baru jika belum ada, dan perbarui informasi pelanggan yang ada ketika berubah.
  • Manajemen Inventaris Produk : Tambahkan produk baru sambil memperbarui jumlah stok produk yang ada.

Keuntungan UPSERT di MySQL

  • Menghindari kesalahan kunci duplikat
  • Menyederhanakan kueri SQL
  • Menjaga integritas data

2. Penggunaan Dasar UPSERT di MySQL

Di MySQL, operasi UPSERT dilakukan menggunakan sintaks INSERT ... ON DUPLICATE KEY UPDATE. Dengan sintaks ini, jika kunci duplikat terjadi, Anda dapat memperbarui sebagian atau seluruh data yang ada alih-alih memasukkan data baru.

Sintaks Dasar

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Penjelasan:

  1. Gunakan INSERT INTO untuk memasukkan data.
  2. Jika data yang dimasukkan sudah ada di tabel, klausa ON DUPLICATE KEY UPDATE dieksekusi, dan data yang ada diperbarui.

Contoh:

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

Dalam contoh di atas, jika pengguna dengan user_id 1 sudah ada, kolom name diperbarui menjadi ‘Taro Tanaka’. Jika pengguna tidak ada, catatan baru dimasukkan.

3. Sintaks SQL Mendetail dan Contoh UPSERT

Memperbarui Beberapa Kolom

Saat menggunakan UPSERT, ada kasus di mana Anda mungkin ingin memperbarui hanya kolom tertentu. Dalam situasi seperti itu, Anda dapat menentukan hanya kolom yang diperlukan dalam klausa ON DUPLICATE KEY UPDATE.

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

Dalam contoh ini, jika produk dengan product_id 100 sudah ada, hanya kolom price yang diperbarui, sementara kolom lain (seperti name) tetap tidak berubah.

4. Perbedaan dengan Database Lain

Database selain MySQL juga menyediakan fungsionalitas serupa. Misalnya, PostgreSQL dan SQLite menggunakan pernyataan INSERT ... ON CONFLICT atau MERGE untuk mencapai perilaku seperti UPSERT.

Contoh PostgreSQL

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

Di PostgreSQL dan SQLite, klausa ON CONFLICT digunakan untuk mengontrol perilaku ketika kesalahan kunci duplikat terjadi. Sebaliknya, MySQL menggunakan klausa ON DUPLICATE KEY UPDATE.

Karakteristik Khusus MySQL

  • MySQL menggunakan INSERT ... ON DUPLICATE KEY UPDATE , dan karena sintaksnya berbeda dari database lain, diperlukan perhatian khusus saat migrasi antar sistem.

5. Teknik UPSERT Lanjutan

Bulk UPSERT (Pemrosesan Batch untuk Beberapa Catatan)

UPSERT dapat dieksekusi tidak hanya untuk satu catatan tetapi juga untuk beberapa catatan sekaligus. Ini secara signifikan meningkatkan efisiensi operasi database.

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

Dalam contoh ini, beberapa catatan produk dimasukkan sekaligus. Jika kunci duplikat ada, hanya kolom price dari catatan yang sesuai yang diperbarui.

Menggunakan Stored Procedure untuk UPSERT

Untuk mengoptimalkan pemrosesan UPSERT, Anda juga dapat menggunakan prosedur tersimpan. Ini memungkinkan Anda membuat logika yang dapat digunakan kembali di dalam basis data, meningkatkan keterbacaan serta pemeliharaan kode Anda.

6. Kesalahan Umum dan Pertimbangan Penting

Transaksi dan Deadlock

Saat menggunakan UPSERT—terutama dengan volume data yang besar—deadlock dapat terjadi. Jika tingkat isolasi transaksi MySQL disetel ke REPEATABLE READ, gap lock lebih mungkin terjadi.

Menghindari Gap Lock

  • Anda dapat mengurangi kemungkinan deadlock dengan mengubah tingkat isolasi transaksi menjadi READ COMMITTED.
  • Jika diperlukan, pertimbangkan untuk memecah operasi UPSERT yang besar menjadi batch yang lebih kecil dan mengeksekusi beberapa kueri alih‑alih satu pernyataan besar.

7. Kesimpulan

Fitur UPSERT MySQL sangat berguna untuk menyederhanakan penyisipan dan pembaruan data sekaligus menghindari kesalahan kunci duplikat. Namun, menerapkan UPSERT memerlukan pertimbangan cermat terhadap potensi deadlock dan pengaturan transaksi. Bila digunakan dengan tepat, UPSERT memungkinkan operasi basis data yang lebih sederhana dan lebih efisien.