Practical MySQL & MariaDB

Apa Itu AUTO_INCREMENT di MySQL? Cara Memeriksa, Menetapkan, dan Mengatur Ulang (Panduan Lengkap)

Manajemen Tabel
目次

Apa Itu AUTO_INCREMENT MySQL?

Dasar-dasar AUTO_INCREMENT

Di MySQL, AUTO_INCREMENT adalah fitur yang secara otomatis menambah nilai numerik dalam sebuah tabel basis data. Fitur ini biasanya digunakan sebagai kunci utama (PRIMARY KEY), memungkinkan nomor berurutan diberikan secara otomatis setiap kali baris baru dimasukkan—tanpa harus menentukan ID secara manual.

Penggunaan Utama AUTO_INCREMENT

  • Pembuatan ID anggota otomatis : Menetapkan ID unik saat pengguna mendaftar
  • Manajemen nomor pesanan : Secara otomatis memberikan nomor untuk setiap pesanan
  • Penetapan kode produk : Digunakan sebagai pengidentifikasi unik untuk produk

Dengan memanfaatkan AUTO_INCREMENT secara tepat, Anda dapat menjaga keunikan data sekaligus mengurangi beban administrasi.

Cara Memeriksa Nilai AUTO_INCREMENT di MySQL [3 SQL Commands]

Metode 1: Gunakan SHOW TABLE STATUS

SHOW TABLE STATUS LIKE 'table_name';

Saat Anda menjalankan perintah ini, nilai AUTO_INCREMENT saat ini ditampilkan pada kolom Auto_increment.

Metode 2: Ambil dari INFORMATION_SCHEMA.TABLES

SELECT AUTO_INCREMENT FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

Metode ini memungkinkan Anda mengambil langsung nilai AUTO_INCREMENT yang ditetapkan untuk tabel target.

Metode 3: Gunakan SHOW CREATE TABLE

SHOW CREATE TABLE table_name;

Saat Anda menjalankan perintah ini, DDL (Data Definition Language) pembuatan tabel ditampilkan, yang mencakup pengaturan AUTO_INCREMENT.

Cara Menetapkan dan Memodifikasi AUTO_INCREMENT

Menetapkan AUTO_INCREMENT Saat Membuat Tabel Baru

Untuk mendefinisikan kolom dengan AUTO_INCREMENT saat membuat tabel baru, gunakan SQL berikut:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Mengubah Nilai AUTO_INCREMENT pada Tabel yang Sudah Ada

Jika Anda ingin mengubah nilai AUTO_INCREMENT pada tabel yang sudah ada, gunakan perintah berikut:

ALTER TABLE users AUTO_INCREMENT = 1000;

Setelah menjalankan perintah ini, catatan berikutnya yang dimasukkan akan dimulai dengan ID 1000.

Mengubah Nilai Increment AUTO_INCREMENT (MySQL 8.0 atau Lebih Baru)

Secara default, nilai AUTO_INCREMENT bertambah 1. Namun, Anda dapat mengubah nilai increment bila diperlukan.

SET @@auto_increment_increment = 5;

Dengan pengaturan ini, nilai AUTO_INCREMENT bertambah 5 setiap kali (mis., 1, 6, 11, …).

Catatan Penting Saat Menggunakan AUTO_INCREMENT [Prevent Data Issues]

Batas Nilai Maksimum AUTO_INCREMENT

Nilai maksimum AUTO_INCREMENT tergantung pada tipe data kolom. Misalnya, saat menggunakan tipe INT, nilai maksimum adalah 2.147.483.647. Jika batas ini terlampaui, ID baru tidak dapat lagi dihasilkan, yang mengakibatkan error.

Solusi

  • Gunakan BIGINT untuk mendukung nilai yang jauh lebih besar
  • Pertimbangkan pengarsipan data atau reset bila diperlukan

Perilaku AUTO_INCREMENT Setelah Menghapus Data

Di MySQL, menghapus record tidak secara otomatis mereset nilai AUTO_INCREMENT.

DELETE FROM users WHERE id = 100;

Bahkan jika ID tertentu dihapus seperti ini, nilai yang dimasukkan berikutnya tetap melanjutkan dari penghitung saat ini tanpa penggunaan kembali.

Cara Reset
Menggunakan TRUNCATE TABLE menghapus semua data dan mereset nilai AUTO_INCREMENT.

TRUNCATE TABLE users;

Setelah melakukan operasi ini, ID yang dimasukkan selanjutnya akan dimulai kembali dari 1.

Transaksi dan AUTO_INCREMENT

Di MySQL, nilai AUTO_INCREMENT tidak kembali meskipun transaksi dibatalkan (ROLLBACK).

START TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;

Dalam kasus ini, data tidak dimasukkan, tetapi nilai AUTO_INCREMENT tetap bertambah. Akibatnya, dapat terjadi celah pada nomor ID.

Pertanyaan yang Sering Diajukan (FAQ)

Q1: Mengapa nilai AUTO_INCREMENT kadang memiliki celah?

A: Karena angka tidak direset setelah INSERT diikuti oleh ROLLBACK. Penghapusan dan insert yang gagal juga dapat menyebabkan celah.

Q2: Bagaimana cara mereset nilai AUTO_INCREMENT?

A: Jalankan TRUNCATE TABLE table_name; untuk meresetnya.

Q3: Bagaimana cara memeriksa nilai AUTO_INCREMENT saat ini?

A: Gunakan SHOW TABLE STATUS atau query INFORMATION_SCHEMA.TABLES.

Q4: Apa yang terjadi jika AUTO_INCREMENT melebihi nilai maksimumnya?

A: Jika melebihi nilai maksimum INT, akan terjadi error. Anda harus mengubah tipe kolom menjadi BIGINT.