自增字段取值

GaussDB(for MySQL)对自增字段的赋值有以下几种方法：

# 表结构
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

1. 不对自增字段赋值，数据库会自动将自增值填入字段中，AUTO_INCREMENT自增。
   1. 插入数据。

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

   2. 查询表数据。

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

   3. 查询表结构。

       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. 对自增字段赋0或null值，数据库会自动将自增值填入字段中。AUTO_INCREMENT自增。
   1. 插入数据。

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

   2. 查询数据。

      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. 查询表结构。

      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. 直接使用大于AUTO_INCREMENT的值A，数据库会将A填入字段并修改AUTO_INCREMENT=A+1。
   1. 插入数据。

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

   2. 查询数据。

      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. 查询表结构。

      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. 使用小于AUTO_INCREMENT，但不冲突的值。数据可以插入，但AUTO_INCREMENT不变。

   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. 使用负值数据可以插入，但AUTO_INCREMENT不变。
   1. 插入数据。

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

   2. 查询数据。

      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. 查询表结构。

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