[HowTo]-MySQL序列AUTO_INCREMENT

基础知识:

• 通用AUTO_INCREMENT属性

1. 每个表只能有一列具有AUTO_INCREMENT属性
2. 必须给该列添加索引(一般是主键,唯一索引或者普通索引也可以)
3. 必须给该列添加NOT NULL限制条件.(MySQL会自动设置)

• LAST_INSERT_ID()函数

获取最近生成的序号值.如果在当前连接还没有生成过AUTO_INCREMENT值,该函数将返回0.
该函数只返回本次连接服务器以后租金一次生成的AUTO_INCREMENT值,不受其他连接的影响.一次插入多个数据行的INSERT语句,将生成多个AUTO_INCREMENT值,但是LAST_INSERT_ID()函数只返回第一个.

• InnoDB引擎下的特性

1. 允许在CREATE TABLE和ALTER TABLE语句中使用AUTO_INCREMENT关键字设置序号值.(5.0.3版本之后)
2. 使用TRUNCATE语句后,序列被重置.
3. InnoDB在内存里维护序列计数器,并未存储在数据表中.如果从序列的顶端删除一些值,然后重启数据库,那么删除过的序列值将被重复使用.(同时也会取消CREATE/ALTER TABLE语句中对AUTO_INCREMENT值的设置)
4. 生成AUTO_INCREMENT值的事务操作被回滚,序列中会出现"断裂带"

测试:
参考InnoDB引擎中的第3点,初步以为会导致主从的复制问题,做了下面简单的测试,得到的结果:
正常操作情况下,不会导致复制问题

3306-主库
3307-从库

在主库上面创建表t01,建表语句如下
CREATE TABLE `t01` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入两条记录
insert into t01 (c) values(1),(2);

删除第2条
delete from t01 where c = 2;

查看建表语句中的AUTO_INCREMENT值
AUTO_INCREMENT=3

重启MySQL
查看建表语句中的AUTO_INCREMENT值
AUTO_INCREMENT=2

查看从库的AUTO_INCREMENT值
AUTO_INCREMENT=3

再主库插入记录
insert into t01 (c) values(1),(2);

查看从库状态:无异常.
binlog日志中的SET INSERT_ID语句,可以设置序列值.在正常的操作流程下,不会有异常产生.


注意
如果3306在重启的过程中,3307中插入数据,待3306重启之后,再执行插入数据的语句,会导致数据冲突.
Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t01 (c) values(1),(2)'