MySQL 中有關auto_increment及auto_increment_offset方面的介紹

標籤：show   技術   dup   color   oss   logs   mysql   sql   ble   

資料庫查詢中，涉及到auto_increment中的參數變數一共有兩個

[[email protected]][(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 屬於全域可變的變數，故此可以通過修改自增值來達到測試目的

[[email protected]][(none)]> create table boss.autoinc1(col int not null auto_increment primary key);Query OK, 0 rows affected (1.03 sec)[[email protected]][(none)]> set @@auto_increment_increment=10;Query OK, 0 rows affected (0.00 sec)[[email protected]][(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開始，那麼此時插入資料會是什麼結果？

[[email protected]][(none)]> insert into boss.autoinc1 values(null),(null),(null),(null);Query OK, 4 rows affected (0.29 sec)Records: 4  Duplicates: 0  Warnings: 0[[email protected]][(none)]> select col from boss.autoinc1;+-----+| col |+-----+|   1 ||  11 ||  21 ||  31 |+-----+4 rows in set (0.00 sec)

從結果集來看，auto_increment_increment的自增，為下一個跟上一個的間隔為10，也就是11->21->31->41以此類推

此時，我們設定offset這個的位移值，那麼資料則會

[[email protected]][(none)]> create table boss.autoinc2(col int not null auto_increment primary key);Query OK, 0 rows affected (1.31 sec)[[email protected]][(none)]> insert into boss.autoinc2 values(null),(null),(null),(null);Query OK, 4 rows affected (0.14 sec)Records: 4  Duplicates: 0  Warnings: 0[[email protected]][(none)]> select col from boss.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，即

[[email protected]][mysql]> set @@auto_increment_offset=5;Query OK, 0 rows affected (0.00 sec)[[email protected]][mysql]> create table boss.autoinc6(col int not null auto_increment primary key);Query OK, 0 rows affected (0.36 sec)[[email protected]][mysql]> set @@auto_increment_increment=10;Query OK, 0 rows affected (0.00 sec)[[email protected]][mysql]> show variables like ‘auto_inc%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 10    || auto_increment_offset    | 5     |+--------------------------+-------+2 rows in set (0.00 sec)[[email protected]][mysql]> insert into boss.autoinc6 values(null),(null),(null),(null);Query OK, 4 rows affected (0.08 sec)Records: 4  Duplicates: 0  Warnings: 0[[email protected]][mysql]> select col from boss.autoinc6;+-----+| col |+-----+|   5 ||  15 ||  25 ||  35 |+-----+4 rows in set (0.00 sec)

 

MySQL 中有關auto_increment及auto_increment_offset方面的介紹