MySQL的系统变量或会话变量auto_increment_increment(自增步长)和auto_increment_offset(自增偏移量)控制着数据表的自增列ID。
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE `test_tb1` ( `id` int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql> insert into test_tb1(name) values('Andy'),('Danny');
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test_tb1;
+----+-------+
| id | name |
+----+-------+
| 1 | Andy |
| 2 | Danny |
+----+-------+
2 rows in set (0.04 sec)
mysql> show session variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> set session auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%auto_incre%'; -- 改变自增步长
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> show global variables like '%auto_incre%'; -- 全局变量并没有变化
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.05 sec)
mysql> truncate table test_tb1;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test_tb1(name) values('Andy'),('Bonny'),('Lisa'),('Jack'),('Robin');
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test_tb1;
+----+-------+
| id | name |
+----+-------+
| 1 | Andy |
| 11 | Bonny |
| 21 | Lisa |
| 31 | Jack |
| 41 | Robin |
+----+-------+
5 rows in set (0.00 sec)
获取指定数据表的下一个auto_increment自增值的两种方式:
mysql> show table status \G
*************************** 1. row ***************************
Name: test_tb1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 51
Create_time: 2018-12-19 14:43:53
Update_time: 2018-12-19 14:54:23
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select auto_increment from information_schema.`TABLES` where table_name='test_tb1';
+----------------+
| auto_increment |
+----------------+
| 51 |
+----------------+
1 row in set (0.03 sec)
auto_increment_increment和auto_increment_offset的取值范围： 1 ~ 65535，这个范围和自增列的值没关系，列的数据大小是对应的值类型控制的。
mysql> set auto_increment_increment=-1;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show variables like '%auto_incre%'; -- 小于1时用默认最小值1
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> set auto_increment_increment=65536;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%auto_incre%'; -- 大于65535时用默认最大值65536
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 65535 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> truncate table test_tb1;
Query OK, 0 rows affected (0.49 sec)
mysql> insert into test_tb1(name) values('Andy'), ('Bonny'), ('Danny'), ('Jack'), ('Robin');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test_tb1; -- 值可以大于65535
+--------+-------+
| id | name |
+--------+-------+
| 1 | Andy |
| 65536 | Bonny |
| 131071 | Danny |
| 196606 | Jack |
| 262141 | Robin |
+--------+-------+
5 rows in set (0.00 sec)
mysql> truncate table test_tb1;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test_tb1(id, name) values(-3, 'Andy'), (-2, 'Bonny'), (-1, 'Danny');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_tb1; -- 值可以小于1
+----+-------+
| id | name |
+----+-------+
| -3 | Andy |
| -2 | Bonny |
| -1 | Danny |
+----+-------+
3 rows in set (0.00 sec)
全局(global)与会话(session)自增属性，会话针对当前连接的所有操作，全局变量会在新的连接会话中生效。
mysql> show global variables like 'auto_incre%'; -- 全局的自增属性值
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> show variables like 'auto_incre%'; -- 会话的自增属性值，同show session variables like 'auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 65535 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> set global auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 65535 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> show global variables like 'auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)