MySQL中的自增列
公众号：杨建荣的学习笔记 · 作者：r16笔记第13天 · 发布：2018-05-05 23:57:20 · 原文链接
MySQL的自增列情况比较特殊，之前分析了两篇。
而且在OOW的时候也着重提了一下自增列的历史遗留问题。
当然基于MySQL自增列的实现，确实是不够优雅，在新的版本还在持续引入新的特性。比如MGR里面，自增列的步长大了许多，默认是7了，这是在设计的时候考虑了MGR的节点数，提前做了预留，大多数情况下我们可以避免大量的预留值浪费。
当然，最近还有个网友问了我一个自增列的问题，描述的场景略微复杂些，我做了简化和抽象。
我们创建两个表t1,t2,在t2里面插入数据，然后使用insert into select的方式插入数据。
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> insert into t2 values(‘aa’),(‘bb’);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
把t2的数据插入t1
mysql> insert into t1(name) select *from t2;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
这个时候问题来了，数据id是1,2。
mysql> select * 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)
在此插入两条记录，使用insert into t1(name) select *from t2;
自增利的值这个时候是7，但是数据的情况如下：
mysql> select max(id) from t1;
+———+
| max(id) |
+———+
| 5 |
+———+
1 row in set (0.00 sec)
mysql> select *from t1;
+—-+——+
| id | name |
+—-+——+
| 1 | aa |
| 2 | bb |
| 4 | aa |
| 5 | bb |
+—-+——+
这种情况就比较麻烦了，类似的测试我又做了一些，可以很明显看到有些自增ID的缺位。
mysql> select *from t1;
+—-+——+
| id | name |
+—-+——+
| 1 | aa |
| 2 | bb |
| 4 | aa |
| 5 | bb |
| 7 | aa |
| 8 | bb |
+—-+——+
究其原因，和insert语句的定位也有关系，目前有几类insert语句。
1、simple insert 如insert into t(name) values(‘test’)
2、bulk insert 如load data | insert into … select …. from ….
3、mixed insert 如insert 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> insert into t(x) values(0),(3);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
可以看到自增列的值中间显然是有断层的。
mysql> select * from t;
+—+
| x |
+—+
| 1 |
| 3 |
+—+
如果我们把null值特意放进来，会自增一个相对精确的值。
mysql> insert into t(x) values(0),(null),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from t;
+—+
| x |
+—+
| 1 |
| 2 |
| 3 |
+—+
而一旦在程序侧修改自增列，其实带来的问题会更加严重，很可能出现1062的错误。
比如这个场景，表t有两行记录，值为1和3。我们修改一下自增列的值。
mysql> update t set x=4 where x=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以看到数据已经默默做了修改。
mysql> select * from t;
+—+
| x |
+—+
| 3 |
| 4 |
+—+
这个时候插入数据，就会有冲突了。
mysql> insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry ‘4’ for key ‘PRIMARY’
当然这类问题，细化起来，和实例是否重启也关系重大，对此阿里特意做了定制。
对于自增列的问题，大家在程序侧需要格外注意。