Help Center/ Relational Database Service/ Troubleshooting/ RDS for MySQL/ Other Issues/ Changing the AUTO_INCREMENT Value of a Table
Updated on 2023-11-03 GMT+08:00

Changing the AUTO_INCREMENT Value of a Table

The methods are as follows:

  • If the value of AUTO_INCREMENT is greater than the maximum value of the auto-increment column in the table, AUTO_INCREMENT can be changed to a larger value within the value range.
    mysql> show create table animals; 
    +---------+------------------------------------------------------------------+ 
    | Table   | Create Table                                                     | 
    +---------+------------------------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8   | 
    +---------+------------------------------------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> select * from animals; 
    +-----+-----------+ 
    | id  | name      | 
    +-----+-----------+ 
    | -50 | -middle   | 
    |   1 | fish       | 
    |   2 | cat       | 
    |  50 | middle    | 
    | 100 | rabbit    | 
    +-----+-----------+ 
    11 rows in set (0.00 sec)  
    mysql> alter table animals AUTO_INCREMENT=200; 
    Query OK, 0 rows affected (0.22 sec) 
    Records: 0  Duplicates: 0  Warnings: 0  
    mysql> show create table animals; 
    +---------+-------------------------------------------------------+ 
    | Table   | Create Table                                          | 
    +---------+-------------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8 | 
    +---------+-------------------------------------------------------+
  • If the new value of AUTO_INCREMENT is still greater than the maximum value of the auto-increment column in the table, the change was successful. Otherwise, the value is changed to the maximum value of the auto-increment column plus 1 by default.
    mysql> select * from animals; 
    +-----+-----------+ 
    | id  | name      | 
    +-----+-----------+ 
    | -50 | -middle   | 
    |   1 | fish       | 
    |   2 | cat       | 
    |  50 | middle    | 
    | 100 | rabbit    | 
    +-----+-----------+ 
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                        | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8 | 
    +---------+-----------------------------------------------------+ 
    mysql> alter table animals AUTO_INCREMENT=150; 
    Query OK, 0 rows affected (0.05 sec) 
    Records: 0  Duplicates: 0  Warnings: 0 
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                         | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 | 
    +---------+-----------------------------------------------------+ 
    mysql> alter table animals AUTO_INCREMENT=50; 
    Query OK, 0 rows affected (0.04 sec) 
    Records: 0  Duplicates: 0  Warnings: 0 
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                        | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 | 
    +---------+-----------------------------------------------------+ 
    mysql> delete from animals where id=100; 
    Query OK, 1 row affected (0.00 sec)  
    mysql> select * from animals; 
    +-----+-----------+ 
    | id  | name      | 
    +-----+-----------+ 
    | -50 | -middle   | 
    |   1 | fish       | 
    |   2 | cat       | 
    |  50 | middle    | 
    +-----+-----------+ 
    10 rows in set (0.00 sec)  
    mysql> alter table animals AUTO_INCREMENT=50; 
    Query OK, 0 rows affected (0.04 sec) 
    Records: 0  Duplicates: 0  Warnings: 0  
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                        | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 | 
    +---------+-----------------------------------------------------+ 
    1 row in set (0.00 sec)
  • The value of AUTO_INCREMENT cannot be changed to a negative number.
    mysql> alter table animals AUTO_INCREMENT=-1; 
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1