表的新增AUTO_INCREMENT的值和步长
背景描述：
AUTO_INCREMENT的初值与步长由"auto_increment_increment" 和"auto_increment_offset"两个参数决定。
1.auto_increment_offset：AUTO_INCREMENT值的初值。
2.auto_increment_increment：AUTO_INCREMENT值每次增长的步长。
3.当 auto_increment_offset > auto_increment_increment 时，实际使用时初值会变为为auto_increment_increment。
4.当 auto_increment_offset <= auto_increment_increment 时，自增值计算方式：值 = auto_increment_offset + N*auto_increment_increment（N为插入的数据条数）。
实战操作：
在高可用关系型MySQL中"auto_increment_increment"和"auto_increment_offset"参数默认都为1。
操作用例如下：
1.auto_increment_offset=1，auto_increment_increment=1，那么初值为1，步长为1。
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table tiayiyun_test(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tiayiyun_test values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.修改auto_increment_increment=2，步长变为2。
mysql> set session auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 2 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into tiayiyun_test values(0), (0), (0);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+-----+
6 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.auto_increment_offset=10，auto_increment_increment=2，初值为2（因为auto_increment_offset > auto_increment_increment），步长为2。
mysql> set session auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)
mysql> set session auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 10 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table tianyiyun_test(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table tianyiyun_test;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiyun_test | CREATE TABLE `tianyiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tianyiyun_test values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tianyiyun_test;
+-----+
| uid |
+-----+
| 2 |
| 4 |
| 6 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table tianyiyun_test;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiyun_test | CREATE TABLE `tianyiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.auto_increment_offset=5，auto_increment_increment=10，初值为5，步长为10。
mysql> set session auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> set session auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table tianyiy(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tianyiy values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tianyiy;
+-----+
| uid |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table tianyiy;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiy | CREATE TABLE `tianyiy` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
