MySQL數值型別在binlog中需要注意的細節(r12筆記第69天)

    MySQL裡的數值型別分得很細，光整型資料就有多種資料型別。tinyint,smallint,mediumint,int(integer),還有範圍最大的bigint，它們對應的數值範圍也大大不同，大體來說就是下面的數值範圍，從有符號數和無符號數來區別對待。

型別名稱	有符號數（signed)	無符號數(Unsigned)
tinyint	-129~127	0~255
smallint	-32768~32767	0~65535
mediumint	-8388608~8388607	0~16777215
int(integer)	-2147483648~2147483647	0~4294967295
bigint	-9223372036854775808~ 9223372036854775807	0~18446744073709551615

   這一點上Oracle做得很大氣，直接一個number型別，精度也包了，兩者在這個地方風格截然不同。

   對於MySQL的資料型別，我們來說說bigint,如果按照無符號數，最大的值為18446744073709551615，這是一個相當大的數字，如果從有符號資料的角度來看就是-1，那麼問題來了，在MySQL的binlog裡面是否會區分signed還是unsigned呢，如果不區分，這類問題該怎麼應對。

  

  我做了如下的測試,使用conv來做進位制轉換。

> select conv(-1,10,2);
+------------------------------------------------------------------+
| conv(-1,10,2)                                                    |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 |
+------------------------------------------------------------------+

> select conv(18446744073709551615,10,2);
+------------------------------------------------------------------+
| conv(18446744073709551615,10,2)                                  |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 |
+------------------------------------------------------------------+從機制轉換的結果來看，兩者是沒有差別的，如果是實際的場景中，這可是天壤之別。

我們換一個角度來轉換一下。

> select conv(repeat(1,64),2,-10);
+--------------------------+
| conv(repeat(1,64),2,-10) |
+--------------------------+
| -1                       |
+--------------------------+

> select conv(repeat(1,64),2,10);
+-------------------------+
| conv(repeat(1,64),2,10) |
+-------------------------+
| 18446744073709551615    |
+-------------------------+這麼看來，讓人有些擔憂，如果達到這種資料的臨界點，會發生什麼意料之外的結果呢。

我們來建立一個表，指定兩個欄位，一個為有符號型別，一個為無符號型別，然後對應的數字，從binlog來看看解析出來的結果。

create table t1 (id int unsigned not null auto_increment primary key, col1 bigint unsigned, col2 bigint signed) engine=innodb;接著我們切一下日誌，檢視一下master對的狀態，得到日誌的偏移量和binlog名字。

> flush logs; show master status;
+---------------+----------+
| File          | Position |
+---------------+----------+
| binlog.000031 |      107 |
+---------------+----------+這個時候我們插入兩列值，一個無符號，一個有符號。

insert into t1 (col1, col2) values (18446744073709551615, -1);然後使用flush logs再次切換日誌。

檢視資料的情況，可以從輸出看出兩者是有明顯的差別的。

> select * from t1;
+----+----------------------+------+
| id | col1                 | col2 |
+----+----------------------+------+
|  1 | 18446744073709551615 |   -1 |
+----+----------------------+------+我們從binlog來解析一下。

mysqlbinlog -vv binlog.000031

得到的部分日誌如下：

### INSERT INTO test.t1
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=-1 (18446744073709551615) /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=-1 (18446744073709551615) /* LONGINT meta=0 nullable=1 is_null=0 */
# at 268
#170519 18:54:47 server id 13386  end_log_pos 295       Xid = 76
COMMIT/*!*/;這樣看來對於binlog中，有符號數和無符號數都會按照無符號數來轉換，當然直接看資料型別是沒有標識有符號和無符號的差別的。所以如果是單純要解析binlog處理資料就需要考慮到這個地方的差別，對此一種思路是檢視information_schema中的列資訊來做出更加明確的判斷。

參考資料：https://mariadb.com/resources/blog/sign-row-based-binary-logging-and-integer-signedness-mysql-and-mariadb