Updated on 2023-10-19 GMT+08:00
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) );
- If no value is specified for the auto-increment field, GaussDB(for MySQL) automatically enters the value of AUTO_INCREMENT to the field.
- Insert records into the table.
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
- Querying table data
select * from animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
- 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 | +---------+--------------------------------------------------+
- Insert records into the table.
- If 0 or NULL is specified for the auto-increment field, GaussDB(for MySQL) automatically enters the value of AUTO_INCREMENT to the field.
- Insert records into the table.
INSERT INTO animals (id,name) VALUES(0,'groundhog'); INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
- 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)
- 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 | +---------+------------------------------------------------------------+
- Insert records into the table.
- 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.
- Insert records into the table.
INSERT INTO animals (id,name) VALUES(100,'rabbit');
- 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)
- 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 | +---------+----------------------------------------------------------------------+
- Insert records into the table.
- 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 | +---------+------------------------------------------------------------------+
- 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.
- Insert records into the table.
INSERT INTO animals (id,name) VALUES(-50,'-middle');
- 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)
- 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 | +---------+-----------------------------------------------------------------------------------+
- Insert records into the table.
Parent topic: Basic Issues
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
The system is busy. Please try again later.