MySQL中Bit数据类型使用

Mysql Bit字段类型用来存储二进制的位值，Bit(M)代表可存储M位的二进制数据，M取值为区间为1-64。数据库Bit类型常用来存储Boolean类型的状态，也就是可以使用一个字段来存最多64个状态，如果你的系统状态够多或者将来可能会有扩展状态，那推荐使用这个类型进行处理，查询或更新某个状态时按位操作即可。

举个例子：某个系统需要保存对用户发送短信的状态,有10几种不同类型的短信,每种都要标记发过没有，这时候用bit类型就很方便。

1、准备测试表

CREATETABLE`bit_demo`(`id`intNOTNULL,`name`varchar(100)DEFAULTNULL,`flag`bit(3)DEFAULTNULLCOMMENT'从低到高位依次为移动，电信，联通',PRIMARYKEY(`id`),KEY`idx_bit`(`flag`))

为了将来可扩展，我们一般先从低位开始使用，此处我们设计三个位，从低到高位依次为移动，电信，联通短信渠道是否完成发送，各位值上１表示true，0表示false。3个位，一共8种情况，二进制值范围：000->111，十进制范围：0->7，如下表格：
二进制十进制说明0000所有渠道都未发送0011移动渠道已发送0102电信渠道已发送0113移动、电信渠道已发送1004联通渠道已发送1015移动、联通渠道已发送1106联通、电信渠道已发送1117移动、电信、联通渠道已发送
2、如何写入bit数据

位值字面值用

b

或者

0b

做前缀，后接以

0

跟

1

组成的二进制数字。其中

0b

是区分大小写的，

0B

则会报错。

合法的 Bit-value：

1. b’01’
2. B’01’
3. 0b01

非法的 Bit-value：

1. b’2’ (2 不是二进制数值, 必须为 0 或 1)
2. 0B01 (0B 必须是小写 0b)

以下三条sql插入了相同位值：

insertinto bit_demo(`id`,`name`,`flag`)values(1,'张三',3);insertinto bit_demo(`id`,`name`,`flag`)values(2,'李四',b'011');insertinto bit_demo(`id`,`name`,`flag`)values(3,'三五',0b011);

3、bit数据查询展示

位值是作为二进制返回的，输出到 MySQL Client 可能会显示不出来**(有些client桌面工具默认展示10进制)，**

如果要转换为可打印的字符，可以使用内建函数

BIN()

或者

HEX(

)来进行查询，如下内置函数：

1. flag+0 10进制
2. BIN(flag) 二进制
3. OCT(flag)八进制
4. HEX(flag) 十六进制

内置函数使用案例如下：

SELECT name,flag+0, BIN(flag), OCT(flag), HEX(flag)FROM bit_demo

高位为0，转换函数不会显示，从上图业务数据看出，所有用户已发送移动和电信渠道的短信．

4、修改指定位状态

1. 如果张三用户已发送联通渠道短信，如何update数据？
2. 如果张三用户取消电信渠道短信，如何update数据？
3. 先把张三数据查出来，变更数据，再整体写回去？

正确姿势应该如下：

4.1、使用或运算写１值

说明：运用任何值和0做或运算，不改变原值特性，例如将011的最高位改为1的运算如下：

// 011|100=111update bit_demo set flag=flag|100where id=1

查询结果：

从上图业务数据看出，张三用户所有渠道短信已发送．

4.2、使用与运算写0值

说明：运用任何值和1做与运算，不改变原值特性，例如将111的最高位改为0的运算如下：

// 111&011=011update bit_demo set flag=flag&011where id=1

查询结果：

从上图业务数据看出，张三用户联通渠道短信还未发送．

4.3、使用异或运算写0或1值

说明：用异或运算修改，只会在原值基础上进行变更，最终值有可能是0也有可能是1

法则回顾：异或也叫半加运算，其运算法则相当于不带进位的二进制加法，例子：

110^100=010

update bit_demo set flag=flag^b'100'where id=1;

5、如何根据某位状态查询

1. 如何查询所有已发送过电信渠道短信的数据？
2. 如何查询所有未发送电信渠道短信的数据？

原理：将数据库值先作位运算，再作对比
任意数据xxx和010作&运算，都等于0x0格式

# 查询已发送电信渠道数据SELECT id,name,flag+0, BIN(flag)FROM bit_demo where flag&010=b'010'
或
SELECT id,name,flag+0, BIN(flag)FROM bit_demo where flag&010=2

# 查询未送电信渠道数据SELECT id,name,flag+0, BIN(flag)FROM bit_demo where flag&010=b'000'
或
SELECT id,name,flag+0, BIN(flag)FROM bit_demo where flag&010=0

完！