mysql 位运算性能数据库位运算

转载

mob64ca140beea5 2023-11-03 13:25:55

文章标签 mysql 位运算性能字段位运算位运算符 文章分类 MySQL 数据库

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

常用的位运算符

位运算符是二目运算符

符号	描述	运算规则
&	(按位)与	两个位都为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) 的员工时：

SELECT * FROM t_test WHERE Role&1=1

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

SELECT * FROM t_test WHERE Role&5=5

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

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

当我们需要设置员工不具有实名认证(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)
);