mysql 中的bit数据类型

本文探讨了MySQL中BIT类型字段在创建索引后导致查询结果异常的现象,通过实验对比了带索引与不带索引时的不同表现,并详细解析了BIT类型在MySQL中的存储方式及其对查询的影响。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

http://blog.itpub.net/22664653/viewspace-743642/


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

<think>嗯,用户想了解MySQL中的bit数据类型的使用和特性。首先,我需要回忆一下MySQL的官方文档和相关资料。记得bit类型是用来存储位字段的,可以指定长度,比如bit(1)就是一个二进制位,通常用来表示布尔值。但用户可能想知道更详细的用法,比如如何插入数据、查询时的处理,以及和其他数据类型的区别。 接下来,我需要参考用户提供的引用内容。引用[2]提到bit(M)可以存储M位的二进制数据,M的范围是1-64。这说明bit类型不仅可以表示布尔值,还能存储更长的二进制数据,比如权限位之类的。另外,引用[2]还提到可以使用位操作来查询或更新状态,这可能是bit类型的一个优势。 然后,引用[1]提到MySQL中的数值类型,比如int的有符号和无符号,还有zerofill属性。虽然这和bit类型没有直接关系,但可能需要注意bit类型是否支持类似的属性,比如是否能用zerofill,但根据用户的问题,可能不需要涉及这些。 引用[3]和[4]讨论了char和varchar的区别,可能和bit类型无关,但需要确认bit类型在存储和查询时的效率如何,是否有类似定长类型的优势。不过用户的问题主要集中在bit类型本身,所以这部分可能不需要深入。 用户可能的应用场景是什么?比如存储多个开关状态,用bit(M)来节省空间。比如M=8可以存8个布尔值,这样比用8个tinyint(1)更高效。但需要确认MySQL的存储机制,bit类型是否真的是按位存储,还是会有其他开销。 另外,插入数据时,用户可能用b'101'这样的二进制字面量,或者用整数,比如插入5相当于二进制101。但要注意当插入的值超过bit(M)的范围时会发生什么,比如截断还是报错。例如,bit(3)的最大值是7,如果插入8会怎么样?可能需要测试或查文档。 查询的时候,直接select bit类型的字段会显示二进制形式吗?比如bit(3)存储的是5,select会显示b'101'吗?还是显示成其他格式?根据引用[3],可能需要使用函数如BIN()或HEX()来转换,否则可能显示为乱码或者不可读的形式。 另外,关于存储空间,bit(M)的存储空间是大约(M+7)/8字节,比如M=8需要1字节,M=9需要2字节。这点需要确认是否正确,因为不同的数据库可能有不同的存储方式。 还有,bit类型的默认值如何设置?是否可以用二进制字面量或者整数?比如DEFAULT b'1'或者DEFAULT 1。 可能用户会问到bit和tinyint(1)的区别,特别是当存储布尔值时,两者有什么优劣。比如存储空间、查询效率、可读性等方面。需要比较两者,指出bit类型更节省空间,但可能在可读性和操作上不如tinyint方便。 还需要注意版本兼容性问题,比如不同MySQL版本对bit类型的处理是否有变化,比如旧版本是否支持bit类型,或者是否有不同的行为。 可能用户在使用bit类型时遇到的常见问题,比如插入数据时的格式错误,查询时显示不正确,或者应用程序中处理bit类型的问题。例如,某些ORM框架可能对bit类型的支持不够好,导致需要手动处理转换。 此外,性能方面,使用bit类型进行位运算是否高效,比如在where子句中使用位掩码来查询特定位的状态,这样的查询能否利用索引,或者是否会导致全表扫描。 总结下来,我需要组织回答的结构,先介绍bit类型的定义和基本特性,然后讨论如何插入和查询数据,存储机制,应用场景,与其他数据类型的对比,以及常见问题和注意事项。同时,引用相关的资料来支持这些点,比如引用[2]提到的存储多个状态和位操作的优势,引用[3]关于显示需要转换的问题。</think>### MySQL Bit数据类型详解 #### 一、Bit类型定义与基本特性 1. **存储方式** MySQL的`BIT(M)`类型用于存储二进制位数据,其中$M$表示位数,取值范围为$1 \leq M \leq 64$。例如: - `BIT(1)`:存储单个二进制位(0或1),常用于布尔状态。 - `BIT(8)`:存储8位二进制数据(如`b'10101010'`),适用于多状态组合[^2]。 2. **存储空间** 存储空间计算公式为:$\lceil M/8 \rceil$字节。例如: - `BIT(1)`占用1字节(实际最小分配单位); - `BIT(9)`占用2字节。 #### 二、数据操作与查询 1. **插入数据** - 使用二进制字面量:`INSERT INTO table (flags) VALUES (b'101');` - 使用十进制整数:`INSERT INTO table (flags) VALUES (5);`(等价于`b'101'`) - 超出范围的数值会被截断,例如向`BIT(2)`插入`b'111'`会存储为`b'11'`。 2. **查询与显示** 直接查询时可能显示为二进制字符串(如`0x05`)或乱码,需用函数转换: ```sql SELECT flags, BIN(flags), HEX(flags) FROM table; -- 输出二进制或十六进制格式 ``` #### 三、应用场景与优化 1. **多状态存储** 一个`BIT(64)`字段可存储64个独立状态,例如用户权限、开关配置等。通过位运算操作特定状态: ```sql -- 检查第3位是否为1 SELECT * FROM table WHERE flags & b'100'; -- 设置第2位为1 UPDATE table SET flags = flags | b'10'; ``` 2. **对比其他类型** | 类型 | 存储空间 | 适用场景 | 可读性 | |-------------|-------|------------------------|-----| | `BIT(M)` | 紧凑 | 多状态组合、布尔值 | 低 | | `TINYINT(1)`| 1字节 | 简单布尔值(如`0/1`) | 高 | | `VARCHAR` | 变长 | 非二进制数据(如状态描述字符串) | 最高 | #### 四、注意事项 1. **默认值与约束** - 支持默认值设置:`flags BIT(4) DEFAULT b'0011'` - 允许`NOT NULL`约束,但不支持`AUTO_INCREMENT`。 2. **兼容性问题** - 部分ORM框架(如早期Hibernate)可能无法直接解析`BIT`类型,需手动映射为整数或布尔类型。 - 迁移到其他数据库(如PostgreSQL)时,需注意`BIT`类型的语义差异。 #### 五、性能优化建议 - **索引使用**:直接对`BIT`字段创建索引效率较低,建议拆分常用状态到独立字段。 - **位运算优化**:复杂位操作(如`~`、`<<`)可能影响查询性能,需结合业务场景测试。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值