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';
+----------+
| 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;
+------+
mysql创建表数据类型| 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)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论