auto_increment_increment和auto_increment_offset
可以在全局global,本地session或者local动态设置这两个参数以控制auto_increment的增量与偏移量。
当修改auto_increment_offset之后的第一个auto_increment值一定是能够满足大于当前auto_increment最大值的最小倍数值,
之后再插入新值增量就为auto_increment_increment了。
看下测试情况:
mysql> set @@session.auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_increment_increment';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set @@session.auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_increment_increment';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> select * from tab_auto_incr2;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 2 | 1 |
| 4 | 1 |
| 10 | 1 |
| 11 | 1 |
| 20 | 1 |
| 22 | 1 |
| 24 | 1 |
| 26 | 1 |
| 28 | 1 |
| 30 | 1 |
| 31 | 1 |
| 41 | 1 |
| 51 | 1 |
| 61 | 1 |
| 71 | 1 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
| 10 | 2 |
| 11 | 2 |
| 20 | 2 |
| 22 | 2 |
| 30 | 2 |
+----+---+
24 rows in set (0.00 sec)
mysql> insert into tab_auto_incr2(b) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_auto_incr2;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 2 | 1 |
| 4 | 1 |
| 10 | 1 |
| 11 | 1 |
| 20 | 1 |
| 22 | 1 |
| 24 | 1 |
| 26 | 1 |
| 28 | 1 |
| 30 | 1 |
| 31 | 1 |
| 41 | 1 |
| 51 | 1 |
| 61 | 1 |
| 71 | 1 |
| 75 | 1 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
| 10 | 2 |
| 11 | 2 |
| 20 | 2 |
| 22 | 2 |
| 30 | 2 |
+----+---+
25 rows in set (0.00 sec)
可以看到,大于最大值71的且为5(auto_increment_offset=5)的倍数的最小值是75,所以在第一次insert时新值是75,再次执行insert,
mysql> insert into tab_auto_incr2(b) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_auto_incr2;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 2 | 1 |
| 4 | 1 |
| 10 | 1 |
| 11 | 1 |
| 20 | 1 |
| 22 | 1 |
| 24 | 1 |
| 26 | 1 |
| 28 | 1 |
| 30 | 1 |
| 31 | 1 |
| 41 | 1 |
| 51 | 1 |
| 61 | 1 |
| 71 | 1 |
| 75 | 1 |
| 85 | 1 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
| 10 | 2 |
| 11 | 2 |
| 20 | 2 |
| 22 | 2 |
| 30 | 2 |
+----+---+
26 rows in set (0.00 sec)
可以看到,再次增加新值之后,就按auto_increment_increment(=10)进行新增