【MySQL】bit 型別引發的故事

楊奇龍發表於2012-09-15
MySql型別
       對一個表進行建立索引後,開發報告說之前可以查詢出結果的查詢在建立索引之後查詢不到結果:
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';   
+----------+
| count(*) |
+----------+
|      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
*************************** 1. row ***************************
           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
1 row in set (0.00 sec)
對該表加上了索引之後,原來的sql 選擇了索引
mysql> explain SELECT count(*) FROM `test_node` WHERE uid='1655928604919847' AND is_deleted='0' \G
*************************** 1. row ***************************
           id: 1
  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';   
+----------+
| count(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)
分析至此,我們知道了問題出在索引上面。
 KEY `ind_n_aid_isd_state` (`uid`,`is_deleted`,`state`)
sql 先從 test_node 表中選擇中 uid='1655928604919847'的記錄,然後從結果集中選擇is_deleted='0'的行,但是對於bit型別的記錄,在索引中儲存的內容與'0'不等。所以選擇不出is_deleted='0'的行,因此結果幾為0.
接下來,我們對mysql的bit位做一個介紹。
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'相同。
MySQL把BIT當做字串型別, 而不是資料型別。當檢索BIT(1)列的值, 結果是一個字串且內容是二進位制位0或1, 而不是ASCII值”0″或”1″.然而, 
如果在一個數值上下文檢索的話, 結果是位元串轉化而成的數字.當需要與另一個值進行比較時,如果儲存值’00111010′(是58的二進位制表示)到一個BIT(8)的欄位中然後檢索出來,得到的是字串 ':'---ASCII編碼為58,但是在數值環境中, 得到的是值58
解釋到這裡，剛開始的問題就迎刃而解了。
問題是儲存的結果值容易混淆,儲存00111001時,返回時的10進位制數,還是ASCII碼對應的字元？
來看看具體的值
root@rac1 : test 22:13:47> CREATE TABLE bittest(a bit(8));        
Query OK, 0 rows affected (0.01 sec)
root@rac1 : test 22:21:25> INSERT INTO bittest VALUES(b'00111001');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 22:28:36> INSERT INTO bittest VALUES(b'00111101');           
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 22:28:54> INSERT INTO bittest VALUES(b'00000001');       
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:11:30> insert into bittest values(b'00111010');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:12:24> insert into bittest values(b'00000000');      
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:16:42> 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> 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     |
+------+------+--------+--------+--------+
5 rows in set (0.00 sec)
模擬線上環境對錶bittest 加上索引：
root@rac1 : test 22:30:13> 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> select * from bittest where a='0';                     
Empty set (0.00 sec)  ---結果集為空。
檢視執行計劃，使用了索引。
root@rac1 : test 20:55:17> 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 |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
強制不走索引的話，結果集含有記錄：
root@rac1 : test 20:55:25> select * from bittest ignore index (ind_a) where a='0';
+------+
| a    |
+------+
|      |
+------+
1 row in set (0.00 sec)
下面我們檢視一下where 條件的 布林值：
root@rac1 : test 21:00:11> select b'0'=0;  
+--------+
| b'0'=0 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
root@rac1 : test 21:00:22> select b'0'='0';
+----------+
| b'0'='0' |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
bit 型別的 b'0'==0,b'0'!='0' ,哪個值 等於'0'? 答案是ascii 值為48的
root@rac1 : test 21:01:18> select b'110000'='0';
+---------------+
| b'110000'='0' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
root@rac1 : test 21:01:28> select b'110000'+0;
+-------------+
| b'110000'+0 |
+-------------+
|          48 |
+-------------+
1 row in set (0.00 sec)
如果使用 a=0 作為條件的話，依然有結果
root@rac1 : test 21:00:25> 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 |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
root@rac1 : test 21:00:35> select * from bittest where a=0;   
+------+
| a    |
+------+
|      |
+------+
1 row in set (0.00 sec)
所以，可以做一個小結：
對於bit 型別的數值
不使用使用索引，mysql 檢索bit的值是不管是數值還是字元，mysql會對where 條件進行型別轉化,將字元轉換為數值，並比較數值對應的ascii碼，如果值為1，則返回結果，否則，結果為空。
root@rac1 : test 21:08:37> select * from bittest ignore index (ind_a) where a='48';
+------+
| a    |
+------+
| 0    |
+------+
1 row in set (0.00 sec)
將字串'48'轉化為數值的48也即b'110000'，和字元'0'的ascii 碼做比較 
root@rac1 : test 21:08:48> select * from bittest ignore index (ind_a) where a=48; 
+------+
| a    |
+------+
| 0    |
+------+
1 row in set (0.00 sec)
使用索引時:bit位在索引中儲存的格式是bin型別，即'0'/'1'bit位，且不會對字串進行數值轉換。
root@rac1 : test 21:08:58> select * from bittest  where a=57;
+------+
| a    |
+------+
| 9    |
+------+
1 row in set (0.00 sec)
字元'9'對應的ASCII碼程式碼為57 而不是字串'57'
root@rac1 : test 21:09:10> select * from bittest  where a='57';
Empty set (0.01 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-743642/，如需轉載，請註明出處，否則將追究法律責任。

相關文章