Sebuah kolom di database yang memiliki atribut auto increment pada umumnya digunakan sebagai Index yang sifatnya unik. Dalam artian kolom tersebut digunakan sebagai ‘ID’ atau kunci, untuk membedakan baris yang satu dengan baris yang lainnya.

Misalnya kita bermain pada sebuah tabel animal sederhana berikut:

CREATE TABLE `animal` (
  `id` INT( 6 ) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR( 255 ) NOT NULL ,
  `color` VARCHAR( 255 ) NOT NULL ,
  PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Terdapat 3 buah kolom id, name, dan color dengan kolom id sebagai primary key-nya dan auto increment. Secara default, nilai kolom id auto increment untuk baris pertama yang dimasukkan ke dalam tabel animal adalah 1. Untuk baris kedua, ketiga, dan selanjutnya, nilai kolom nya adalah 2, 3, dan seterusnya kecuali ditentukan lain (secara manual).

Contohnya adalah sebagai berikut:

INSERT INTO animal (name, color) VALUES ('horse', 'white');
INSERT INTO animal (name, color) VALUES ('horse', 'black');
INSERT INTO animal (id, name, color) VALUES ('5', 'cat', 'white');
INSERT INTO animal (name, color) VALUES ('dog', 'brown');

akan menghasilkan data seperti ini di database:

mysql> SELECT * FROM animal;
+----+-------+-------+
| id | name  | color |
+----+-------+-------+
|  1 | horse | white |
|  2 | horse | black |
|  5 | cat   | white |
|  6 | dog   | brown |
+----+-------+-------+
4 rows in set (0.00 sec)

Terlihat bahwa jika data dimasukkan ke dalam tabel tanpa memberikan nilai pada kolom id (yang auto increment), secara otomatis nilai auto increment yang disimpan adalah nilai id yang terakhir disimpan + 1 dan bukan nilai id baris terakhir + 1.

Maksudnya? Kok bisa begitu?
Lanjut maaang…

Misalnya kita lanjutkan lagi query di tabel animal dengan query:

mysql> DELETE FROM animal WHERE id = 6;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM animal;
+----+-------+-------+
| id | name  | color |
+----+-------+-------+
|  1 | horse | white |
|  2 | horse | black |
|  5 | cat   | white |
+----+-------+-------+
3 rows in set (0.00 sec)

Kemudian memasukkan baris baru lagi dengan query:

mysql> INSERT INTO animal (name, color) VALUES ('bird', 'green');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM animal;
+----+-------+-------+
| id | name  | color |
+----+-------+-------+
|  1 | horse | white |
|  2 | horse | black |
|  5 | cat   | white |
|  7 | bird  | green |
+----+-------+-------+
4 rows in set (0.00 sec)

Terlihat bahwa nilai auto increment pada saat memasukkan data: bird, green, nilai auto increment-nya adalah 7 yang didapat dari nilai id baris dog, brown, yaitu 6 + 1 = 7, dan bukan dari nilai id baris sebelumnya (cat, white), yaitu 5 + 1 = 6.

Bagaimana jika kita ingin nilai id-nya adalah nilai dari id dari baris sebelumnya + 1?

Caranya, sebelum melakukan query INSERT untuk memasukkan baris data baru, terlebih dahulu, eksekusi query:

ALTER TABLE animal AUTO_INCREMENT = 1;

query tersebut akan membuat baris selanjutnya akan memiliki nilai auto increment (bukan membuat nilai kolom auto increment pada baris berikutnya menjadi 1), nilai id maksimum yang ada di tabel + 1. Contohnya, jika kita lanjutkan query pada tabel animal tadi:

mysql> DELETE FROM animal WHERE id = 7;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE animal AUTO_INCREMENT = 1;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM animal;
+----+-------+-------+
| id | name  | color |
+----+-------+-------+
|  1 | horse | white |
|  2 | horse | black |
|  5 | cat   | white |
+----+-------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO animal (name, color) VALUES ('snake', 'yellow');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM animal;
+----+-------+--------+
| id | name  | color  |
+----+-------+--------+
|  1 | horse | white  |
|  2 | horse | black  |
|  5 | cat   | white  |
|  6 | snake | yellow |
+----+-------+--------+
4 rows in set (0.00 sec)

Terbukti bahwa ketika memasukkan baris baru data: snake, yellow, nilai id yang di dapat adalah 6 dari 5 + 1, dan bukan 8 (dari nilai id tertinggi pada baris yang pernah ada di database + 1, yaitu 7+1 = 8).

Well, dengan query singkat

ALTER TABLE animal AUTO_INCREMENT = 1;

Kita bisa ’sedikit’ menghemat nilai id yang tidak terpakai yang ada di dalam tabel. Karena besarnya nilai id itu pun ada batasannya. Semoga bermanfaat!

Sebagian dari artikel ini dibuat dari forum diskusi phpBuilder yang ada di halaman:

http://www.phpbuilder.com/board/archive/index.php/t-619716.html