INSERT Multiple Rows

Mengisi tabel dengan INSERT satu baris demi satu baris terasa wajar saat latihan, tetapi menjadi beban serius saat aplikasi harus memuat ratusan atau ribuan baris sekaligus. Setiap statement INSERT berbiaya: perjalanan bolak-balik ke server, waktu parsing, dan penulisan log transaksi. MySQL mengatasi hal ini dengan mengizinkan satu statement INSERT membawa banyak baris nilai. Pada Chapter ini kita akan membedah bentuk VALUES multi-baris, membaca output N rows affected secara cermat, memahami urutan baris dan penomoran AUTO_INCREMENT pada batch, membuktikan sifat atomiknya lewat satu kegagalan, lalu menutup dengan INSERT ... SELECT multi-baris dan catatan penting tentang max_allowed_packet.

Menyiapkan Tabel Latihan #

Kita mulai dari tabel kosong di database latihan belajar_dml:

CREATE DATABASE belajar_dml;
USE belajar_dml;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  stok INT NOT NULL DEFAULT 0
);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Struktur tabel sengaja sama dengan chapter-chapter sebelumnya. Kali ini kita tidak mengisinya baris demi baris, melainkan dalam batch — sesuai tema chapter.

Satu Statement, Banyak Baris #

Bentuk multi-baris menuliskan beberapa kelompok nilai yang dipisahkan koma setelah keyword VALUES. Kita isi tabel kosong kita dengan empat produk sekaligus:

INSERT INTO produk (nama, harga, stok) VALUES
  ('Buku Tulis', 4500.00, 120),
  ('Pensil 2B', 3000.00, 200),
  ('Penghapus', 2500.00, 150),
  ('Stipo', 6000.00, 40);

Output yang Dihasilkan:

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Mari kita bedah setiap bagiannya:

1. Baris 1 (INSERT INTO produk (nama, harga, stok) VALUES): Bagian pembuka sama persis dengan INSERT satu baris — nama tabel, daftar kolom, keyword VALUES. Daftar kolom cukup ditulis sekali untuk seluruh batch.
2. Baris 2-5 (('Buku Tulis', ...), hingga ('Stipo', ...);): Setiap kelompok dalam tanda kurung adalah satu baris data. Kelompok-kelompok tersebut dipisahkan koma, dan titik koma hanya ditulis setelah kelompok terakhir — tanda bahwa seluruh batch adalah satu statement utuh.

Output-nya memuat dua baris informasi. Baris Query OK, 4 rows affected menyatakan empat baris tersimpan, dan baris Records: 4 Duplicates: 0 Warnings: 0 merincikan jumlah baris yang dikirim, yang ditolak karena kunci ganda, dan yang memicu peringatan. Kita periksa hasilnya:

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

+----+------------+---------+------+
| id | nama       | harga   | stok |
+----+------------+---------+------+
|  1 | Buku Tulis | 4500.00 |  120 |
|  2 | Pensil 2B  | 3000.00 |  200 |
|  3 | Penghapus  | 2500.00 |  150 |
|  4 | Stipo      | 6000.00 |   40 |
+----+------------+---------+------+
4 rows in set (0.00 sec)

Urutan Baris dan Penomoran AUTO_INCREMENT #

Baris-baris dalam batch diproses sesuai urutan penulisannya, dan penomoran AUTO_INCREMENT mengikuti urutan tersebut. Kita tambahkan tiga produk baru dan langsung periksa nomornya:

INSERT INTO produk (nama, harga, stok) VALUES
  ('Map Plastik', 8500.00, 35),
  ('Kertas HVS A4', 25000.00, 15),
  ('Penghapus Putih', 3000.00, 90);

SELECT id, nama, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

+----+-----------------+------+
| id | nama            | stok |
+----+-----------------+------+
|  1 | Buku Tulis      |  120 |
|  2 | Pensil 2B       |  200 |
|  3 | Penghapus       |  150 |
|  4 | Stipo           |   40 |
|  5 | Map Plastik     |   35 |
|  6 | Kertas HVS A4   |   15 |
|  7 | Penghapus Putih |   90 |
+----+-----------------+------+
7 rows in set (0.00 sec)

Map Plastik yang ditulis pertama mendapat id 5, diikuti Kertas HVS A4 dengan id 6, dan Penghapus Putih dengan id 7. Fungsi LAST_INSERT_ID() setelah statement multi-baris mengembalikan id baris PERTAMA batch — bukan yang terakhir:

INSERT INTO produk (nama, harga, stok) VALUES
  ('Pensil Warna', 5000.00, 60),
  ('Rautan Pensil', 4000.00, 75);

SELECT LAST_INSERT_ID() AS id_baris_pertama;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

+------------------+
| id_baris_pertama |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

Respons menunjukkan angka 8 — nomor Pensil Warna, baris pertama dari dua baris batch tersebut. Perilaku ini penting untuk kode aplikasi: bila kita menghitung id baris terakhir dengan asumsi LAST_INSERT_ID() + jumlah_baris - 1, hasilnya memang benar untuk tabel tanpa selisih penomoran, tetapi angka resmi yang dijamin adalah tetap yang pertama.

Gagal Satu Baris, Gagal Semua #

Statement multi-baris bersifat atomik di tingkat statement: satu baris yang gagal membuat seluruh baris pada statement yang sama dibatalkan. Kita buktikan dengan sengaja menyisipkan id yang sudah ada di tengah batch:

-- @error
INSERT INTO produk (id, nama, harga, stok) VALUES (10, 'Buku Gambar', 6000.00, 50), (2, 'Stipo Baru', 7000.00, 30), (11, 'Klip Kertas', 1500.00, 100);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1062 (23000): Duplicate entry '2' for key 'produk.PRIMARY'
Bye

Galat ERROR 1062 terjadi karena id 2 sudah dipakai oleh Pensil 2B. Pertanyaannya: apakah baris pertama (Buku Gambar, id 10) yang ditulis sebelum baris bermasalah tetap tersimpan? Kita hitung isi tabel:

SELECT COUNT(*) AS jumlah_baris FROM produk;

Output yang Dihasilkan:

+--------------+
| jumlah_baris |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)

Jumlah baris tetap 9 — tidak ada satu pun baris baru yang tersimpan, termasuk Buku Gambar yang tidak bermasalah. InnoDB membatalkan seluruh pekerjaan statement ketika baris kedua gagal. Perilaku ini menguntungkan: batch tidak pernah tersimpan setengah jalan. Namun batch yang berisi data kotor akan menelan semua baris sehat di dalamnya — pastikan data sudah dibersihkan, atau pecah batch bila kegagalan parsial harus dihindari.

Menyalin Banyak Baris dengan INSERT … SELECT #

Bentuk INSERT ... SELECT yang kita kenal pada chapter INSERT juga bekerja untuk puluhan hingga jutaan baris tanpa kita menulis satu pun nilai secara manual. Kita salin seluruh produk ke tabel arsip:

CREATE TABLE produk_arsip (
  nama VARCHAR(100) NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

INSERT INTO produk_arsip (nama, harga)
SELECT nama, harga
FROM produk;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

Respons Query OK, 9 rows affected menegaskan sembilan baris tersalin sekaligus dalam satu statement. Aturan mainnya tetap: jumlah dan urutan kolom hasil SELECT harus cocok dengan daftar kolom tujuan, dan setiap baris hasil query menjadi satu baris baru di tabel tujuan. Kita baca kembali isinya:

SELECT * FROM produk_arsip ORDER BY nama;

Output yang Dihasilkan:

+-----------------+----------+
| nama            | harga    |
+-----------------+----------+
| Buku Tulis      |  4500.00 |
| Kertas HVS A4   | 25000.00 |
| Map Plastik     |  8500.00 |
| Penghapus       |  2500.00 |
| Penghapus Putih |  3000.00 |
| Pensil 2B       |  3000.00 |
| Pensil Warna    |  5000.00 |
| Rautan Pensil   |  4000.00 |
| Stipo           |  6000.00 |
+-----------------+----------+
9 rows in set (0.00 sec)

Batas Ukuran Statement: max_allowed_packet #

Satu statement multi-baris berukuran besar tetap harus lolos dari satu ambang: seluruh teks statement dikirim sebagai satu paket jaringan, dan ukuran paket maksimumnya diatur oleh variabel server max_allowed_packet. Kita periksa nilainya di server latihan:

SHOW VARIABLES LIKE 'max_allowed_packet';

Output yang Dihasilkan:

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

Nilai 67108864 byte setara dengan 64 MiB — nilai bawaan MySQL 8.4 untuk koneksi server-client. Statement yang lebih besar dari nilai ini ditolak server dengan galat, bukan dipotong diam-diam. Batasan ini jarang tercapai oleh baris pendek seperti contoh kita, tetapi sangat relevan saat batch berisi kolom teks panjang, banyak kolom, atau data JSON besar. Solusinya sederhana: pecah batch besar menjadi beberapa statement yang masing-masing muat di bawah ambang tersebut.

Membersihkan Database Latihan #

Latihan selesai. Database belajar_dml berisi dua tabel dan boleh dihapus:

DROP DATABASE belajar_dml;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• INSERT Statement — MySQL 8.4 Reference Manual
• INSERT … SELECT Statement — MySQL 8.4 Reference Manual
• Server System Variables — MySQL 8.4 Reference Manual

Batch multi-baris membuat pengisian data menjadi cepat, tetapi kita baru melihat satu sisi masalahnya: satu baris dengan kunci yang sudah ada menggagalkan seluruh statement. Bagaimana bila data itu memang harus masuk — baris baru disisipkan, baris lama diperbarui, keduanya dalam satu statement yang sama? Chapter berikutnya membahas INSERT ... ON DUPLICATE KEY UPDATE, pola upsert yang menjawab kebutuhan itu, lengkap dengan cara membaca output 1, 2, dan 0 rows affected yang sering membingungkan.