如何理解MySQL表的自動新增AUTO_INCREMENT的值和步長
更新時間 2023-12-16 22:29:22
最近更新時間: 2023-12-16 22:29:22
分享文章
本頁介紹如何理解MySQL表的自動新增AUTO_INCREMENT的值和步長。
表的新增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)
