[adsense id=”0514458240″ width=”468″ height=”60″]
There are occasions when it is necessary to reset the auto_increment value of a table in MySQL. For example, you are adding content but want to identify a new application process by changing from the current value of let’s say 1538 and start the next value on 10000. That way you can easily identify entries which were inserted with the new process.
Firstly, some background into how auto_increments works. It is possible to assign a value to an auto_increment field or have a value automatically assigned by leaving the auto_increment field as a null.
mysql> insert into persons (dob, name) values (str_to_date('01/01/2015 00:00', '%d/%m/%Y %T'), 'Tom'),
-> (str_to_date('01/01/2015 01:00', '%d/%m/%Y %T'), 'Tim'),
-> (str_to_date('02/01/2015 15:23', '%d/%m/%Y %T'), 'Ron'),
-> (str_to_date('07/01/2015 10:19', '%d/%m/%Y %T'), 'Jerry'),
-> (str_to_date('07/01/2015 21:59', '%d/%m/%Y %T'), 'Abe'),
-> (str_to_date('11/01/2015 16:00', '%d/%m/%Y %T'), 'Jake'),
-> (str_to_date('12/01/2015 13:32', '%d/%m/%Y %T'), 'John'),
-> (str_to_date('13/01/2015 21:37', '%d/%m/%Y %T'), 'Jack');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into persons (id, dob, name) values (null, str_to_date('13/02/2015 13:09', '%d/%m/%Y %T'), 'Jane');
Query OK, 1 row affected (0.01 sec)
mysql> select * from persons order by id;
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
+----+---------------------+-------+
9 rows in set (0.00 sec)
mysql> insert into persons (id, dob, name) values (15, str_to_date('11/02/2015 01:17', '%d/%m/%Y %T'), 'Jade');
Query OK, 1 row affected (0.01 sec)
mysql> select * from persons order by id;
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
| 15 | 2015-02-11 01:17:00 | Jade |
+----+---------------------+-------+
10 rows in set (0.00 sec)
Adding another entry at this point will scan the largest value in the id column and add one. So the next value will not be 10 but will be 16.
mysql> insert into persons (dob, name) values (str_to_date('15/02/2015 03:29', '%d/%m/%Y %T'), 'Jasmin');
Query OK, 1 row affected (0.01 sec)
mysql> select * from persons order by id;
+----+---------------------+--------+
| id | dob | name |
+----+---------------------+--------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
| 15 | 2015-02-11 01:17:00 | Jade |
| 16 | 2015-02-15 03:29:00 | Jasmin |
+----+---------------------+--------+
11 rows in set (0.00 sec)
Truncating a table will automatically reset the auto_increment value back to 1 on a table. However, this implies removing all of the data.
Now let us have a look at what happens if we reach the maximum value for the auto_increment column and we attempt to add another row. An INT has a max SIGNED value of 2147483647. So we will add in a record with this value and then attempt to add a new value with auto_increment automatically assigning the next largest value i.e. over the limit of the column.
mysql> insert into persons (id, dob, name) values (2147483647, str_to_date('17/02/2015 15:13', '%d/%m/%Y %T'), 'Tod');
Query OK, 1 row affected (0.01 sec)
mysql> select * from persons;
+------------+---------------------+--------+
| id | dob | name |
+------------+---------------------+--------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
| 15 | 2015-02-11 01:17:00 | Jade |
| 16 | 2015-02-15 03:29:00 | Jasmin |
| 2147483647 | 2015-02-17 15:13:00 | Tod |
+------------+---------------------+--------+
12 rows in set (0.00 sec)
mysql> insert into persons (dob, name) values (str_to_date('17/02/2015 19:32', '%d/%m/%Y %T'), 'Buddy');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
In this case we have reached the maximum value we now need to change the auto_increment value. However, we need to remove the highest value auto_increment value on the table otherwise it will no longer work as the new value will always be the highest auto_increment value plus 1. In the example below we will set the auto_increment value to 30 and start adding entries using this value.
mysql> delete from persons where id = 2147483647;
Query OK, 1 row affected (0.00 sec)
mysql> select * from persons;
+----+---------------------+--------+
| id | dob | name |
+----+---------------------+--------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
| 15 | 2015-02-11 01:17:00 | Jade |
| 16 | 2015-02-15 03:29:00 | Jasmin |
+----+---------------------+--------+
11 rows in set (0.00 sec)
mysql> alter table persons auto_increment = 30;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into persons (dob, name) values (str_to_date('17/02/2015 19:32', '%d/%m/%Y %T'), 'Buddy');
Query OK, 1 row affected (0.01 sec)
mysql> select * from persons;
+----+---------------------+--------+
| id | dob | name |
+----+---------------------+--------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
| 8 | 2015-01-13 21:37:00 | Jack |
| 9 | 2015-02-13 13:09:00 | Jane |
| 15 | 2015-02-11 01:17:00 | Jade |
| 16 | 2015-02-15 03:29:00 | Jasmin |
| 30 | 2015-02-17 19:32:00 | Buddy |
+----+---------------------+--------+
12 rows in set (0.00 sec)