【MySQL】bit 類型引發的故事

    對一個表進行建立索引後,開發報告說之前可以查詢出結果的查詢在建立索引之後查詢不到結果:

mysql> select count(*) from `node` where uid='1655928604919847' and is_deleted='0';

+----------+

| count(*) |

|        0     |

1 row in set, 1 warning (0.00 sec)

而正确的結果是

mysql>   select count(*) from `test_node` where uid='1655928604919847' and is_deleted='0';   

|      107 |

1 row in set (0.00 sec)

為什麼加上索引之後就沒有結果了呢？檢視表結構如下:

mysql> show create table test_node \g

*************************** 1. row ***************************

       table: test_node

create table: create table `test_node` (

  `node_id` int(11) not null auto_increment comment '主鍵anto_increment',

 ....

  `is_deleted` bit(1) not null default b'0', ---is_deleted 是bit 類型的！

  `creator` int(11) not null,

  `gmt_created` datetime not null,

...

  primary key (`node_id`),

  key `node_uid` (`uid`),

  key `ind_n_aid_isd_state` (`uid`,`is_deleted`,`state`)

) engine=innodb auto_increment=18016 default charset=utf8

問題就出現在bit 類型的字段上面。

為加索引之前

mysql> explain select count(*) from `test_node` where uid='1655928604919847' and is_deleted='0' \g

           id: 1

  select_type: simple

        table: node

         type: ref

possible_keys: node_uid

          key: node_uid

      key_len: 8

          ref: const

         rows: 197

        extra: using where

對該表加上了索引之後,原來的sql 選擇了索引

  select_type: simple

        table: test_node

    type: ref

possible_keys: node_uid,ind_n_aid_isd_state

          key: ind_n_aid_isd_state

      key_len: 13

          ref: const,const

         rows: 107

        extra: using where; using index

1 row in set (0.00 sec

去掉使用ind_n_aid_isd_state索引,是有結果集的！

mysql>select count(*) from `test_node` ignore index(ind_n_aid_isd_state) where uid='1655928604919847' and is_deleted='0';   

分析至此,我們知道了問題出在索引上面。

 key `ind_n_aid_isd_state` (`uid`,`is_deleted`,`state`)

sql 先從 test_node 表中選擇中 uid='1655928604919847'的記錄,然後從結果集中選擇is_deleted='0'的行,但是對于bit類型的記錄,在索引中存儲的内容與'0'不等。是以選擇不出is_deleted='0'的行,是以結果幾為0.

<b>接下來,我們對mysql的bit位做一個介紹。</b>

mysql5.0以前,bit隻是tinyint的同義詞而已。但是在mysql5.0以及之後的版本,bit是一個完全不同的資料類型!

使用bit資料類型儲存位段值。bit(m)類型允許存儲m位值。m範圍為1到64,bit(1)定義一個了隻包含單個比特位的字段, bit(2)是存儲2個比特位的字段,一直到64位。要指定位值,可以使用b'value'符。value是一個用0和1編寫的二進制值。例如,b'111'和b'100000000'分别表示7和128。如果為bit(m)列配置設定的值的長度小于m位,在值的左邊用0填充。例如,為bit(6)列配置設定一個值b'101',其效果與配置設定b'000101'相同。

<b>mysql把bit當做字元串類型, 而不是資料類型。當檢索bit(1)列的值, 結果是一個字元串且内容是二進制位0或1, 而不是ascii值”0″或”1″.然而, </b>

<b>如果在一個數值上下文檢索的話, 結果是比特串轉化而成的數字.當需要與另一個值進行比較時,如果存儲值’00111010′(是58的二進制表示)到一個bit(8)的字段中然後檢索出來,得到的是字元串 ':'---ascii編碼為58,但是在數值環境中, 得到的是值58</b>

解釋到這裡，剛開始的問題就迎刃而解了。

問題是存儲的結果值容易混淆,存儲00111001時,傳回時的10進制數,還是ascii碼對應的字元？

來看看具體的值

root@rac1 : test 22:13:47&gt; create table bittest(a bit(8));        

query ok, 0 rows affected (0.01 sec)

root@rac1 : test 22:21:25&gt; insert into bittest values(b'00111001');

query ok, 1 row affected (0.00 sec)

root@rac1 : test 22:28:36&gt; insert into bittest values(b'00111101');           

root@rac1 : test 22:28:54&gt; insert into bittest values(b'00000001');       

root@rac1 : test 20:11:30&gt; insert into bittest values(b'00111010');

root@rac1 : test 20:12:24&gt; insert into bittest values(b'00000000');      

root@rac1 : test 20:16:42&gt; select a,a+0,bin(a) from bittest ;

+------+------+--------+

| a    | a+0  | bin(a) |

|      |    0 | 0      | 

|    |    1 | 1      |

| 9    |   57 | 111001 |

| :    |   58 | 111010 |

| =    |   61 | 111101 |

5 rows in set (0.00 sec)

從結果中可以看到 存儲情況

root@rac1 : test 20:14:59&gt; select a,a+0,bin(a),oct(a),hex(a) from bittest;

+------+------+--------+--------+--------+

| a    | a+0  | bin(a) | oct(a) | hex(a) |

|      |    0 | 0      | 0      | 0      |

|    |    1 | 1      | 1      | 1      |

| 9    |   57 | 111001 | 71     | 39     |

| :    |   58 | 111010 | 72     | 3a     |

| =    |   61 | 111101 | 75     | 3d     |

模拟線上環境對表bittest 加上索引：

root@rac1 : test 22:30:13&gt; alter table bittest add key ind_a(a);

query ok, 0 rows affected (0.05 sec)

records: 0  duplicates: 0  warnings: 0

root@rac1 : test 20:55:11&gt; select * from bittest where a='0';                     

empty set (0.00 sec)  ---結果集為空。

檢視執行計劃，使用了索引。

root@rac1 : test 20:55:17&gt; explain select * from bittest where a='0'; 

+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+

| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | extra                    |

|  1 | simple      | bittest | ref  | ind_a         | ind_a | 2       | const |    1 | using where; using index |

強制不走索引的話，結果集含有記錄：

root@rac1 : test 20:55:25&gt; select * from bittest ignore index (ind_a) where a='0';

+------+

| a    |

|      |

下面我們檢視一下where 條件的 布爾值：

root@rac1 : test 21:00:11&gt; select b'0'=0;  

+--------+

| b'0'=0 |

|      1 |

root@rac1 : test 21:00:22&gt; select b'0'='0';

| b'0'='0' |

|        0 |

bit 類型的 b'0'==0,b'0'!='0' ,哪個值 等于'0'? 答案是ascii 值為48的

root@rac1 : test 21:01:18&gt; select b'110000'='0';

+---------------+

| b'110000'='0' |

|             1 |

root@rac1 : test 21:01:28&gt; select b'110000'+0;

+-------------+

| b'110000'+0 |

|          48 |

如果使用 a=0 作為條件的話，依然有結果

root@rac1 : test 21:00:25&gt; explain select * from bittest where a=0;  

root@rac1 : test 21:00:35&gt; select * from bittest where a=0;   

是以，可以做一個小結：

對于bit 類型的數值

不使用使用索引，mysql 檢索bit的值是不管是數值還是字元，mysql會對where 條件進行類型轉化,将字元轉換為數值，并比較數值對應的ascii碼，如果值為1，則傳回結果，否則，結果為空。

root@rac1 : test 21:08:37&gt; select * from bittest ignore index (ind_a) where a='48';

| 0    |

将字元串'48'轉化為數值的48也即b'110000'，和字元'0'的ascii 碼做比較 

root@rac1 : test 21:08:48&gt; select * from bittest ignore index (ind_a) where a=48; 

使用索引時:bit位在索引中存儲的格式是bin類型，即'0'/'1'bit位，且不會對字元串進行數值轉換。

root@rac1 : test 21:08:58&gt; select * from bittest  where a=57;

| 9    |

字元'9'對應的ascii碼代碼為57 而不是字元串'57'

root@rac1 : test 21:09:10&gt; select * from bittest  where a='57';

empty set (0.01 sec)