Updated on 2023-11-03 GMT+08:00

Auto-increment Field Values

RDS for MySQL uses the following methods to assign values to an auto-increment field:

# Table structure
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);
  1. If no value is specified for the auto-increment field, RDS for MySQL automatically enters the value of AUTO_INCREMENT to the field.
    mysql> INSERT INTO animals (name) VALUES ('fish'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0 
    mysql> select * from animals;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | fish     |
    |  2 | cat     |
    |  3 | penguin |
    |  4 | lax     |
    |  5 | whale   |
    |  6 | ostrich |
    +----+---------+
    6 rows in set (0.00 sec) 
    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=7 DEFAULT CHARSET=utf8 |
    +---------+--------------------------------------------------+
  2. If 0 or NULL is specified for the auto-increment field, RDS for MySQL automatically enters the value of AUTO_INCREMENT to the field.
    mysql> INSERT INTO animals (id,name) VALUES(0,'groundhog');
    Query OK, 1 row affected (0.00 sec) 
    mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
    Query OK, 1 row affected (0.01 sec) 
    mysql> select * from animals;
    +----+-----------+
    | id | name      |
    +----+-----------+
    |  1 | fish       |
    |  2 | cat       |
    |  3 | penguin   |
    |  4 | lax       |
    |  5 | whale     |
    |  6 | ostrich   |
    |  7 | groundhog |
    |  8 | squirrel  |
    +----+-----------+8 
    rows in set (0.00 sec) 
    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=9 DEFAULT CHARSET=utf8 |
    +---------+------------------------------------------------------------+
  3. If the value X that is greater than the value of AUTO_INCREMENT is specified for the auto-increment field, RDS for MySQL inserts X to the field and changes AUTO_INCREMENT to X + 1.
    mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
    Query OK, 1 row affected (0.00 sec) 
    mysql> select * from animals;
    +-----+-----------+
    | id  | name      |
    +-----+-----------+
    |   1 | fish       |
    |   2 | cat       |
    |   3 | penguin   |
    |   4 | lax       |
    |   5 | whale     |
    |   6 | ostrich   |
    |   7 | groundhog |
    |   8 | squirrel  |
    | 100 | rabbit    |
    +-----+-----------+
    9 rows in set (0.00 sec) 
    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 |
    +---------+----------------------------------------------------------------------+
  4. If a value less than the value of AUTO_INCREMENT is specified for the auto-increment field, RDS for MySQL enters the value to the field and AUTO_INCREMENT remains unchanged.
    mysql> INSERT INTO animals (id,name) VALUES(50,'middle');
    Query OK, 1 row affected (0.00 sec) 
    mysql> select * from animals;
    +-----+-----------+
    | id  | name      |
    +-----+-----------+
    |   1 | fish       |
    |   2 | cat       |
    |   3 | penguin   |
    |   4 | lax       |
    |   5 | whale     |
    |   6 | ostrich   |
    |   7 | groundhog |
    |   8 | squirrel  |
    |  50 | middle    |
    | 100 | rabbit    |
    +-----+-----------+
    10 rows in set (0.00 sec) 
    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 |
    +---------+------------------------------------------------------------------+
  5. If a negative value is specified for the auto-increment field, RDS for MySQL enters the value to the field and AUTO_INCREMENT remains unchanged.
    mysql> INSERT INTO animals (id,name) VALUES(-50,'-middle');
    Query OK, 1 row affected (0.00 sec) 
    mysql> select * from animals;
    +-----+-----------+
    | id  | name      |
    +-----+-----------+
    | -50 | -middle   |
    |   1 | fish       |
    |   2 | cat       |
    |   3 | penguin   |
    |   4 | lax       |
    |   5 | whale     |
    |   6 | ostrich   |
    |   7 | groundhog |
    |   8 | squirrel  |
    |  50 | middle    |
    | 100 | rabbit    |
    +-----+-----------+
    11 rows in set (0.00 sec) 
    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         |
    +---------+--------------------------------------------------------------------------------------------------+