自增字段取值-j9九游会登录

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