实验环境:

mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | |
| auto_increment_offset | |
+--------------------------+-------+
rows in set (0.03 sec)
auto_increment_offset:默认自增长起始点值;
auto_increment_increment:默认自增长增量值;
MySQL自增长的bug?-LMLPHP
MySQL自增长的bug?-LMLPHP

(截图取材于标准MySQL5.7官方文档--没有盗版)

mysql> create table t1(id int not null primary key auto_increment);
Query OK, rows affected (0.21 sec) mysql> insert into t1 values(null),(null),(null);
Query OK, rows affected (0.06 sec)
Records: Duplicates: Warnings: mysql> select * from t1;
+----+
| id |
+----+
| |
| |
| |
+----+
rows in set (0.00 sec) 

1、修改自增长变量值

mysql> set @@auto_increment_offset=;
Query OK, rows affected (0.00 sec) mysql> set @@auto_increment_increment=;
Query OK, rows affected (0.00 sec) mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | |
| auto_increment_offset | |
+--------------------------+-------+
rows in set (0.02 sec)

通过变量的修改,现在的自增长起始值是10,增量是5。

2、建表插值

mysql> create table t2(id int not null primary key auto_increment);
Query OK, rows affected (0.11 sec) mysql> insert into t2 values(null),(null),(null);
Query OK, rows affected (0.04 sec)
Records: Duplicates: Warnings:

因为自增长起始值是10,增量是5;猜想结果值是10,15,20

3、查询结果

mysql> select * from t2;
+----+
| id |
+----+
| |
| |
| |
+----+
rows in set (0.01 sec)

结果却非我们所猜想的???

继续插入值

mysql> insert into t2 values(null),(null),(null);
Query OK, rows affected (0.05 sec)
Records: Duplicates: Warnings: mysql> select * from t2;
+----+
| id |
+----+
| |
| |
| |
| |
| |
| |
+----+
rows in set (0.00 sec)

经过多组测试,结果依旧(试验其他值都是正常的,唯独上述有问题)

那么,

  问题来了,auto_increment_offset=10和auto_increment_increment=5这一组数值算是MySQL自增长里的一个bug吗?

05-04 05:16