mysql位运算在存储一对多关系中的应用

Posted by admin on 2018, May 23

mysql位运算在存储一对多关系中的应用

位运算

位运算符有很多,例如与(&)、非(~)、或(|)、异或(^)、移位(<<和>>)等。这些运算符在日常编码中很少会用到,但面对下面的问题会表现出位运算的应用场景,今天要说的就是这些,基础部分需要自行查询。

场景

电商系统会产生很多很多的订单,而订单分很多类型,为了说明方便,只列举如下几种:

  • 普通订单
  • 建材订单
  • 一元夺宝订单
  • 订单

普通情况就是用4个值比如1,2,3,4来代表类型在mysql中存储,但当你的类型越来越复杂,比如这个订单是建材订单,也是安装订单,如果开始设计不好就不那么好存储,下面就是替代方案的开始。

建表如下:

CREATE TABLE `do_you_copy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `comment` varchar(32) DEFAULT NULL COMMENT '备注',
  `type` bit(32) NOT NULL COMMENT '比特表示的类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里关键就是 type 字段了,32个比特可以存储32个一对多类型,每一个位代表一种,0表示不是此类型,1表示是此类型,忽略高位下面列举几个例子:

bit二进制 (十进制) 普通 建材 一元 安装 备注
0001    (1) 0 0 0 1 安装订单
0010    (2) 0 0 1 0 一元
0100    (4) 0 1 0 0 建材
1000    (8) 1 0 0 0 普通
0101    (5) 0 1 0 1 安装+建材
0111    (7) 0 1 1 1 安装+建材+一元

如表格中列举,很容易理解了,在某一位上,如果是1代表,具备此订单属性,0则代表不具备,多个位上是1的情况即此订单满足多种属性了,也就是所谓的一对多。

sql应用

设计好之后sql也要跟着变成位运算的了,mysql直接支持位运算,所以我们直接以sql语句为例介绍,只需要用十进制代表的数字进行位运算就可以了。

1、 查询单纯是安装订单

#查到一条数据
select * from `do_you_copy` where (type) = 1;

2、 查询包含安装订单类型的所有

#查到三条数据
select * from `do_you_copy` where (type & 1) = 1;

3、 查询包含安装或者建材订单类型的所有(只需查两个的和,即1+4=5)

#查到两条数据
select * from `do_you_copy` where (type & 5) = 5;

4、 查询既是一元也是建材订单类型的

#查到一条数据
select * from `do_you_copy` where (type & 4) = 4 and (type & 2) = 2;

5、 查询不包含普通订单类型的

#查到一条数据
select * from `do_you_copy` where (type & 1) = 0;

以上大部分场景就覆盖到了。

缺点

位运算就无法使用到索引了,应用的时候还是需要多斟酌。