关闭

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

标签: 数据库SQL位运算多选状态标识
574人阅读 评论(0) 收藏 举报
分类:
  • 引言【摘自其他文章】:   

     

     最近在对公司以前的一个项目进行调整时发现,数据库中有很多表示“多选状态标识”的字段。“多选状态标识”可能描述的并不十分准确,在这里用我们项目中的几个例子进行说明一下。
      例一:表示某个商家是否支持多种会员卡打折(如有金卡、银卡、其他卡等),项目中的以往的做法是:在每条商家记录中为每种会员卡建立一个标志位字段。如图:


 
      其中蓝色区域的三个整形字段分别表示三种会员卡。当值为“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以后的版本才有。






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

  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
  3. 【更新】status =点赞; update news set status = status | 2 where id = 1
  4.  
  5. 【更新】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
  3. 【选择】status =置顶&推荐;
  4. select * from news where status & 3 = 3
  5. 【选择】status =不置顶&不推荐;
  6. select * from news where status & 1 != 1 and status & 4 != 4

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



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


更新前:



 

更新后:




 

 

  • 后记【位运算】:

按位与运算

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

按位或运算

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


按位异或运算

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

求反运算

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

左移运算

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

右移运算

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



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

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


0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

mysql 使用位运算

如果你不知道什么是位运算的话, 那么请你先去看看基础的C语言教程吧。 与运算 a & b  , 或运算 a | b ,  异或运算 a ^ b ,或者 你也可以将 与运算理解为 + 法  例如 1|2...
  • 21aspnet
  • 21aspnet
  • 2011-09-22 16:36
  • 23021

java中通过位运算实现多个状态的判断

通过   通过 public interface LogConstants { /** * 消耗标记 */ short COST_ASSE...
  • u010746364
  • u010746364
  • 2015-12-11 08:37
  • 1923

Android多状态组合之位运算(BitMask)

熟悉的BitMaskAndroid中使用位运算来保存状态的地方很多,你一定不会陌生,layout中的类似这种:android:gravity="bottom|right" 这种一般用于表示多种状态组合...
  • zhyj9493352669
  • zhyj9493352669
  • 2016-05-25 16:01
  • 6053

利用位运算处理权限分配来优化数据库存储,并且提高运算效率

这个是临阵磨枪的,在现在做的一个OA项目中,由于多权限造成后台静态管理网页泛滥了,现在不得不改进些新的技术,又因为以前的权限表是作为管理员表的外键,给这次修改带来很大麻烦,所以想到了类似与Linux的...
  • zhx278171313
  • zhx278171313
  • 2014-03-26 23:21
  • 1409

利用位运算处理权限分配来优化数据库存储,并且提高运算效率

这个是临阵磨枪的,在现在做的一个OA项目中,由于多权限造成后台静态管理网页泛滥了,现在不得不改进些新的技术,又因为以前的权限表是作为管理员表的外键,给这次修改带来很大麻烦,所以想到了类似与Linux的...
  • zhx278171313
  • zhx278171313
  • 2014-03-26 23:21
  • 1409

mysql位运算简化简单的一对多关系

记得以前参与的一个互联网项目,用户角色比较
  • weinianjie1
  • weinianjie1
  • 2014-10-09 11:24
  • 3445

位运算的简介与实例(Mysql)

程序中的所有数在计算机内存中都是以二进制的形式储存的。位运算就是直接对整数在内存中的二进制位进行操作。 In digital computer programming, a bitwise opera...
  • sinat_26342009
  • sinat_26342009
  • 2016-07-14 10:41
  • 1419

数据库设计——“多选状态标识”的处理

最近在对公司以前的一个项目进行调整时发现,数据库中有很多表示“多选状态标识”的字段。“多选状态标识”可能描述的并不十分准确,在这里用我们项目中的几个例子进行说明一下。       例一:表示某个商家...
  • caomiao2006
  • caomiao2006
  • 2014-03-31 11:32
  • 2145

FZU1892接水管游戏-BFS加上简单的状态压缩和位运算处理

原题地址:http://acm.fzu.edu.cn/problem.php?pid=1892 Problem 1892 接水管游戏 Accept: 108    Submit: 498 Ti...
  • Lulu11235813
  • Lulu11235813
  • 2016-04-21 11:36
  • 1078

使用位运算处理权限问题

在实际应用中可以做用户权限的应用 我这里说到的权限管理办法是一个普遍采用的方法,主要是使用到”位运行符”操作,& 位与运算符、| 位或运行符。参与运算的如果是10进制数,则会被转换至2进制数参与运算...
  • wepe12
  • wepe12
  • 2017-05-08 18:41
  • 7473
    个人资料
    • 访问:49832次
    • 积分:1894
    • 等级:
    • 排名:千里之外
    • 原创:120篇
    • 转载:47篇
    • 译文:3篇
    • 评论:2条
    博客专栏