How to handle fragmentation of auto increment ID column in MySQL?

Whenever we renumber, there might be a problem. There is a need to declare a unique ID for a column.

In MySQL version 5.6 InnoDB, we can reuse the auto_increment ID by including the ID column in an INSERT statement and we can give any specific value that we want.

The situations are as follows −

• Whenever we delete the ID with the highest number
• Whenever we start and stop MySQL server
• Whenever we insert a new record

Example of ID auto increment using auto_increment variable.

 mysql> create table UniqueAutoId -> ( -> id int auto_increment, -> Unique(id) -> ); Query OK, 0 rows affected (0.45 sec) 

Inserting records into table.

 mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.13 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.16 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.07 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.10 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.10 sec) 

Displaying all records.

 mysql> select *from UniqueAutoId; 

The following is the output.

 +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec) 

To delete records, we have used DELETE statement. Here, we are deleting id=5;

 mysql> DELETE from UniqueAutoId where id=5; Query OK, 1 row affected (0.14 sec) 

Displaying all records.

 mysql> select *from UniqueAutoId; 

The following is the output.

 +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) 

Let us again delete a record from the table.

 mysql> delete from UniqueAutoId where id=2; Query OK, 1 row affected (0.15 sec) 

Again, displaying records from the table.

 mysql> select *from UniqueAutoId; 

The following is the output.

 +----+ | id | +----+ | 1 | | 3 | | 4 | +----+ 3 rows in set (0.00 sec 

The above results in fragmentation.