MySQL自增ID相关设置
官网介绍:https://dev.mysql.com/doc/refman/5.7/en/replication-options-source.html
- auto_increment_increment控制列中值的增量,即步长。
- auto_increment_offset确定AUTO_INCREMENT列值的起点,即初始值。
1、验证auto_increment_increment参数
#(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) #(2)创建测试表autoinc1 mysql> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) #(3)设置自增ID新步长为10 mysql> SET @@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 | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec) #(4)插入空测试数据,验证自增情况 mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec)
2、验证auto_increment_offset参数
#(1)修改初始偏移量 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 | 10 | | auto_increment_offset | 5 | +--------------------------+-------+ 2 rows in set (0.00 sec) #(2)创建测试表autoinc2 mysql> CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) #(3)插入测试数据 mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM autoinc2; +-----+ | col | +-----+ | 5 | | 15 | | 25 | | 35 | +-----+ 4 rows in set (0.00 sec)
注:上述修改不会影响存量数据的自增ID情况,详情可以参考如下测试数据。
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> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec) mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | | 45 | | 55 | | 65 | | 75 | +-----+ 8 rows in set (0.00 sec)
作者:UStarGao
链接:https://www.starcto.com/mysql/309.html
来源:STARCTO
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-06-03开源笔记Leanote解决方案
- 2021-05-29Rsync+sersync实现数据实时同步备份
- 2021-08-20Jupyter Notebook工具介绍
- 2021-01-28Docker安装部署教程
- 2022-07-21Ceph集群部署之ceph-deploy工具自动化安装