MySQL reset auto increment value

Consider the following table schema:

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

The table already have 5 rows, therefore the next auto increment value is 6.

How to reset the auto increment value?

We can change the auto increment value with the following query.

mysql> ALTER TABLE test AUTO_INCREMENT=9;

Now, if we run the 'show create table' query we can see that the auto increment value is equal to 9.

mysql> SHOW CREATE TABLE test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

Remember that, you cannot set the auto increment to a value that already exists or a number that needs to fill a gap between numbers. For example:

mysql> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
+----+
3 rows in set (0.00 sec)

The auto increment value is equal to 6. If we try to set the 'auto_increment' value to be equal to 3 (to fill the gap), automatically it will be set to 6.

- Posted by Eva to MySql