MySQL auto_increment_increment，auto_increment_offset 用法

出处。 https://blog.csdn.net/robinson_0612/article/details/39779509 

MySQL中对于表上ID自增列可以在创建表的时候来指定列上的auto_increment属性；等同于SQL server中的identity属性；Oracle则是通过Sequence方式来实现。在MySQL中，系统变量auto_increment_increment，auto_increment_offset 影响自增列的值及其变化规则。本文主要描述这两个系统变量的相关用法。

 

1、auto_increment_increment与auto_increment_offset作用

[sql]  view plain  copy

1. auto_increment_increment控制列中的值的增量值，也就是步长。  
2. auto_increment_offset确定AUTO_INCREMENT列值的起点，也就是初始值。  
3. 变量范围：可以在全局以及session级别设置这2个变量  
4.   
5. --当前系统环境  
6. root@localhost[(none)]> show variables like 'version';  
7. +---------------+------------+  
8. | Variable_name | Value      |  
9. +---------------+------------+  
10. | version       | 5.5.39-log |  
11. +---------------+------------+  
12.   
13. root@localhost[mysql]> create database tempdb;  
14.   
15. root@localhost[mysql]> use tempdb;  
16.   
17. --查看变量auto_increment_increment与auto_increment_offset  
18. root@localhost[tempdb]> show variables like '%auto_incre%';  
19. +--------------------------+-------+  
20. | Variable_name            | Value |  
21. +--------------------------+-------+  
22. | auto_increment_increment | 1     |  
23. | auto_increment_offset    | 1     |  
24. +--------------------------+-------+  

2、演示auto_increment_increment与auto_increment_offset

[sql]  view plain  copy

1. --创建演示表，使用auto_increment子句  
2. root@localhost[tempdb]> create table t1(id int not null auto_increment primary key, col varchar(20));  
3.   
4. --插入记录  
5. root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');  
6.   
7. --下面可以看到id列起始值为1，增量为1  
8. root@localhost[tempdb]> select * from t1;  
9. +----+-------+  
10. | id | col   |  
11. +----+-------+  
12. |  1 | robin |  
13. |  2 | fred  |  
14. |  3 | jack  |  
15. |  4 | james |  
16. +----+-------+  
17.   
18. --设置步长为5  
19. root@localhost[tempdb]> set session auto_increment_increment=5;  
20.   
21. root@localhost[tempdb]> show variables like '%auto_incre%';  
22. +--------------------------+-------+  
23. | Variable_name            | Value |  
24. +--------------------------+-------+  
25. | auto_increment_increment | 5     |  
26. | auto_increment_offset    | 1     |  
27. +--------------------------+-------+  
28.   
29. --清空表t1  
30. root@localhost[tempdb]> truncate table t1;  
31.   
32. --再次插入记录  
33. root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');  
34.   
35. --如下查询可以看到步长以5位基数发生变化  
36. root@localhost[tempdb]> select * from t1;  
37. +----+-------+  
38. | id | col   |  
39. +----+-------+  
40. |  1 | robin |  
41. |  6 | fred  |  
42. | 11 | jack  |  
43. | 16 | james |  
44. +----+-------+  
45.   
46. --设置初始值为5  
47. root@localhost[tempdb]> set session auto_increment_offset=5;  
48.   
49. root@localhost[tempdb]> show variables like '%auto_incre%';  
50. +--------------------------+-------+  
51. | Variable_name            | Value |  
52. +--------------------------+-------+  
53. | auto_increment_increment | 5     |  
54. | auto_increment_offset    | 5     |  
55. +--------------------------+-------+  
56.   
57. root@localhost[tempdb]> truncate table t1;  
58.   
59. root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');  
60.   
61. --下面是新的结果  
62. root@localhost[tempdb]> select * from t1;  
63. +----+-------+  
64. | id | col   |  
65. +----+-------+  
66. |  5 | robin |  
67. | 10 | fred  |  
68. | 15 | jack  |  
69. | 20 | james |  
70. +----+-------+  

3、auto_increment_increment与auto_increment_offset取值范围

[sql]  view plain  copy

1. --将变量auto_increment_increment设置为0  
2. root@localhost[tempdb]> set session auto_increment_increment=0;  
3.   
4. --实际值变成了1  
5. root@localhost[tempdb]> show variables like '%auto_increment%';  
6. +--------------------------+-------+  
7. | Variable_name            | Value |  
8. +--------------------------+-------+  
9. | auto_increment_increment | 1     |  
10. | auto_increment_offset    | 5     |  
11. +--------------------------+-------+  
12.   
13. --同样将auto_increment_offset设置为0  
14. root@localhost[tempdb]> set session auto_increment_offset=0;  
15.   
16. --实际值也变成了1  
17. root@localhost[tempdb]> show variables like '%auto_increment%';  
18. +--------------------------+-------+  
19. | Variable_name            | Value |  
20. +--------------------------+-------+  
21. | auto_increment_increment | 1     |  
22. | auto_increment_offset    | 1     |  
23. +--------------------------+-------+  
24.   
25. --下面尝试将2个变量设置为大于65535  
26. root@localhost[tempdb]> set session auto_increment_increment=65537;  
27.   
28. root@localhost[tempdb]> set session auto_increment_offset=65537;  
29.   
30. --其实际的值都变成了65535  
31. root@localhost[tempdb]> show variables like '%auto_increment%';  
32. +--------------------------+-------+  
33. | Variable_name            | Value |  
34. +--------------------------+-------+  
35. | auto_increment_increment | 65535 |  
36. | auto_increment_offset    | 65535 |  
37. +--------------------------+-------+  
38.   
39. --尝试为2个变量设置为负值  
40. root@localhost[tempdb]> set session auto_increment_offset=-2;  
41.   
42. root@localhost[tempdb]> set session auto_increment_increment=-5;  
43.   
44. --下面的查询可以看出全部恢复到缺省值1  
45. root@localhost[tempdb]> show variables like '%auto_increment%';  
46. +--------------------------+-------+  
47. | Variable_name            | Value |  
48. +--------------------------+-------+  
49. | auto_increment_increment | 1     |  
50. | auto_increment_offset    | 1     |  
51. +--------------------------+-------+  
52.   
53. 由上可以看出2个变量只能设置为1至65535之间的整数值。  
54. 所有非正整数全部会置为缺省值1，大于65535的值会被自动置为65535。  

4、全局与session级别的设置

[sql]  view plain  copy

1. --查看全局范围这2个变量的值  
2. root@localhost[tempdb]> show global variables like '%auto_increment%';  
3. +--------------------------+-------+  
4. | Variable_name            | Value |  
5. +--------------------------+-------+  
6. | auto_increment_increment | 1     |  
7. | auto_increment_offset    | 1     |  
8. +--------------------------+-------+  
9.   
10. --下面分别设置session基本的值  
11. root@localhost[tempdb]> set session auto_increment_increment=5;  
12.   
13. root@localhost[tempdb]> set session auto_increment_offset=10;  
14.   
15. --查看session级别的值  
16. root@localhost[tempdb]> show session variables like '%auto_increment%';  
17. +--------------------------+-------+  
18. | Variable_name            | Value |  
19. +--------------------------+-------+  
20. | auto_increment_increment | 5     |  
21. | auto_increment_offset    | 10    |  
22. +--------------------------+-------+  
23.   
24. --查看全局级别的值  
25. root@localhost[tempdb]> show global variables like '%auto_increment%';  
26. +--------------------------+-------+  
27. | Variable_name            | Value |  
28. +--------------------------+-------+  
29. | auto_increment_increment | 1     |  
30. | auto_increment_offset    | 1     |  
31. +--------------------------+-------+  
32.   
33. --设置全局级别的值  
34. root@localhost[tempdb]> set global auto_increment_increment=2;  
35.   
36. root@localhost[tempdb]> set global auto_increment_offset=3;  
37.   
38. root@localhost[tempdb]> show global variables like '%auto_increment%';  
39. +--------------------------+-------+  
40. | Variable_name            | Value |  
41. +--------------------------+-------+  
42. | auto_increment_increment | 2     |  
43. | auto_increment_offset    | 3     |  
44. +--------------------------+-------+  

5、已有auto_increment列值任一变量变化的情形

[sql]  view plain  copy

1. root@localhost[tempdb]> truncate table t1;  
2.   
3. root@localhost[tempdb]> show variables like '%auto_increment%';  
4. +--------------------------+-------+  
5. | Variable_name            | Value |  
6. +--------------------------+-------+  
7. | auto_increment_increment | 1     |  
8. | auto_increment_offset    | 1     |  
9. +--------------------------+-------+  
10.   
11. root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack');            
12.   
13. root@localhost[tempdb]> select * from t1;  
14. +----+-------+  
15. | id | col   |  
16. +----+-------+  
17. |  1 | robin |  
18. |  2 | fred  |  
19. |  3 | jack  |  
20. +----+-------+  
21.   
22. root@localhost[tempdb]> set session auto_increment_increment=5;  
23.   
24. root@localhost[tempdb]> show variables like '%auto_increment%';  
25. +--------------------------+-------+  
26. | Variable_name            | Value |  
27. +--------------------------+-------+  
28. | auto_increment_increment | 5     |  
29. | auto_increment_offset    | 1     |  
30. +--------------------------+-------+  
31.   
32. --Author: Leshami  
33. --Blog  : http://blog.csdn.net/leshami  
34.   
35. root@localhost[tempdb]> insert into t1(col) values('david'),('tim'),('jerry');  
36.   
37. root@localhost[tempdb]> select * from t1;  
38. +----+-------+  
39. | id | col   |  
40. +----+-------+  
41. |  1 | robin |  
42. |  2 | fred  |  
43. |  3 | jack  |  
44. |  6 | david |  
45. | 11 | tim   |  
46. | 16 | jerry |  
47. +----+-------+  
48.   
49. New_value = auto_increment_offset+ N * auto_increment_increment  
50. New_value1 = 1 + 1 * 5 = 6  
51. New_value2 = 1 + 2 * 5 = 11  
52.   
53. --下面是修改auto_increment_offset后的结果  
54. root@localhost[tempdb]> set session auto_increment_offset=2;  
55.   
56. root@localhost[tempdb]> insert into t1(col) values('lewis'),('ian');  
57.   
58. root@localhost[tempdb]> select * from t1;  
59. +----+-------+  
60. | id | col   |  
61. +----+-------+  
62. |  1 | robin |  
63. |  2 | fred  |  
64. |  3 | jack  |  
65. |  6 | david |  
66. | 11 | tim   |  
67. | 16 | jerry |  
68. | 22 | lewis |  
69. | 27 | ian   |  
70. +----+-------+  
71.   
72. 这个id为22，应该是这样推算来的：max(id)+(new_offset-old_offset)+increment  
73. 也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。