mysql自增属性

auto_increment_increment

控制列中的值的增量值,比如:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

mysql> CREATE TABLE auto_test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> SET auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> insert auto_test values (null),(null),(null),(null);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select id from auto_test;
+----+
| id |
+----+
|  1 |
|  6 |
| 11 |
| 16 |
+----+
4 rows in set (0.01 sec)

auto_increment_offset

auto_increment_offset确定AUTO_INCREMENT列值的起始或者起点值.

mysql> SET auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE auto_test1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> insert auto_test1 values (null),(null),(null),(null);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  select id from auto_test1;
+----+
| id |
+----+
|  5 |
| 10 |
| 15 |
| 20 |
+----+
4 rows in set (0.00 sec)

值设置规则

1.auto_increment_offset > auto_increment_increment,mysq会忽略auto_increment_offset这个值.例如:

mysql> SET auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE auto_test3 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> insert auto_test3 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_test3;
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

2.当这两个值有一个被改变时,AUTO_INCREMEN列值的计算方法.auto_increment_offset + N * auto_increment_increment (N >= 0).例如:

mysql> SET auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> select id from auto_test1;
+----+
| id |
+----+
|  5 |
| 10 |
| 15 |
| 20 |
+----+
4 rows in set (0.00 sec)

mysql> insert auto_test1 values (NULL), (null);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select id from auto_test1;
+----+
| id |
+----+
|  5 |
| 10 |
| 15 |
| 20 |
| 27 |
| 32 |
+----+
6 rows in set (0.00 sec)

第5条记录27没有按照自增规则,他是由上面的计算公式来计算的: 2 + 5 * 5 = 27

3.这两个属性是全局属性,不能被用于表级别.因此在mysql中不能通过多表设置不同起始值来实现序列.