Auto-increment Field Values

GaussDB(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, GaussDB(for MySQL) automatically enters the value of AUTO_INCREMENT to the field.
   1. Insert records into the table.

       INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');

   2. Querying table data

       select * from animals;
      +----+---------+
      | id | name    |
      +----+---------+
      |  1 | dog     |
      |  2 | cat     |
      |  3 | penguin |
      |  4 | lax     |
      |  5 | whale   |
      |  6 | ostrich |
      +----+---------+

   3. Query the table structure.

       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, GaussDB(for MySQL) automatically enters the value of AUTO_INCREMENT to the field.
   1. Insert records into the table.

      INSERT INTO animals (id,name) VALUES(0,'groundhog');
      INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

   2. Query data.

      select * from animals;
      +----+-----------+
      | id | name      |
      +----+-----------+
      |  1 | dog       |
      |  2 | cat       |
      |  3 | penguin   |
      |  4 | lax       |
      |  5 | whale     |
      |  6 | ostrich   |
      |  7 | groundhog |
      |  8 | squirrel  |
      +----+-----------+
      8 rows in set (0.00 sec) 

   3. Query the table structure.

      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, GaussDB(for MySQL) inserts X to the field and changes AUTO_INCREMENT to X + 1.
   1. Insert records into the table.

      INSERT INTO animals (id,name) VALUES(100,'rabbit');

   2. Query data.

      select * from animals;
      +-----+-----------+
      | id  | name      |
      +-----+-----------+
      |   1 | dog       |
      |   2 | cat       |
      |   3 | penguin   |
      |   4 | lax       |
      |   5 | whale     |
      |   6 | ostrich   |
      |   7 | groundhog |
      |   8 | squirrel  |
      | 100 | rabbit    |
      +-----+-----------+
      9 rows in set (0.00 sec) 

   3. Query the table structure.

      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, GaussDB(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 | dog       |
   |   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, GaussDB(for MySQL) enters the value to the field and AUTO_INCREMENT remains unchanged.
   1. Insert records into the table.

      INSERT INTO animals (id,name) VALUES(-50,'-middle');

   2. Queries data.

      select * from animals;
      +-----+-----------+
      | id  | name      |
      +-----+-----------+
      | -50 | -middle   |
      |   1 | dog       |
      |   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)

   3. Query the table structure.

      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         |
      +---------+-----------------------------------------------------------------------------------+