数据库表字段应用位运算的设计

数据库表字段应用位运算的设计

常用的位运算符

位运算符是二目运算符

符号	描述	运算规则
&	(按位)与	两个位都为1时，结果才为1
|	(按位)或	两个位都为0时，结果才为0
^	(按位)异或	两个位相同为0，相异为1
~	(按位)取反	0变1，1变0
<<	(按位)左移	各二进位全部左移若干位，高位丢弃，低位补0
>>	(按位)右移	各二进位全部右移若干位，对无符号数，高位补0，有符号数，有的补符号位（算术右移），有的补0（逻辑右移）

 

类似权限等一对多的字段设计

参照 Linux chmod命令

举例说明:
  假设人员认证有实名认证、人脸认证、指纹认证、机构认证 4 种
  我们一般的做法可能有两种：一种是增加一个varchar字段，每种认证之间用一个特殊符号分隔保存，例如"1,2,3,4"; 另一种方法是建立一个关系表
  第一种方法查询极不方便，例如 查询有人脸和机构认证的人员, 第二种方法如果人员还有其他属性，就要根据每个属性去建立关系表，增加了复杂度。

我们可以这么做：

CREATE TABLE IF NOT EXISTS t_test( 
  Id serial,
  Name varchar(10) NOT NULL, -- 员工
  Role smallint DEFAULT 0, -- 认证 1实名认证 2人脸认证 4指纹认证 8机构认证
  CreatedAt timestamptz(6) DEFAULT CURRENT_TIMESTAMP,
  UpdatedAt timestamptz(6) DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (Id)
);

首先我们用 1、2、4、8、16、32、64...这种2 ⁿ的数字 让每个数值代表一个类型
Role 字段存的是类型数值之和，如 员工具有人脸认证和指纹认证 则Role的值为6 (2+4 = 6 )

经过如上方法计算的Role值后，假设有如下数据

id	role	说明
1	3	1+2
2	14	2+4+8
3	0	无认证
4	5	1+4
5	6	2+4
6	7	1+2+4

从表中可以看到Role值成了3、14、0、5、6、7，这些数值如何供我们查询使用呢？
我们此时就可以使用位运算来解决这个问题
多个和单个条件都适用

1. 当我们需要查询 具有 实名认证(1) 的员工时：

   SELECT * FROM t_test WHERE Role&1=1  
   

2. 当我们需要查询 具有 实名认证(1) 和 指纹认证(4) 的员工时：

   SELECT * FROM t_test WHERE Role&5=5  
   

3. 当我们需要设置 员工 具有实名认证(1) 和 指纹认证(4) 的员工时：

   UPDATE t_test SET Role=Role|5  WHERE Id=1  
   

4. 当我们需要设置 员工 不具有实名认证(1) 和 指纹认证(4) 的员工时：
   此时要注意 该员工一定是具有 1 和 4 认证的才能使用该方法去设置不具有该认证

   UPDATE t_test SET Role=Role^5  WHERE Id=1 AND Role&5=5
   

 

类似状态等一对一的字段设计

举例说明:
  假设人员状态有上班、出差、休息、请假 4 种
  如果使用位运算的方式 我们同样可以设计成:

CREATE TABLE IF NOT EXISTS t_test( 
  Id serial,
  Name varchar(10) NOT NULL, -- 员工
  Status smallint DEFAULT 0, -- 状态 1上班 2出差 4休息 8请假
  CreatedAt timestamptz(6) DEFAULT CURRENT_TIMESTAMP,
  UpdatedAt timestamptz(6) DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (Id)
);

1. 当我们需要查询 上班和请假 的员工时：

   SELECT * FROM t_test WHERE Status&9>0  
   

本文提供了一些位运算的设计思路，具体实现肯定需要参照实际需求

 

• 位运算