ITPub博客

MySQL中的自增列

原创 MySQL 作者:jeanron100 时间:2018-05-07 09:44:25 0 删除 编辑

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

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

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

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

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

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

MySQL中的自增列

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

我们创建两个表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'

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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-2153890/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-05-14

  • 博文量
    1667
  • 访问量
    14196515