使用位运算，处理数据库中的"多选状态标识"

• 引言【摘自其他文章】：  
  

   

    最近在对公司以前的一个项目进行调整时发现，数据库中有很多表示“多选状态标识”的字段。“多选状态标识”可能描述的并不十分准确，在这里用我们项目中的几个例子进行说明一下。

     例一：表示某个商家是否支持多种会员卡打折（如有金卡、银卡、其他卡等），项目中的以往的做法是：在每条商家记录中为每种会员卡建立一个标志位字段。如图：

 

     其中蓝色区域的三个整形字段分别表示三种会员卡。当值为“1”时表示当前商家支持这种会员卡打折，反之“0”则表示不支持。

 

     例二：表示系统字典表中某种类型方式，会在哪个功能模块中调用。如某种“支付方式”可能在“收银模块”中会用到，在“结算模块”中也会用到。如图：

     用多字段来表示“多选标识”存在一定的缺点：首先这种设置方式很明显不符合数据库设计第一范式，增加了数据冗余和存储空间。再者，当业务发生变化时，不利于灵活调整。比如，增加了一种新的会员卡类型时，需要在数据表中增加一个新的字段，以适应需求的变化。

 

     因此，我们在重新审视数据库设计时，我的一位同事提出了一种代替方式：将多个状态标识字段合并成一个字段，并把这个字段改成字符串型，对多选状态值以字符串数组的方式保存（一个以逗号分隔的字符串：“1,2,3”）。表的结构变成如下：

    “MEMBERCARD”字段中，当存在“1”时表示支持金卡打折，“2”时表示支持银卡打折，“3”表示支持其他卡打折。

     这样调整的好处，不仅消除相同字段的冗余，而且当增加新的会员卡类别时，不需增加新的字段。但带来新的问题：在数据查询时，需要对字符串进行分隔。并且字符串类型的字段在查询效率和存储空间上不如整型字段。

 

     总的来说，上面调整的思路是正确的，但不够自然。我后来考虑了一下，觉得可以用“位”来解决这个问题：二进制的“位”本来就有表示状态的作用。可以用下面各个位来分别表示不同种类的会员卡打折支持：

     这样，“MEMBERCARD”字段仍采用整型。当某个商家支持金卡打折时，则保存“1（0001）”，支持银卡时，则保存“2（0010）”，两种都支持，则保存“3（0011）”。其他类似。表结构如图：

我们在编写SQL语句时，只需要通过“位”的与运算，就能简单的查询出想要数据：

[java]  view plain  copy

1. //查询支持金卡打折的商家信息：  
2. select * from factory where MEMBERCARD & b'0001'  
3. 或者：  
4. select * from factory where MEMBERCARD & 1  
5.  
6. //查询支持银卡打折的商家信息：  
7. select * from factory where MEMBERCARD & b'0010'  
8. 或者：  
9. Select * from factory where MEMBERCARD & 2  

 

     通过这样的处理方式既节省存储空间，查询时又简单方便。以上sql语句为MySQL的语法，其他数据库方法类似。并且“b'0010'”二进制的表示方式的语法是在5.0以后的版本才有。

以上来源自： http://blog.csdn.net/wangdejun/article/details/5692033

• 实际操作【实战一把】：
  

1. CREATE TABLE `news`(
2.  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
3.  `title` varchar(20) NOT NULL COMMENT '文章标题',
4.  `status` int(2) NOT NULL COMMENT '状态 1:是否置顶;2:是否点赞;4:是否推荐',
5.  PRIMARY KEY (`id`)
6. ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

数据表的原始数据：

1. 【更新】status =置顶;
2. update news set status = status |1 where id =1
4. 【更新】status =点赞; update news set status = status | 2 where id = 1
6. 【更新】status =推荐;update news set status = status | 4 where id = 2

1. 【选择】status =推荐;
2. select *from news where status &4=4

 

1. 【选择】status =置顶&推荐&点赞;;
2. select *from news where status &7=7
4. 【选择】status =置顶&推荐;
5. select *from news where status &3=3
7. 【选择】status =不置顶&不推荐;
8. select *from news where status &1!=1and status &4!=4

1. 【选择】status =不置顶|不点赞;
2. select *from news where status &1!=1or status &2!=2

2. 【更新某记录】status =推荐，为不推荐;
3. update news set status = status ^4 where id =1

更新前：

 

更新后：

 

 

• 后记【位运算】：
  

按位与运算

1. 按位与运算符"&"是双目运算符。
2. 其功能是参与运算的两数各对应的二进位相与。只有对应的两个二进位均为1时，结果位才为1，否则为0。
3. 参与运算的数以补码方式出现。
5. 例如：9&5可写算式如下：
6.    00001001      (9的二进制补码)
7.   &00000101    (5的二进制补码)
8.    00000001       (1的二进制补码)
9. 可见9&5=1。
11. 按位与运算通常用来对某些位清0或保留某些位。
12. 例如把a 的高八位清0，保留低八位，可作a&255运算（255的二进制数为0000000011111111）。

按位或运算

1. 按位或运算符“|”是双目运算符。其功能是参与运算的两数各对应的二进位相或。
2. 只要对应的二个二进位有一个为1时，结果位就为1。参与运算的两个数均以补码出现。
4. 例如：9|5可写算式如下：
5.    00001001
6.   |00000101
7.    00001101    (十进制为13)
8. 可见9|5=13

按位异或运算

1. 按位异或运算符“^”是双目运算符。其功能是参与运算的两数各对应的二进位相异或，当两对应的二进位相异时，结果为1。
2. 参与运算数仍以补码出现，
4. 例如9^5可写成算式如下：
5.    00001001
6.   ^00000101
7.    00001100    (十进制为12)

求反运算

1. 求反运算符~为单目运算符，具有右结合性。
2. 其功能是对参与运算的数的各二进位按位求反。
4. 例如~9的运算为：
5.    ~(0000000000001001)
6. 结果为：1111111111110110

左移运算

1. 左移运算符“<<”是双目运算符。其功能把“<<”左边的运算数的各二进位全部左移若干位，由“<<”右边的数指定移动的位数，高位丢弃，低位补0。
3. 例如：
4.    a<<4
5. 指把a的各二进位向左移动4位。
7. 如a=00000011(十进制3)，左移4位后为00110000(十进制48)。

右移运算

1. 右移运算符“>>”是双目运算符。
2. 其功能是把“>>”左边的运算数的各二进位全部右移若干位，“>>”右边的数指定移动的位数。
4. 例如：
5.    设  a=15，
6.    a>>2
7. 表示把000001111右移为00000011(十进制3)。

注意：对于有符号数，在右移时，符号位将随同移动。当为正数时，最高位补0，而为负数时，符号位为1，

        最高位是补0或是补1 取决于编译系统的规定。Turbo C和很多系统规定为补1