• 博客访问: 13963984
  • 博文数量: 1488
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-14 23:24
  • 认证徽章:
个人简介

每日发文,或技术、或总结,偶有日间小事也以为记,谓之学习笔记,成年累月1500多天,中间几乎没有间断,要旨只有一个:学习交流,共同进步 。 学习笔记精华整理,个人新书《Oracle DBA工作笔记》已开售,在京东,当当,亚马逊,淘宝,天猫均有售,欢迎选购。

文章分类

全部博文(1488)

文章存档

2018年(132)

2017年(320)

2016年(356)

2015年(346)

2014年(270)

2013年(43)

2012年(21)

分类: MySQL

2018-05-07 09:44:25

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'

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

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

阅读(1568) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册

/pmw/251V.html /daxue/205G.html /0088/636h.html /ggs/352r.html /bg88/416c.html /ks/877n.html /xyw/268v.html /3088/701b.html /55901/601M.html /0088/452L.html /88410/455J.html /8888/324r.html /1088/860S.html /bosoo/327E.html /888/882T.html /ks/107g.html /bbs/111d.html /ymw/616t.html /bg88/873z.html /123/372n.html /1088/622H.html /xyw/343Z.html /wb/672Q.html /3088/313W.html /3088/883E.html /gpw/806Y.html /bosoo/070m.html /ymw/705R.html