对一个表进行创建索引后,开发报告说之前可以查询出结果的查询在创建索引之后查询不到结果:
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 |
|
