【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;

+------+

| 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)

时间： 2024-09-14 03:44:38

【MySQL】bit 类型引发的故事的相关文章

详解mysql索引总结----mysql索引类型以及创建_Mysql

关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引.  一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万

MYSQL列类型参考

mysql|参考    本附录介绍MySQL提供的每种列类型.关于利用每种类型的详细说明请参阅第2 章.除非另有说明,否则所列出的类型早在MySQL3.21.0 中就已经有了.    按下列约定给出类型名说明:    方括号( [ ]) 可选信息.    M 最大显示宽度.除非另有说明,否则M 应该是一个1到255 之间的整数.    D 有小数部分的类型的小数位数.D 为一个0 到30 之间的整数.D 应该小于等于M - 2.否则,M 的值将调整为D + 2.    在ODBC 术语中,M 和

移植-oracle timestamp（6） 对应mysql什么类型？

问题描述 oracle timestamp(6) 对应mysql什么类型? 1C 请教大家一个问题,关于数据库移植方面的内容.oracle到mysql移植在oracle中存在timestamp(6)类型,可以保存6位秒信息.那么在mysql有什么类型可以替换timestamp(6)呢?谢谢大家! 解决方案 mysql也有时间戳,参考http://database.51cto.com/art/200905/124240.htm 解决方案二: 没有直接对应的,可以转换一下http://databas

mysql char类型主键做查询、更新条件时遇见怪事了，求大神解答

问题描述 mysql char类型主键做查询.更新条件时遇见怪事了,求大神解答 mysql> desc card_info; +-------------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------+------+-----+-------------------+-------+ |

mysql-Linux 下 Mysql byte类型的问题

问题描述 Linux 下 Mysql byte类型的问题 最近遇到一个问题,数据库是Linux版的Mysql数据库, 在访问数据库做查询操作时, select substr(xxx,5,7) as time form xxx 返回来的结果集time变成了Byte类型..这是怎么回事,是MySql设置的问题吗?求解答 解决方案 byte类型的问题 解决方案二: http://www.cnblogs.com/zdz8207/p/3765073.html 应该是字符串

excel表内的时间导入到mysql datetime类型的数据是0

问题描述 excel表内的时间导入到mysql datetime类型的数据是0 excel表内的时间导入到mysql datetime类型的数据是0000-00-00 00:00:00,该怎么处理才好 工具是navicat for mysql 解决方案 你先在excel里把时间格式调整一下吧. 或者你先把excel里的数据导入到 sqlserver里, 毕竟他们是一家母司的东西, 兼容性比较强一点. 导入到sqlserver里了, 再导到mysql就不再是什么难事.

图片存储-hibernate mysql blob类型 图文存储怎么解决

问题描述 hibernate mysql blob类型 图文存储怎么解决 我想做一个文章存取得功能,即一片文章的内容有文字和图片,均存在实类Article中的content中,在Java是String类型,在mysql中是blob类型. jsp页面有一个form表(含有编辑器)是用来发表文章的,怎么从form表中,取得content的参数,然后转化成blob类型存进数据库,又怎么从数据库取出在转化成String在页面上显示呢???求大神指教怎么存取成功? 新手一枚,请大家多多包涵! 解决方案 是

MySQL索引类型总结和使用技巧以及注意事项_Mysql

在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

MySQL表类型和存储引擎版本不一致解决方法

使用的是老版本的mysql客户端Navicate 8 ,mysql 服务端用的是mysql5.6的版本,在修改版本引擎的时候出现版本不对; mysql error 'TYPE=MyISAM' 解决办法: Replace TYPE=MyISAM with ENGINE=MyISAM The problem was "TYPE=MyISAM" which should be "ENGINE=MyISAM" as per MySQL version updates – a