MySQL自增列

MySQL的自增列情况比较特殊，之前分析了两篇。

MySQL自增列主从不一致的测试(r12笔记第37天)

MySQL自增列的重复值问题(r12笔记第25天)

而且在OOW的时候也着重提了一下自增列的历史遗留问题。

十年前的老问题，MySQL 8.0有了答案

当然基于MySQL自增列的实现，确实是不够优雅，在新的版本还在持续引入新的特性。比如MGR里面，自增列的步长大了许多，默认是7了，这是在设计的时候考虑了MGR的节点数，提前做了预留，大多数情况下我们可以避免大量的预留值浪费。

当然，最近还有个网友问了我一个自增列的问题，描述的场景略微复杂些，我做了简化和抽象。

我们创建两个表t1,t2,在t2里面插入数据，然后使用ｉｎｓｅｒｔ into ｓｅｌｅｃｔ的方式插入数据。

mysql> create table t1(id int auto_increment primary key,name varchar(255));

Query OK, 0 rows affected (0.12 sec)

mysql> create table t2(name varchar(255))engine=innodb;

Query OK, 0 rows affected (0.10 sec)

插入两条记录

mysql> ｉｎｓｅｒｔ into t2 values(‘aa‘),(‘bb‘);

Query OK, 2 rows affected (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 0

把t2的数据插入t1

mysql> ｉｎｓｅｒｔ into t1(name) ｓｅｌｅｃｔ *from t2;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

这个时候问题来了，数据id是1,2。

mysql> ｓｅｌｅｃｔ * from t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

+----+------+

2 rows in set (0.00 sec)

但是自增列的值直接跳到了4。

mysql> show create table t1;

| Table | Create Table

| t1 | CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

在此插入两条记录，使用ｉｎｓｅｒｔ into t1(name) ｓｅｌｅｃｔ *from t2;

自增利的值这个时候是7，但是数据的情况如下：

mysql> ｓｅｌｅｃｔ max(id) from t1;

+---------+

| max(id) |

+---------+

| 5 |

+---------+

1 row in set (0.00 sec)

mysql> ｓｅｌｅｃｔ *from t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 4 | aa |

| 5 | bb |

+----+------+

这种情况就比较麻烦了，类似的测试我又做了一些，可以很明显看到有些自增ID的缺位。

mysql> ｓｅｌｅｃｔ *from t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 4 | aa |

| 5 | bb |

| 7 | aa |

| 8 | bb |

+----+------+

究其原因，和ｉｎｓｅｒｔ语句的定位也有关系，目前有几类ｉｎｓｅｒｔ语句。

1、simple ｉｎｓｅｒｔ 如ｉｎｓｅｒｔ into t(name) values(‘test‘)

2、bulk ｉｎｓｅｒｔ 如load data | ｉｎｓｅｒｔ into ... ｓｅｌｅｃｔ .... from ....

3、mixed ｉｎｓｅｒｔ 如ｉｎｓｅｒｔ into t(id,name) values(1,‘a‘),(null,‘b‘),(5,‘c‘);

这个和参数innodb_autoinc_lock_mode有很大的关系，默认参数值为1

innodb_auto_lockmode有三个取值

　　　　1）、0 这个表示tradition 传统

　　　　2）、1 这个表示consecutive 连续

　　　　3）、2 这个表示interleaved 交错

这个参数不能在线修改，需要重启实例生效。第一种是表级的auto_inc锁，对于并发插入来说是有影响的。

默认是第二种，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要

语句得到了相应的值后就可以提前释放锁

第三种是相对来说性能最好，但是数据的细节无法保证，很可能出现不一致的情况。

对于null值的处理也蛮特别的。我们补充一些，创建表t

mysql> create table t(x int auto_increment not null primary key);

Query OK, 0 rows affected (0.04 sec)

mysql> ｉｎｓｅｒｔ into t(x) values(0),(3);

Query OK, 2 rows affected (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 0

可以看到自增列的值中间显然是有断层的。

mysql> ｓｅｌｅｃｔ * from t;

+---+

| x |

+---+

| 1 |

| 3 |

+---+

如果我们把null值特意放进来，会自增一个相对精确的值。

mysql> ｉｎｓｅｒｔ into t(x) values(0),(null),(3);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> ｓｅｌｅｃｔ *from t;

+---+

| x |

+---+

| 1 |

| 2 |

| 3 |

+---+

而一旦在程序侧修改自增列，其实带来的问题会更加严重，很可能出现1062的错误。

比如这个场景，表t有两行记录，值为1和3。我们修改一下自增列的值。

mysql> ｕｐｄａｔｅ t set x=4 where x=1;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

可以看到数据已经默默做了修改。

mysql> ｓｅｌｅｃｔ * from t;

+---+

| x |

+---+

| 3 |

| 4 |

+---+

这个时候插入数据，就会有冲突了。

mysql> ｉｎｓｅｒｔ into t(x) values(0);

ERROR 1062 (23000): Duplicate entry ‘4‘ for key ‘PRIMARY‘

当然这类问题，细化起来，和实例是否重启也关系重大，对此阿里特意做了定制。

对于自增列的问题，大家在程序侧需要格外注意。