表的自增auto-j9九游会登录

在数据表中会发现auto_increment的值不等于表中字段最大值 1，可能原因有以下几种：

• 如果步长不为1，则auto_increment=最大值 步长。关于步长不为1的参数说明，请参见14.1.62 表的自增auto_increment初值与步长。

  mysql> show variables like 'auto_inc%'; 
   -------------------------- -------  
  | variable_name            | value | 
   -------------------------- -------  
  | auto_increment_increment | 2     | 
  | auto_increment_offset    | 1     | 
   -------------------------- -------  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  2 | 
  |  4 | 
  |  6 | 
  |  8 | 
   ----  
  mysql> show create table auto_test1; 
   ------------ -----------------------------------------  
  | table      | create table                            | 
   ------------ -----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=10 default charset=utf8 | 
   ------------ ----------------------------------------- 

• 直接修改表的auto_increment，会导致auto_increment变化。

  mysql> select * from animals; 
   ---- -----------  
  | id | name      | 
   ---- -----------  
  |  1 | fish       | 
  |  2 | cat       | 
  |  3 | penguin   | 
   ---- -----------  
  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=4 default charset=utf8           | 
   --------- -----------------------------------------------------  
  mysql> alter table animals auto_increment=100; 
  query ok, 0 rows affected (0.04 sec) 
  records: 0  duplicates: 0  warnings: 0 
  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=100 default charset=utf8         | 
   --------- ----------------------------------------------------- 

• 未提交的事务或回滚的事务，会导致auto_increment增长，但回滚后不会下降。

  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=4 default charset=utf8 | 
   ------------ ----------------------------------------  
  1 row in set (0.00 sec)  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
   ----  
  mysql> begin; 
  query ok, 0 rows affected (0.02 sec)  
  mysql> insert into auto_test1 values (0),(0),(0); 
  query ok, 3 rows affected (0.00 sec) 
  records: 3  duplicates: 0  warnings: 0  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
  |  4 | 
  |  5 | 
  |  6 | 
   ----  
  6 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=7 default charset=utf8 | 
   ------------ ----------------------------------------  
  1 row in set (0.00 sec)  
  mysql> rollback; 
  query ok, 0 rows affected (0.05 sec)  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
   ----  
  3 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=7 default charset=utf8 | 
   ------------ ---------------------------------------- 

• 数据插入后，auto_increment变化，然后删除对应的数据行，auto_increment不会下降。

  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=4 default charset=utf8 | 
   ------------ ----------------------------------------  
  1 row in set (0.00 sec)  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
   ----  
  mysql> insert into auto_test1 values (0),(0),(0); 
  query ok, 3 rows affected (0.00 sec) 
  records: 3  duplicates: 0  warnings: 0  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
  |  4 | 
  |  5 | 
  |  6 | 
   ----  
  6 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=7 default charset=utf8 | 
   ------------ ----------------------------------------  
  1 row in set (0.00 sec)  
  mysql> delete from auto_test1 where id>3;  
  mysql> select * from auto_test1; 
   ----  
  | id | 
   ----  
  |  1 | 
  |  2 | 
  |  3 | 
   ----  
  3 rows in set (0.00 sec)  mysql> show create table auto_test1; 
   ------------ ----------------------------------------  
  | table      | create table                           | 
   ------------ ----------------------------------------  
  | auto_test1 | create table `auto_test1` (   
  `id` int not null auto_increment,   
  primary key (`id`) 
  ) engine=innodb auto_increment=7 default charset=utf8 | 
   ------------ ----------------------------------------