暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySQL系统变量auto_increment_increment与auto_increment_offset学习总结

DBA闲思杂想录 2021-03-27
1574

在MySQL中,系统变量auto_increment_increment与auto_increment_offset是与自增列相关的两个参数变量。在官方文档中,将其划分为Replication Master Options and Variables 。具体参考官方文档[1]

auto_increment_offset    : AUTO_INCREMENT列值的起点,也就是初始值。取值范围是1 .. 65535auto_increment_increment : 控制列中的值的增量值,也就是步长。其默认值是1,取值范围是1 .. 65535

系统变量auto_increment_increment与auto_increment_offset 都有会话级别和全局级别两个值(注意:设置全局系统变量时,对当前连接或已存在的连接不生效,只对后续新建立的连接有效)。它们的取值范围为1 ..65535, 如果设置的时候超过这个范围的话,会是什么情况?如下所示:

mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+4 rows in set (0.00 sec)mysql> set session auto_increment_increment=0;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql>  set session auto_increment_increment=65536;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>  show variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 65535 || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> mysql> set session auto_increment_offset=-1;Query OK, 0 rows affected, 1 warning (0.04 sec)mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 65535 || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)

如上所示,如果系统变量设置其值小于1(0或负数),MySQL会默认设置为1 ,如果大于65535,MySQL会默认设置为65535. 也就是说这两个系统变量的取值范围为 1 ... 65535. 不允许超过这个范围,但是超出范围也没啥错误提示。

系统变量auto_increment_increment修改后,自增列的变化规律

如果我们想知道系统变量auto_increment_increment变化后,自增列的变化规律,最简单、有效的方式就是实验测试,如下所示:

mysql> drop table if exists test;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table test(id int auto_increment primary keyname varchar(32));Query OK, 0 rows affected (0.03 sec)mysql> insert into test(namevalue('kerry1');Query OK, 1 row affected (0.00 sec)mysql> insert into test(namevalue('kerry2');Query OK, 1 row affected (0.01 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 |+----+--------+2 rows in set (0.01 sec)mysql> show  variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> set session auto_increment_increment=3;Query OK, 0 rows affected (0.00 sec)mysql> insert into test(namevalue('kerry3');Query OK, 1 row affected (0.01 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  4 | kerry3 |+----+--------+3 rows in set (0.00 sec)mysql> 

mysql>  set session auto_increment_increment=1;Query OK, 0 rows affected (0.00 sec)mysql> show  variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> truncate table test;Query OK, 0 rows affected (0.05 sec)mysql> insert into test(namevalue('kerry1');Query OK, 1 row affected (0.00 sec)mysql> insert into test(namevalue('kerry2');Query OK, 1 row affected (0.00 sec)mysql> insert into test(namevalue('kerry3');Query OK, 1 row affected (0.00 sec)mysql> set session auto_increment_increment=3;Query OK, 0 rows affected (0.00 sec)mysql> show  variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 3     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> insert into test(namevalue('kerry4');Query OK, 1 row affected (0.03 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  3 | kerry3 ||  4 | kerry4 |+----+--------+4 rows in set (0.00 sec)mysql> 

为什么修改auto_increment_increment=3后, 新增的数据id=4呢?其实这个是因为自增列的计算逻辑为 auto_increment_offset + N × auto_increment_increment  N表示第几次,从1开始计算(auto_increment_offset=1),并且计算值必须大于自增列的最大值(Max(ID)),

对于第一种情况:id  1、2,   因为最大值为2,而自增列的取值为1+1*3= 4, 所以自增列取值为4对于第二种情况:id= 1、2、3. 因为最大值为3,而自增列的取值为1+1*3= 4, 所以自增列取值为4

官方文档的描述如下所示:

If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this: auto_increment_offset + N × auto_increment_increment where N is a positive integer value in the series [1, 2, 3, ...].

注意:如果表没有值,自增列第一个值为auto_increment_offset

如下所示对于这种情况,新增列的值为1+2*3=7 ,因为已有1、4这样的取值。

mysql> set session auto_increment_increment=1;Query OK, 0 rows affected (0.00 sec)mysql> show  variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> truncate table test;Query OK, 0 rows affected (0.02 sec)mysql> insert into test(namevalue('kerry1');Query OK, 1 row affected (0.01 sec)mysql> insert into test(namevalue('kerry2');Query OK, 1 row affected (0.02 sec)mysql> insert into test(namevalue('kerry3');Query OK, 1 row affected (0.00 sec)mysql>  insert into test(namevalue('kerry4');Query OK, 1 row affected (0.00 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  3 | kerry3 ||  4 | kerry4 |+----+--------+4 rows in set (0.00 sec)mysql> set session auto_increment_increment=3;Query OK, 0 rows affected (0.00 sec)mysql> insert into test(namevalue('kerry5');Query OK, 1 row affected (0.03 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  3 | kerry3 ||  4 | kerry4 ||  7 | kerry5 |+----+--------+5 rows in set (0.00 sec)

auto_increment_offset修改后,自增列的变化规律

换个测试方法,如果修改系统变量auto_increment_offset的话,自增列会怎么变化呢?且看下面实验

mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  3 | kerry3 ||  4 | kerry4 ||  7 | kerry5 |+----+--------+5 rows in set (0.00 sec)mysql> show  variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 3     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> set auto_increment_offset=2;Query OK, 0 rows affected (0.00 sec)mysql> insert into test(namevalue('kerry6');Query OK, 1 row affected (0.01 sec)mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | kerry1 ||  2 | kerry2 ||  3 | kerry3 ||  4 | kerry4 ||  7 | kerry5 || 11 | kerry6 |+----+--------+6 rows in set (0.00 sec)

其实这个按下面这个公式来计算的:max(id)+(new_offset-old_offset)+increment ,也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。

  • max(id) =7
  • new_offset =2
  • old_offset =1
  • increment =3

另外,需要注意的是:如果设置auto_increment_offset的值远远大于auto_increment_increment, MySQL会忽略系统变量auto_increment_offset的值。When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.(如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略)

另外,关于系统变量auto_increment_increment,在一些早期MySQL版本中存在一个Bug:”Bug 15851528 : DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS“, 下面演示一下这个Bug。更多详细信息参考链接的官方资料:

1:如下所示,首先准备好测速所需的相关表和存储过程。

mysql> select version();+------------+| version()  |+------------+| 5.6.23-log |+------------+1 row in set (0.00 sec)mysql> show variables like 'innodb_autoinc_lock_mode';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_autoinc_lock_mode | 1     |+--------------------------+-------+1 row in set (0.00 sec)mysql> drop table if exists test_items;Query OK, 0 rows affected (0.09 sec)mysql> create table test_items (id int(11) auto_increment primary key);Query OK, 0 rows affected (0.01 sec)mysql> delimiter &&mysql> drop procedure if exists prc_insert;    ->      -> create procedure prc_insert(in  cnt int)    -> begin    -> declare i int;    -> set i=1;    -> while i < cnt do    ->     insert into `test_items` values (DEFAULT);    ->         ->     set i = i+1;    ->      -> end while;    -> end &&Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;delimiter &&drop procedure if exists prc_insert; create procedure prc_insert(in  cnt int)begindeclare i int;set i=1;while i < cnt do    insert into `test_items` values (DEFAULT);        set i = i+1end while;end &&

2:在连接A中,设置系统变量:auto_increment_increment=1

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+|              16 |+-----------------+1 row in set (0.00 sec)mysql> set auto_increment_increment=1;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.01 sec)

3:在连接B中,设置系统变量:auto_increment_increment=2

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+|              14 |+-----------------+1 row in set (0.00 sec)mysql> set auto_increment_increment=2;Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

4:在两个连接中同时执行存储过程call prc_insert(100000); 构造并发插入的场景,就能构造出这个Bug。如下所示:

其实,其它场景也能构造出这样的错误

注意:在官方文档中:Error Duplicate Entry ' ' For Key 'PRIMARY' on Auto-Increment Primary Key when changing AUTO_INCREMENT_INCREMENT (文档 ID 2498911.1),提示这个Bug在下面版本中已经fix掉

This BUG is fixed in 5.5.65, 5.6.45, 5.7.27, 8.0.17 releases.

For older versions than the above versions, workaround is to use the same AUTO_INCREMENT_INCREMENT for all sessions and do not try to set different values in the sessions running in parallel.

参考资料

[1]

17.1.6 Replication and Binary Logging Options and Variables: https://dev.mysql.com/doc/refman/8.0/en/replication-options.html


文章转载自DBA闲思杂想录,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论