自增字段取值

RDS for MySQL对自增字段赋值有以下几种方法：

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

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

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

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