MySQL的auto_increment使用

说明

总结自《mysql技术内幕（第5版）》

创建auto_increment列要遵循如下规则

1. 每个表只能有一个列具有auto_increment属性，且必须为整数数据类型
   （当然，也支持浮点类型，但强烈不建议）
2. 该列必须建立索引，最常见的就是使用primary key或unique索引，当然，也支持不唯一索引
3. 该列必须是not null的

auto_increment列将有如下特性

1. 用insert插入auto_increment列
   1. 该值已存在，且该列是unique，发生键重复错误
   2. 插入成功，若该值大于auto_increment，则重置auto_increment
   3. 若该值为null，该值被置为当前序号，并引发MySQL自动生成下一个序号
   4. 若该值为0，且未启用no_auto_value_on_zero模式，等效于该值为null的情况
   5. 若该值为0，且已启用no_auto_value_on_zero模式，插入成功
2. 用update更新auto_increment列的值
   1. 若新值已使用且该列是unique的，则发生键重复错误
   2. 若新值大于已有值，重置auto_increment
   3. 若新值是0，且未存在，插入成功（即使未启用no_auto_value_on_zero模式）
3. 用replace更新行
   1. 若根据auto_increment列的值来更新，则auto_increment列值不变
   2. 若根据其他具有唯一性的列的值来更新，且序号列值为null或0且未启用no_auto_value_on_zero时，该列的值被更新为新序号
4. auto_increment开始序号默认值为1，每次递增量默认值为1

获取当前会话最近生成的auto_increment值

使用last_insert_id()方法

1. 返回最近生成的auto_increment值，若从未生成过，返回0
2. 一次插入多行的insert语句，只返回语句中第一次生成的auto_increment值
3. 如果使用insert delayed，那么直到实际插入行时，才会生成auto_increment值，这时该方法无用

不同存储引擎对auto_increment列的支持

1. InnoDB

1. 在创建表时，可以使用auto_increment = n表选项来设置初始序列值
   并且在表创建之后，还可以使用alter table tb_name auto_increment = m来进行修改
2. 如果生成auto_increment值的事务被回滚，那么auto_increment将不会回滚，即出现序列断层
3. 从序列顶端（序列值趋向大的为顶端）删除的值通常不能再重用。不过如下情况可以：
   1. 使用truncate table tb_name清空表，表数据将被清，且auto_increment将重置为1
   2. 在首次生成auto_increment值时，InnoDB会使用列的当前最大值+1（若表为空，则值为1）
   3. InnoDB将在内存中维护auto_increment值，即auto_increment值不在表空间里，当删除序列顶端某些值时，再重启服务器，则之前被删除的序列顶端的值将被重用。
4. 重启服务器，在create table或alter table语句里使用的auto_increment选项将失效
5. 表里不能使用符合索引生成多个独立的序列

2. MyISAM

1. 序列一般是单调的，且被删除后不会被重用，以下情况除外：
   1. 使用truncate table tb_name清空表数据，auto_increment=1
   2. 表里使用复合索引生成序列，则从序列顶端被删除的值将被重用
2. 序列默认从1开始，可以在create table语句里，使用auto_increment = n来指定
3. 可以使用alter table auto_increment = m来修改

重置已有列的序列编号

使用一个alter table语句，删除auto_increment列，再加回来，且设置auto_increment=1

//已有表
create table tb_name(
`name` char(10),
id int unsigned not null auto_increment primary key
);

//重置序列号，此列若被别处引用可能失败或破坏两表间的关系
alter table tb_name
drop primary key,
drop id,
add id int unsigned not null aut