AUTO_INCREMENT

Kunci surrogate, konvensi kolom id, dan perilaku penomoran otomatis InnoDB.

Daftar isi

Atribut AUTO_INCREMENT membuat server mengisi sebuah kolom dengan nomor berurutan untuk setiap baris baru yang tidak menyebut nilai kolom tersebut. Atribut ini dipakai untuk membentuk surrogate key, yaitu kunci teknis yang dipilih semata sebagai identitas baris dan tidak membawa makna bisnis. Topik ini membahas alasan kolom id menjadi konvensi yang mapan, cara mendeklarasikannya, perilaku penomoran InnoDB yang menimbulkan nomor hilang, serta cara membaca dan mengatur counter penomorannya.

Surrogate key dan konvensi kolom id#

Nilai alamiah seperti email, nomor anggota, atau kode katalog terasa pantas menjadi kunci, tetapi semuanya bermasalah ketika dipakai sebagai pengenal fisik baris. Nilai alamiah dapat berubah, misalnya anggota memperbarui alamat emailnya, dan perubahan itu memaksa seluruh rujukan turut diubah. Kunci surrogate menghindari masalah ini karena nilainya tidak pernah perlu berubah, pendek, dan tidak bermakna apa pun di luar identitas.

Konvensi yang lazim pada tabel MySQL menetapkan kolom pertama bernama id, bertipe INT UNSIGNED, dengan atribut AUTO_INCREMENT dan peran PRIMARY KEY. Berkas skema berikut memakai konvensi tersebut.

sql skema-perpustakaan.sql

-- tidak-dijalankan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;

bash

# membuat ulang basis data perpustakaan dari nol
mysql -u root < skema-perpustakaan.sql

Sifat teknis AUTO_INCREMENT menuntut tiga hal. Atribut AUTO_INCREMENT hanya boleh melekat pada satu kolom per tabel, kolom tersebut wajib menjadi kunci, dan tipenya harus berupa tipe bilangan bulat. Kombinasi AUTO_INCREMENT dengan PRIMARY KEY memenuhi semuanya sekaligus.

Perilaku penomoran otomatis#

Isi tabel anggota dengan tiga baris yang tidak menyebut kolom id, lalu periksa hasilnya.

bash

# menyisipkan tiga baris tanpa menyebut kolom id
mysql -t -u root perpustakaan <<'SQL'
INSERT INTO anggota (nama, email)
VALUES ('Ali', '[email protected]'), ('Budi', '[email protected]'), ('Citra', '[email protected]');
SELECT id, nama, email FROM anggota ORDER BY id;
SQL

text Keluaran

+----+-------+-------------------+
| id | nama  | email             |
+----+-------+-------------------+
|  1 | Ali   | [email protected]   |
|  2 | Budi  | [email protected]  |
|  3 | Citra | [email protected] |
+----+-------+-------------------+

Server memberi nomor 1, 2, dan 3 sesuai urutan penyisipan. Nomor diberikan pada saat penyisipan diproses, bukan pada saat transaksi dikomit. Pada tabel yang diproses banyak koneksi sekaligus, dua transaksi bisa memperoleh nomor berurutan tetapi menulis barisnya dengan urutan terbalik. Sifat ini wajar karena tugas kolom id hanyalah membedakan baris, bukan mencatat urutan kejadian. Definisi kanonis tabel kemudian memperlihatkan posisi counter penomorannya, yaitu nilai yang akan diberikan kepada baris berikutnya.

bash

# menampilkan definisi kanonis tabel anggota beserta counter-nya
mysql -t -u root perpustakaan -e "SHOW CREATE TABLE anggota\G"

text Keluaran

*************************** 1. row ***************************
       Table: anggota
Create Table: CREATE TABLE `anggota` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Klausa AUTO_INCREMENT=4 pada definisi tersebut adalah counter tabel, bukan jumlah barisnya. Counter ini selalu bergerak maju, dan nilai terbarunya juga dapat dilihat melalui information_schema tanpa menampilkan seluruh definisi tabel.

Menyebut id secara eksplisit#

AUTO_INCREMENT tidak melarang penyebutan nilai id secara eksplisit. Penyisipan dengan id tertentu memindahkan counter apabila nilainya melebihi posisi saat ini, dan penyisipan tanpa id setelahnya melanjutkan dari situ. Fungsi LAST_INSERT_ID() melaporkan nomor terakhir yang dihasilkan server dalam koneksi yang sama, sehingga aplikasi dapat mengetahui identitas baris yang baru saja disisipkannya.

bash

# menyisipkan satu baris dengan id eksplisit, lalu satu baris otomatis
mysql -t -u root perpustakaan <<'SQL'
INSERT INTO anggota (id, nama, email) VALUES (10, 'Dewi', '[email protected]');
INSERT INTO anggota (nama, email) VALUES ('Eka', '[email protected]');
SELECT LAST_INSERT_ID();
SELECT id, nama, email FROM anggota ORDER BY id;
SQL

text Keluaran

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               11 |
+------------------+
+----+-------+-------------------+
| id | nama  | email             |
+----+-------+-------------------+
|  1 | Ali   | [email protected]   |
|  2 | Budi  | [email protected]  |
|  3 | Citra | [email protected] |
| 10 | Dewi  | [email protected]  |
| 11 | Eka   | [email protected]   |
+----+-------+-------------------+

Fungsi LAST_INSERT_ID() mengembalikan 11, yaitu nomor yang baru saja diberikan kepada baris Eka. Nilai ini milik koneksi, bukan milik tabel, sehingga penyisipan dari koneksi lain tidak menggeser hasilnya.

Nomor yang hilang#

Counter tidak pernah berjalan mundur, dan nomor yang sudah diberikan tidak pernah dipakai ulang. Hapus baris Eka yang berada di urutan terakhir, lalu sisipkan satu baris baru.

bash

# menghapus baris terakhir, lalu menyisipkan baris baru
mysql -t -u root perpustakaan <<'SQL'
DELETE FROM anggota WHERE id = 11;
INSERT INTO anggota (nama, email) VALUES ('Farah', '[email protected]');
SELECT id, nama, email FROM anggota ORDER BY id;
SQL

text Keluaran

+----+-------+-------------------+
| id | nama  | email             |
+----+-------+-------------------+
|  1 | Ali   | [email protected]   |
|  2 | Budi  | [email protected]  |
|  3 | Citra | [email protected] |
| 10 | Dewi  | [email protected]  |
| 12 | Farah | [email protected] |
+----+-------+-------------------+

Baris baru memperoleh nomor 12, dan nomor 11 tetap hilang meskipun barisnya sudah tidak ada. Selain penghapusan, penyisipan yang gagal juga mengonsumsi nomor, misalnya penyisipan yang ditolak karena melanggar UNIQUE, demikian pula transaksi yang di-ROLLBACK. Celah seperti ini adalah perilaku normal, dan tabel yang sibuk dapat memuat banyak celah.

Membaca dan mengatur counter#

Daftar kolom yang memakai AUTO_INCREMENT juga dapat ditelusuri dari information_schema.columns, yaitu kolom yang nilai EXTRA-nya memuat auto_increment. Posisi counter itu sendiri terbaca pada klausa AUTO_INCREMENT di definisi kanonis di atas, atau dari kolom AUTO_INCREMENT pada information_schema.tables.

bash

# menelusuri kolom AUTO_INCREMENT melalui information_schema
mysql -t -u root -e "SELECT table_name, column_name, column_type, extra FROM information_schema.columns WHERE table_schema = 'perpustakaan' AND extra LIKE '%auto_increment%'"

text Keluaran

+------------+-------------+--------------+----------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE  | EXTRA          |
+------------+-------------+--------------+----------------+
| anggota    | id          | int unsigned | auto_increment |
+------------+-------------+--------------+----------------+

Perubahan ke arah atas dilakukan dengan ALTER TABLE ... AUTO_INCREMENT, dan baris berikutnya memperoleh nomor di atas nilai baru tersebut.

bash

# menggeser counter ke atas, lalu menyisipkan baris baru
mysql -u root perpustakaan -e "ALTER TABLE anggota AUTO_INCREMENT = 100"

bash

# memeriksa nomor yang diberikan kepada baris berikutnya
mysql -t -u root perpustakaan <<'SQL'
INSERT INTO anggota (nama, email) VALUES ('Gita', '[email protected]');
SELECT id, nama, email FROM anggota ORDER BY id;
SQL

text Keluaran

+-----+-------+-------------------+
| id  | nama  | email             |
+-----+-------+-------------------+
|   1 | Ali   | [email protected]   |
|   2 | Budi  | [email protected]  |
|   3 | Citra | [email protected] |
|  10 | Dewi  | [email protected]  |
|  12 | Farah | [email protected] |
| 100 | Gita  | [email protected]  |
+-----+-------+-------------------+

Nilai yang ditetapkan lebih rendah daripada posisi counter saat ini diabaikan, sehingga penurunan nomor hanya mungkin dengan membangun ulang tabel. Sejak MySQL 8.0, counter juga bertahan ketika server dimatikan dan dinyalakan kembali, karena nilainya disimpan bersama skema, alih-alih dihitung ulang dari nilai maksimum kolom.

Kesalahan umum#

Kesalahan pertama adalah memperlakukan nilai AUTO_INCREMENT sebagai data bisnis, misalnya menjadikannya nomor keanggotaan yang dicetak pada kartu. Nilai ini tidak menjamin rapat, tidak dipakai ulang, dan berpindah saat server berpindah, sehingga nomor keanggotaan yang bermakna sebaiknya dibentuk dari kolom tersendiri.

Kesalahan kedua adalah mengurutkan data berdasarkan kolom id untuk keperluan bisnis. Urutan id mencerminkan urutan penyisipan teknis, bukan urutan makna, dan penyisipan eksplisit dapat melanggarnya kapan saja. Gunakan kolom tanggal atau nomor urut yang dikelola aplikasi bila urutan memang bermakna.

Kesalahan ketiga adalah mencoba menyetel counter ke bawah untuk menambal celah. Penetapan yang lebih rendah daripada posisi saat ini diabaikan tanpa pesan, dan penambalan celah tidak pernah diperlukan karena kolom id bukan data yang dibaca manusia.

Ringkasan#

AUTO_INCREMENT membentuk surrogate key pada kolom id, dan kolomnya wajib bertipe bilangan bulat serta menjadi kunci.
Counter selalu bergerak maju, penyisipan eksplisit memindahkannya ke atas, dan nomor yang sudah terpakai tidak pernah dipakai ulang.
Penyisipan gagal, penghapusan, dan ROLLBACK menimbulkan celah nomor yang merupakan perilaku normal, bukan cacat data.
Counter dapat dibaca melalui SHOW CREATE TABLE maupun information_schema.tables, dan digeser ke atas dengan ALTER TABLE ... AUTO_INCREMENT.

Referensi#

Using AUTO_INCREMENT — MySQL 8.0 Reference Manual — Panduan penggunaan AUTO_INCREMENT beserta aturan kolomnya.
InnoDB AUTO_INCREMENT Handling — MySQL 8.0 Reference Manual — Perilaku penomoran InnoDB, termasuk mode penguncian counter dan persistensinya.