怎么理解MYSQL的auto_increment_offset和auto_increment_increment值

本篇內(nèi)容主要講解“怎么理解MYSQL的auto_increment_offset和auto_increment_increment值”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“怎么理解MYSQL的auto_increment_offset和auto_increment_increment值”吧!

實(shí)際上兩個(gè)值是這樣的：
我們理解auto_increment_offset為0開始的偏移量
auto_increment_increment是一個(gè)步長
auto_increment_offset+(N-1)*auto_increment_increment
N代表的是插入的次數(shù)。這算出來實(shí)際上是在0-+∽ 之間可以設(shè)置的值。
打個(gè)比方
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)

這樣我們?cè)试S的值是2  7  12 17 ....
我們建立一個(gè)表

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)
可以看到?jīng)]有問題

但是問題是遇到如下一個(gè)提示：
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將被忽略。
這個(gè)也可以理解，比如
auto_increment_offset = 10
auto_increment_increment = 5

按照公式我們第一次插入的值是10 15 20 ，但是我們發(fā)現(xiàn)在0-+∽這樣一個(gè)線性范圍內(nèi)，我們丟掉了一個(gè)
這個(gè)值就是10-5 = 5，如果我們這樣理解就理解得通了，但是事實(shí)真是這樣嗎？
我打開源碼：
看到如下的計(jì)算方式
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進(jìn)行斷點(diǎn)調(diào)試如下：
(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
這里出現(xiàn)了負(fù)數(shù)，但是運(yùn)算的時(shí)候是無符號(hào)longlong類型，自動(dòng)類型轉(zhuǎn)換后得到
了一個(gè)非常大的
$6 = 3689348814741910322
這里出現(xiàn)了異常最后得到了一個(gè)數(shù)字 4
然后我們插入的就是4
mysql> select * from testcr5;
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

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

先給出計(jì)算源碼
  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字節(jié)，所以我們認(rèn)為他們表示的范圍相同，他們則相同
 同時(shí)我們還需要知道ulonglong是不能存儲(chǔ)負(fù)數(shù)的
 而variables->auto_increment_increment - variables->auto_increment_offset =-5 他轉(zhuǎn)換為
 ulong正數(shù)就是 18446744073709551611 為什么是這么多呢？
 首先我們要看5的ulong的表示如下：
 0 0000000 00000000 00000000 00000000 00000000 00000000 00000000 00000101 最開始的是符號(hào)位
 反碼
 0 1111111 11111111 11111111 11111111 11111111 11111111 1111111111111010 
 補(bǔ)碼
 0 1111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011
 我們都沒有動(dòng)符號(hào)位，實(shí)際上負(fù)數(shù)的符號(hào)位是1所以是
 1 1111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011
 好下面我們看看他的16進(jìn)制表示
 FF FF FF FF FF FF FF FB 這就是-5long的表示，因?yàn)閁LONG沒有負(fù)數(shù)那么將符號(hào)位作為數(shù)字表示位
 那么轉(zhuǎn)換為10進(jìn)制實(shí)際上就是
 18446744073709551611
 下面是我GDB 出來的，因?yàn)樾《薒ittle_endian是不管在內(nèi)存和磁盤中存儲(chǔ)都是內(nèi)存的低地址存儲(chǔ)數(shù)值的低位數(shù)
 實(shí)際上0xfb    0xff    0xff    0xff    0xff    0xff    0xff    0xff 
 fb是低位
( http://blog.itpub.net/7728585/viewspace-2124159/ 關(guān)于大端小端）
 (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
實(shí)際上就是
18446744073709551611 / 5 = 3689348814741910322 
為什么是3689348814741910322 明顯丟掉了一個(gè)1
實(shí)際上
3689348814741910322*5 = 18446744073709551610
因?yàn)檎麛?shù)是不能表示浮點(diǎn)數(shù)的，在C語言中使用丟棄小數(shù)點(diǎn)后的值。這里就丟了1，這其實(shí)就是為什么是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 我們已經(jīng)說了他的值就是
       3689348814741910322*5 = 18446744073709551610
       然后 
       18446744073709551610+variables->auto_increment_offset
       就是
       18446744073709551610+10
       我來看一下 18446744073709551610 二進(jìn)制
       11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111010
       10的二進(jìn)制
       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
     就是十進(jìn)制的4 。
 這就是4計(jì)算出來的原因。
 所以MYSQL官方文檔使用一個(gè)忽略來表示，實(shí)際上是不確定的值如果        
 如果
 auto_increment_offset 遠(yuǎn)遠(yuǎn)大于 variables->auto_increment_increment
 比如auto_increment_offset=1000
     auto_increment_increment = 2
 那么只要
 nr+ variables->auto_increment_increment< variables->auto_increment_offset
 那么值都是不確定的這里的nr是存儲(chǔ)上一次來的自增值，初始為0
 nr+ variables->auto_increment_increment - variables->auto_increment_offset
所以基于這個(gè)原因，建議大家注意auto_increment_increment 大于 auto_increment_offset
是必要的。 

下面是一個(gè)簡單程序演示這個(gè)過程：

點(diǎn)擊(此處)折疊或打開

1. #include<stdio.h>
   

2. 
   

3. typedef unsigned long long ulonglong;
   

4. typedef unsigned long ulong;
   

5. 
   

6. int main(void)
   

7. {
   

8.         ulonglong nr = 0;
   

9.         ulonglong nr1;
   

10.         ulong auto_increment_increment = 5;
    

11.         ulong auto_increment_offset = 10;
    

12.         ulonglong t1=-5;
    

13.         ulonglong test1;
    

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

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

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

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

18.         nr= (((nr+ auto_increment_increment - auto_increment_offset))/(ulonglong)auto_increment_increment );
    

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

20.         printf("nr+ auto_increment_increment - auto_increment_offset))/(ulonglong)auto_increment_increment is:%llu\n",nr);
    

21.         test1 = nr* (ulonglong)auto_increment_increment;
    

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

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

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

25. 
    

26. }

跑一下如下：
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

到此，相信大家對(duì)“怎么理解MYSQL的auto_increment_offset和auto_increment_increment值”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！