怎么理解MYSQL的auto

137次閱讀

共計 7468 個字符，預計需要花費 19 分鐘才能閱讀完成。

行業資訊
數據庫
MySQL 數據庫
怎么理解 MYSQL 的 auto_increment_offset 和 auto_increment_increment 值

本篇內容主要講解“怎么理解 MYSQL 的 auto_increment_offset 和 auto_increment_increment 值”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“怎么理解 MYSQL 的 auto_increment_offset 和 auto_increment_increment 值”吧!

實際上兩個值是這樣的：
我們理解 auto_increment_offset 為 0 開始的偏移量
auto_increment_increment 是一個步長
auto_increment_offset+(N-1)*auto_increment_increment
N 代表的是插入的次數。這算出來實際上是在 0 -+∽ 之間可以設置的值。
打個比方
mysql set auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)

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

這樣我們允許的值是 2 7 12 17 ….
我們建立一個表

mysql create table testcr11(id int primary key auto_increment) AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.22 sec)
mysql insert into testcr11 values(NULL);
Query OK, 1 row affected (0.01 sec)
mysql select * from testcr11;
+—-+
| id |
+—-+
| 2 |
+—-+
1 row in set (0.00 sec)

可以看到值并不是 1 開始而是 2，在插入一行

mysql insert into testcr11 values(NULL);
Query OK, 1 row affected (0.20 sec)
mysql select * from testcr11;
+—-+
| id |
+—-+
| 2 |
| 7 |
+—-+
2 rows in set (0.00 sec)
可以看到沒有問題

但是問題是遇到如下一個提示：
When the value of auto_increment_offset is greater than that of
auto_increment_increment, the value of auto_increment_offset is ignored

也就是如果 auto_increment_offset auto_increment_increment ,auto_increment_offset 將被忽略。
這個也可以理解，比如
auto_increment_offset = 10
auto_increment_increment = 5

按照公式我們第一次插入的值是 10 15 20，但是我們發現在 0 -+∽這樣一個線性范圍內，我們丟掉了一個
這個值就是 10-5 = 5，如果我們這樣理解就理解得通了，但是事實真是這樣嗎？
我打開源碼：
看到如下的計算方式
inline ulonglong
compute_next_insert_id(ulonglong nr,struct system_variables *variables)
{
const ulonglong save_nr= nr;
if (variables- auto_increment_increment == 1)
nr= nr + 1; // optimization of the formula below
else
{
nr= (((nr+ variables- auto_increment_increment –
variables- auto_increment_offset)) /
(ulonglong) variables- auto_increment_increment);
nr= (nr* (ulonglong) variables- auto_increment_increment +
variables- auto_increment_offset);
}

if (unlikely(nr = save_nr))
return ULLONG_MAX;
return nr;
}

我使用了 GDB 進行斷點調試如下：
(gdb) p nr
$1 = 0
(gdb) n
3479 if (variables- auto_increment_increment == 1)
(gdb) p save_nr
$2 = 0
(gdb) p variables- auto_increment_increment
$3 = 5
(gdb) p variables- auto_increment_offset
$4 = 10
(gdb) n
3485 (ulonglong) variables- auto_increment_increment);
(gdb) p nr
$5 = 0
(gdb) n
3487 variables- auto_increment_offset);
(gdb) p nr
$6 = 3689348814741910322
(gdb) n
3490 if (unlikely(nr = save_nr))
(gdb) p save_nr
$7 = 0
(gdb) p nr
$8 = 4
(gdb) n
3493 return nr;

這樣我們找到了問題所在
(gdb) p nr
$6 = 3689348814741910322

這里
(((nr+ variables- auto_increment_increment –
variables- auto_increment_offset)) /
(ulonglong) variables- auto_increment_increment);
variables- auto_increment_increment –
variables- auto_increment_offset
這里出現了負數，但是運算的時候是無符號 longlong 類型，自動類型轉換后得到
了一個非常大的
$6 = 3689348814741910322
這里出現了異常最后得到了一個數字 4
然后我們插入的就是 4
mysql select * from testcr5;
+—-+
| id |
+—-+
| 4 |
+—-+
1 row in set (0.00 sec)

也許如果 auto_increment_offset auto_increment_increment 會由于轉換問題得到一個
不確定的結果干脆叫做
When the value of auto_increment_offset is greater than that of
auto_increment_increment, the value of auto_increment_offset is ignored
——————————————————————————————————————
下面是具體計算過程：
如果我們要刨根問題為什么是 4 這個問題需要涉及到很多東西我們先來看變量的類型

先給出計算源碼
typedef unsigned long long ulonglong;
typedef unsigned long ulong;

nr= (((nr+ variables- auto_increment_increment –
variables- auto_increment_offset)) /
(ulonglong) variables- auto_increment_increment);

nr= (nr* (ulonglong) variables- auto_increment_increment +
variables- auto_increment_offset);

給出類型

nr (ulonglong *) =0(初始)
variables- auto_increment_increment (ulong *) =5
variables- auto_increment_offset (ulong *) =10

在 64 位 LINUX 上 ULONG 和 ULONGLONG 都是 8 字節，所以我們認為他們表示的范圍相同，他們則相同
同時我們還需要知道 ulonglong 是不能存儲負數的
而 variables- auto_increment_increment – variables- auto_increment_offset =-5 他轉換為
ulong 正數就是 18446744073709551611 為什么是這么多呢？
首先我們要看 5 的 ulong 的表示如下：
0 0000000 00000000 00000000 00000000 00000000 00000000 00000000 00000101 最開始的是符號位
反碼
0 1111111 11111111 11111111 11111111 11111111 11111111 1111111111111010
補碼
0 1111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011
我們都沒有動符號位，實際上負數的符號位是 1 所以是
1 1111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011
好下面我們看看他的 16 進制表示
FF FF FF FF FF FF FF FB 這就是 -5long 的表示，因為 ULONG 沒有負數那么將符號位作為數字表示位
那么轉換為 10 進制實際上就是
18446744073709551611
下面是我 GDB 出來的，因為小端 Little_endian 是不管在內存和磁盤中存儲都是內存的低地址存儲數值的低位數
實際上 0xfb 0xff 0xff 0xff 0xff 0xff 0xff 0xff
fb 是低位
(http://blog.itpub.net/7728585/viewspace-2124159/ 關于大端小端）
(gdb) p test
$1 = 18446744073709551611
(gdb) p test
$2 = (ulonglong *) 0x7fffffffea28
(gdb) x/8bx 0x7fffffffea28
0x7fffffffea28: 0xfb 0xff 0xff 0xff 0xff 0xff 0xff 0xff
既然
nr+ variables- auto_increment_increment = 18446744073709551611
我們來看下一步
/(ulonglong) variables- auto_increment_increment
實際上就是
18446744073709551611 / 5 = 3689348814741910322
為什么是 3689348814741910322 明顯丟掉了一個 1
實際上
3689348814741910322*5 = 18446744073709551610
因為整數是不能表示浮點數的，在 C 語言中使用丟棄小數點后的值。這里就丟了 1，這其實就是為什么是 4 而不是 5 的原因
那么 (初始的 nr=0)
nr= (((nr+ variables- auto_increment_increment –
variables- auto_increment_offset)) /
(ulonglong) variables- auto_increment_increment);
nr = 3689348814741910322
接下來做的是
nr= (nr* (ulonglong) variables- auto_increment_increment +variables- auto_increment_offset);
nr* (ulonglong) variables- auto_increment_increment 我們已經說了他的值就是
3689348814741910322*5 = 18446744073709551610
然后
18446744073709551610+variables- auto_increment_offset
就是
18446744073709551610+10
我來看一下 18446744073709551610 二進制
11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111010
10 的二進制
1010 低位相加
11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111010
+ 1010
———————————————————————–
1 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000100
我們明顯的看到了溢出。溢出就拋棄掉了剩下就是
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000100
就是十進制的 4。
這就是 4 計算出來的原因。
所以 MYSQL 官方文檔使用一個忽略來表示，實際上是不確定的值如果
如果
auto_increment_offset 遠遠大于 variables- auto_increment_increment
比如 auto_increment_offset=1000
auto_increment_increment = 2
那么只要
nr+ variables- auto_increment_increment variables- auto_increment_offset
那么值都是不確定的這里的 nr 是存儲上一次來的自增值，初始為 0
nr+ variables- auto_increment_increment – variables- auto_increment_offset
所以基于這個原因，建議大家注意 auto_increment_increment 大于 auto_increment_offset
是必要的。

下面是一個簡單程序演示這個過程：

點擊 (此處) 折疊或打開

#include stdio.h

typedef unsigned long long ulonglong;

typedef unsigned long ulong;

int main(void)

{

ulonglong nr = 0;

ulonglong nr1;

ulong auto_increment_increment = 5;

ulong auto_increment_offset = 10;

ulonglong t1=-5;

ulonglong test1;

printf(ulonglong size is:%lu ulong size is:%lu\n ,sizeof(unsigned long long),sizeof(unsigned long));

printf(nr init values is:%llu\n ,nr);

printf(auto_increment_increment is:%lu\n ,auto_increment_increment);

printf(auto_increment_offset is :%lu\n ,auto_increment_offset);

nr= (((nr+ auto_increment_increment – auto_increment_offset))/(ulonglong)auto_increment_increment );

printf(-5 ulonglong is :%llu\n ,t1);

printf(nr+ auto_increment_increment – auto_increment_offset))/(ulonglong)auto_increment_increment is:%llu\n ,nr);

test1 = nr* (ulonglong)auto_increment_increment;

nr= (nr* (ulonglong)auto_increment_increment + auto_increment_offset);

printf(nr* (ulonglong)auto_increment_increment is: %llu\n ,test1);

printf(last nr is: %llu\n ,nr);

}

跑一下如下：
ulonglong size is:8 ulong size is:8
nr init values is:0
auto_increment_increment is:5
auto_increment_offset is :10
-5 ulonglong is :18446744073709551611
nr+ auto_increment_increment – auto_increment_offset))/(ulonglong)auto_increment_increment is:3689348814741910322
nr* (ulonglong)auto_increment_increment is: 18446744073709551610
last nr is: 4

到此，相信大家對“怎么理解 MYSQL 的 auto_increment_offset 和 auto_increment_increment 值”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完