Help Center/ GaussDB(for MySQL)/ Troubleshooting/ Basic Issues/ Auto-increment Field Values
Updated on 2024-09-05 GMT+08:00
View PDF
Share

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

Parent topic: Basic Issues