KAL
Menu

REPLACE dan Pola Upsert

Idiom sisip-atau-perbarui: REPLACE, ON DUPLICATE KEY, dan perbedaan perilakunya.

Daftar isi

REPLACE dan ON DUPLICATE KEY UPDATE adalah dua idiom untuk pola upsert, yaitu sisip-atau-perbarui: baris disisipkan apabila kuncinya belum ada dan diperbarui apabila kuncinya sudah terdaftar. Kedua bentuk itu sering dianggap saling menggantikan, padahal mekanisme di baliknya berbeda secara mendasar. Topik ini membahas cara kerja REPLACE yang menghapus baris lama lalu menyisipkan baris baru, ON DUPLICATE KEY UPDATE yang memperbarui baris tanpa menghapusnya, serta konsekuensi keduanya terhadap nilai AUTO_INCREMENT, kolom yang tidak disebutkan, dan trigger.

Tabel contoh dengan kunci unik#

Skenario contohnya adalah pembaruan berkala data kartu anggota perpustakaan dari hasil ekspor sistem pendaftaran. Tabel kartu_anggota menyimpan satu baris per kartu, dengan kolom nomor_kartu yang dijaga UNIQUE KEY bernama uq_nomor agar satu nomor tidak dimiliki dua baris. Kolom id bertipe AUTO_INCREMENT menjadi PRIMARY KEY, kolom saldo bernilai default 0, dan kolom masa_berlaku bertipe DATE.

Skema juga memasang trigger, yaitu prosedur yang dijalankan otomatis oleh server pada peristiwa INSERT, UPDATE, atau DELETE pada sebuah tabel. Trigger bernama catat_hapus pada contoh dijalankan setiap kali baris kartu terhapus dan mencatat nomor serta nama kartunya ke tabel log_hapus. Dengan begitu, pernyataan yang menghapus baris secara diam-diam dapat dikenali dari isi log tersebut. Skema berikut membuat ulang basis data sirkulasi dari nol, sehingga setiap contoh menghasilkan keadaan awal yang sama persis.

sql skema-kartu.sql 
-- tidak-dijalankan
DROP DATABASE IF EXISTS sirkulasi;
CREATE DATABASE sirkulasi;
USE sirkulasi;

CREATE TABLE kartu_anggota (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nomor_kartu  VARCHAR(12) NOT NULL,
  nama         VARCHAR(80) NOT NULL,
  saldo        INT UNSIGNED NOT NULL DEFAULT 0,
  masa_berlaku DATE NOT NULL,
  UNIQUE KEY uq_nomor (nomor_kartu)
);

CREATE TABLE log_hapus (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nomor_kartu VARCHAR(12) NOT NULL,
  nama        VARCHAR(80) NOT NULL
);

CREATE TRIGGER catat_hapus AFTER DELETE ON kartu_anggota
FOR EACH ROW
INSERT INTO log_hapus (nomor_kartu, nama)
VALUES (OLD.nomor_kartu, OLD.nama);

INSERT INTO kartu_anggota (nomor_kartu, nama, saldo, masa_berlaku) VALUES
  ('K-0001', 'Ali Rahman',    20000, '2027-03-31'),
  ('K-0002', 'Budi Hartono',  15000, '2027-05-31'),
  ('K-0003', 'Citra Lestari', 25000, '2027-07-31');
bash 
# membuat ulang basis data sirkulasi beserta tabel dan trigger-nya
mysql -u root < skema-kartu.sql

REPLACE menghapus lalu menyisipkan#

REPLACE menyisipkan baris seperti INSERT biasa, tetapi apabila PRIMARY KEY atau kunci unik lainnya bertabrakan, baris lama dihapus lebih dahulu dan baris baru disisipkan setelahnya. Baris lama tidak diperbarui, melainkan benar-benar digantikan oleh baris yang baru. Pernyataan berikut menyisipkan kartu K-0002 yang nomornya sudah terdaftar, lalu memeriksa ROW_COUNT(), isi tabel kartu, dan isi log penghapusan pada sesi yang sama.

sql Sintaks REPLACE 
-- tidak-dijalankan
REPLACE INTO nama_tabel (kolom_kunci, kolom_1, kolom_2)
VALUES (nilai_kunci, nilai_1, nilai_2);
bash 
# menyisipkan kartu dengan nomor yang sudah terdaftar melalui REPLACE
mysql -t -u root sirkulasi <<'SQL'
REPLACE INTO kartu_anggota (nomor_kartu, nama, masa_berlaku)
VALUES ('K-0002', 'Budi Hartono', '2027-10-31');

SELECT ROW_COUNT() AS baris_terdampak;
SELECT id, nomor_kartu, nama, saldo, masa_berlaku FROM kartu_anggota ORDER BY id;
SELECT id, nomor_kartu, nama FROM log_hapus;
SQL
text Keluaran 
+-----------------+
| baris_terdampak |
+-----------------+
|               2 |
+-----------------+
+----+-------------+---------------+-------+--------------+
| id | nomor_kartu | nama          | saldo | masa_berlaku |
+----+-------------+---------------+-------+--------------+
|  1 | K-0001      | Ali Rahman    | 20000 | 2027-03-31   |
|  3 | K-0003      | Citra Lestari | 25000 | 2027-07-31   |
|  4 | K-0002      | Budi Hartono  |     0 | 2027-10-31   |
+----+-------------+---------------+-------+--------------+
+----+-------------+--------------+
| id | nomor_kartu | nama         |
+----+-------------+--------------+
|  1 | K-0002      | Budi Hartono |
+----+-------------+--------------+

Keluaran tersebut memperlihatkan tiga akibat sekaligus. Pertama, ROW_COUNT() bernilai 2 karena satu baris dihapus dan satu baris disisipkan. Kedua, kartu K-0002 yang semula ber-id 2 kini ber-id 4, yaitu nilai terbaru dari penghitung AUTO_INCREMENT, sedangkan baris dengan id 2 sudah tidak ada. Ketiga, kolom saldo yang tidak disebutkan pada pernyataan REPLACE kembali ke nilai default 0, dan tabel log_hapus berisi satu baris karena trigger DELETE ikut dijalankan.

ON DUPLICATE KEY UPDATE memperbarui tanpa menghapus#

ON DUPLICATE KEY UPDATE menangani konflik yang sama tanpa menghapus apa pun. MySQL mencoba menyisipkan baris seperti biasa, dan apabila PRIMARY KEY atau kunci unik bertabrakan, pernyataan berubah menjadi UPDATE terhadap baris lama sesuai klausa yang ditulis. Baris lama tetap hidup, sehingga id-nya tidak berubah, kolom yang tidak disebutkan tidak tersentuh, dan trigger DELETE tidak pernah dijalankan.

Basis data dibuat ulang dengan skema yang sama agar perbandingan dengan contoh REPLACE sebelumnya setara, lalu pernyataan berikut menyisipkan kartu K-0002 yang nomornya sudah terdaftar.

bash 
# membuat ulang basis data untuk pembanding yang setara
mysql -u root < skema-kartu.sql

# menyisipkan kartu dengan nomor yang sudah terdaftar melalui upsert
mysql -t -u root sirkulasi <<'SQL'
INSERT INTO kartu_anggota (nomor_kartu, nama, masa_berlaku)
VALUES ('K-0002', 'Budi Hartono', '2027-10-31') AS baru
ON DUPLICATE KEY UPDATE masa_berlaku = baru.masa_berlaku;

SELECT ROW_COUNT() AS baris_terdampak;
SELECT id, nomor_kartu, nama, saldo, masa_berlaku FROM kartu_anggota ORDER BY id;
SELECT COUNT(*) AS jumlah_hapus FROM log_hapus;
SQL
text Keluaran 
+-----------------+
| baris_terdampak |
+-----------------+
|               2 |
+-----------------+
+----+-------------+---------------+-------+--------------+
| id | nomor_kartu | nama          | saldo | masa_berlaku |
+----+-------------+---------------+-------+--------------+
|  1 | K-0001      | Ali Rahman    | 20000 | 2027-03-31   |
|  2 | K-0002      | Budi Hartono  | 15000 | 2027-10-31   |
|  3 | K-0003      | Citra Lestari | 25000 | 2027-07-31   |
+----+-------------+---------------+-------+--------------+
+--------------+
| jumlah_hapus |
+--------------+
|            0 |
+--------------+

Hasilnya berbeda dari REPLACE pada setiap titik yang diukur. ROW_COUNT() tetap bernilai 2, tetapi angka itu kini berarti satu baris yang diperbarui, bukan gabungan penghapusan dan penyisipan. Baris K-0002 masih ber-id 2, kolom saldo tetap bernilai 15000 karena tidak disebutkan pada klausa pembaruan, dan jumlah_hapus bernilai 0 karena tidak ada trigger DELETE yang dijalankan.

Perilaku ketika tidak ada konflik#

Ketika nomor kartu yang disisipkan belum terdaftar, tidak ada konflik yang perlu diselesaikan, sehingga REPLACE dan ON DUPLICATE KEY UPDATE sama-sama berlaku sebagai INSERT biasa. Pernyataan berikut menyisipkan dua kartu baru, satu melalui REPLACE dan satu melalui ON DUPLICATE KEY UPDATE, lalu membaca ROW_COUNT() setelah masing-masing pernyataan.

bash 
# kedua idiom berperilaku sebagai INSERT biasa tanpa konflik kunci
mysql -t -u root sirkulasi <<'SQL'
REPLACE INTO kartu_anggota (nomor_kartu, nama, masa_berlaku)
VALUES ('K-0004', 'Dewi Sartika', '2027-12-31');

SELECT ROW_COUNT() AS replace_baris_baru;

INSERT INTO kartu_anggota (nomor_kartu, nama, masa_berlaku)
VALUES ('K-0005', 'Rina Kartika', '2027-12-31') AS baru
ON DUPLICATE KEY UPDATE masa_berlaku = baru.masa_berlaku;

SELECT ROW_COUNT() AS upsert_baris_baru;
SQL
text Keluaran 
+--------------------+
| replace_baris_baru |
+--------------------+
|                  1 |
+--------------------+
+-------------------+
| upsert_baris_baru |
+-------------------+
|                 1 |
+-------------------+

ROW_COUNT() bernilai 1 untuk keduanya, yaitu satu baris baru yang tersimpan. Perbedaan perilaku kedua idiom hanya muncul pada konflik kunci, sedangkan penyisipan tanpa konflik menghasilkan keadaan yang sama.

Kesetaraan pada kasus tanpa konflik ini penting ketika berkas yang diimpor berisi campuran baris baru dan baris lama, misalnya hasil ekspor harian sistem pendaftaran. Kedua idiom menentukan sisip atau perbarui per baris, bukan per pernyataan, sehingga baris yang kuncinya bentrok diperbarui dan baris yang kuncinya baru disisipkan dalam satu pernyataan yang sama. Pemilahan baris tidak perlu dilakukan terlebih dahulu, dan baris lama yang tidak tercantum pada data tidak tersentuh sama sekali.

Memilih antara REPLACE dan ON DUPLICATE KEY UPDATE#

Perbedaan perilaku kedua idiom dapat diringkas dalam tabel berikut.

AspekREPLACEON DUPLICATE KEY UPDATE
Mekanisme saat konflikMenghapus baris lama, lalu menyisipkan baris baruMemperbarui baris lama sesuai klausa
Nilai id baris lamaBerubah mengikuti penghitung AUTO_INCREMENT terbaruTetap
Kolom yang tidak disebutkanKembali ke nilai defaultTetap bernilai lama
Trigger DELETEDijalankanTidak dijalankan
ROW_COUNT() saat konflik2 (satu hapus, satu sisip)2 bila nilai berubah, 0 bila sama

Sebagai aturan praktis, jadikan ON DUPLICATE KEY UPDATE pilihan utama untuk pola upsert karena idiom itu memperbarui data tanpa menyentuh bagian lain dari barisnya. REPLACE lebih cocok untuk penggantian baris utuh, misalnya memuat salinan terbaru satu entitas dari sistem lain, ketika hilangnya kolom yang tidak disebutkan memang dikehendaki. Perhatikan pula hubungan kedua idiom dengan foreign key. Baris anak yang merujuk id baris yang dihapus REPLACE dapat menggantung atau membuat pernyataan itu gagal, tergantung constraint yang berlaku, sedangkan ON DUPLICATE KEY UPDATE tidak memindahkan id sehingga seluruh rujukan tetap sah.

Kesalahan umum#

Kesalahan pertama adalah memakai REPLACE sebagai perintah pembaruan rutin. Setiap pembaruan menjadi penghapusan dan penyisipan, sehingga kolom yang lupa disebutkan kembali ke nilai default, penghitung AUTO_INCREMENT terus bertambah, dan trigger DELETE dijalankan berulang kali. Untuk kebutuhan pembaruan sebagian, gunakan ON DUPLICATE KEY UPDATE atau UPDATE biasa.

Kesalahan kedua adalah menganggap konflik hanya datang dari PRIMARY KEY. REPLACE dan ON DUPLICATE KEY UPDATE menguji setiap UNIQUE KEY pada tabel, termasuk uq_nomor pada contoh ini. Baris yang bertabrakan pada kunci sekunder pun ikut dihapus oleh REPLACE, sehingga pernyataan yang dimaksudkan menyisipkan kartu baru dapat menghapus kartu lain tanpa disadari.

Kesalahan ketiga adalah membaca ROW_COUNT() bernilai 2 sebagai tanda dua baris berubah. Pada REPLACE, angka 2 adalah gabungan satu penghapusan dan satu penyisipan dari satu baris logis. Pada ON DUPLICATE KEY UPDATE, angka 2 memang berarti satu baris diperbarui, sedangkan angka 0 berarti pembaruan menghasilkan nilai yang sama dengan nilai lama.

Ringkasan#

  • REPLACE menyelesaikan konflik kunci dengan menghapus baris lama lalu menyisipkan baris baru, sedangkan ON DUPLICATE KEY UPDATE mengubah sisipan menjadi pembaruan.
  • Konsekuensi REPLACE adalah id yang berubah mengikuti penghitung AUTO_INCREMENT, kolom yang tidak disebutkan kembali ke nilai default, dan trigger DELETE yang ikut dijalankan.
  • ON DUPLICATE KEY UPDATE mempertahankan id, mempertahankan kolom yang tidak disebutkan, dan tidak menjalankan trigger DELETE.
  • Tanpa konflik, keduanya berlaku sebagai INSERT biasa dan ROW_COUNT() bernilai 1.

Referensi#