mysql autoincrementoffset"/>
mysql autoincrementoffset
root@localhost][(none)]> show variables like ‘auto_inc%‘;+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
auto_increment_increment:自增值
auto_increment_offset:漂移值,也就是步长
由于auto_increment_increment 属于全局可变的变量,故此可以通过修改自增值来达到测试目的
[root@localhost][(none)]> create table boss.autoinc1(col int not null auto_increment primary key);
Query OK,0 rows affected (1.03sec)[root@localhost][(none)]> set @@auto_increment_increment=10;
Query OK,0 rows affected (0.00sec)[root@localhost][(none)]> show variables like ‘auto_inc%‘;+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
从上面可以看到,自增从10开始,那么此时插入数据会是什么结果?
[root@localhost][(none)]> insert into boss.autoinc1 values(null),(null),(null),(null);
Query OK,4 rows affected (0.29sec)
Records:4 Duplicates: 0 Warnings: 0
[root@localhost][(none)]> select col fromboss.autoinc1;+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
从结果集来看,auto_increment_increment的自增,为下一个跟上一个的间隔为10,也就是11->21->31->41以此类推
此时,我们设置offset这个的偏移值,那么数据则会
[root@localhost][(none)]> create table boss.autoinc2(col int not null auto_increment primary key);
Query OK,0 rows affected (1.31sec)[root@localhost][(none)]> insert into boss.autoinc2 values(null),(null),(null),(null);
Query OK,4 rows affected (0.14sec)
Records:4 Duplicates: 0 Warnings: 0
[root@localhost][(none)]> select col fromboss.autoinc2;+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
| 35 |
+-----+
4 rows in set (0.00 sec)
可以看到,第一个是从基数1偏移到5个值(1,2,3,4,5),然后自动增值,每次进10这么处理
本质的逻辑为 auto_increment_offset + N × auto_increment_increment N表示第几次,从0的技术开始计算
比如5+0*10,5+1*10,即
[root@localhost][mysql]> set @@auto_increment_offset=5;
Query OK,0 rows affected (0.00sec)[root@localhost][mysql]> create table boss.autoinc6(col int not null auto_increment primary key);
Query OK,0 rows affected (0.36sec)[root@localhost][mysql]> set @@auto_increment_increment=10;
Query OK,0 rows affected (0.00sec)[root@localhost][mysql]> show variables like ‘auto_inc%‘;+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00sec)[root@localhost][mysql]> insert into boss.autoinc6 values(null),(null),(null),(null);
Query OK,4 rows affected (0.08sec)
Records:4 Duplicates: 0 Warnings: 0
[root@localhost][mysql]> select col fromboss.autoinc6;+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
| 35 |
+-----+
4 rows in set (0.00 sec)
MySQL 中有关auto_increment及auto_increment_offset方面的介绍
标签:show 技术 dup color oss logs mysql sql ble
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:.html
更多推荐
mysql autoincrementoffset
发布评论