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.