数据库单字段存储多个标签(位移操作)

在上周的开发中看到了这样的一个问题 一个员工有多个资质
如: 资质 1=监理,2=安全员,4=项目经理,8=技术员,16=特种作业人员,32=劳务人员 (是2的倍数 就是二进制的10进制的展示 )
李白 1=监理 4=项目经理
一般我们的解决方式有以下两种

1. 在员工表这里扩展字段
2. 配置资质表 和 一个关系表 然后链表查询

方案1 没有使用的原因 是因为这个资质可能会频繁的增加 修改 就要频繁的改表结构了 数据量一大 改表结构就不是一个好的操作
方法2 这里没有使用的原因 就是这个用工查询本身就关联表过多了 要不就要2次请求数据库

演示

这里写了一个Demo 提供一下思路
首先准备一个员工表

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `empno` int(11) DEFAULT NULL,
  `del_flag` int(11) DEFAULT '0',
  `record_version` int(255) DEFAULT '1',
  `qualification` int(11) NOT NULL COMMENT '1=监理2=安全员4=项目经理8=技术员16=特种作业人员32=劳务人员',
  PRIMARY KEY (`id`),
  KEY `i` (`age`,`deptid`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4;

使用这个qualification 字段来表示 这个员工的资质

插入

比如 上述例子 李白 拥有 1=监理 4=项目经理 这两个资质 插入sql 如下:

-- 在Java 中资质可以是多个 使用 mybatis的 foreach
insert into emp (name,qualification) values ('李白',  1<<0  | 1<<4) 

查询

数据:

-- 1=监理,2=安全员,4=项目经理,8=技术员,16=特种作业人员
-- 在Java 中资质可以是多个 使用 mybatis的 foreach
select name,
(attrbute & (1<<0) !=0) as 监理,
(attrbute & (1<<1) !=0) as 安全员,
(attrbute & (1<<2) !=0) as 项目经理,
(attrbute & (1<<3) !=0) as 技术员,
(attrbute & (1<<4) !=0) as 特种作业人员,
(attrbute & (1<<5) !=0) as 劳务人员
from emp
where
attrbute != 0

结果

统计

select 
sum((qualification & (1<<0) !=0)) as 监理,
sum((qualification & (1<<1) !=0)) as 安全员,
sum((qualification & (1<<2) !=0)) as 项目经理,
sum((qualification & (1<<3) !=0)) as 技术员,
sum((qualification & (1<<4) !=0)) as 特种作业人员,
sum((qualification & (1<<5) !=0)) as 劳务人员
from emp
where
qualification != 0

条件查询

select *
from emp
where
qualification != 0
# 仅查询监理 
and  qualification & 1 !=0
--------------------------------------------
select *
from emp
where
qualification != 0
# 仅查询监理 并且是 项目经理  
and qualification & ( 1<<0  | 1<<4) = (1<<0  | 1<<4)
---------------------------------------
select *
from emp
where
qualification != 0
# 仅查询监理 或 项目经理  
and qualification & ( 1<<0  | 1<<4) != 0

修改 减少标签

-- 在Java 中资质可以是多个 使用 mybatis的 foreach
update emp set  qualification = qualification ^ (1<<4) where  id  = 19 

查看结果

select id , name,(qualification & (1<<0) !=0) as 监理,
(qualification & (1<<1) !=0) as 安全员,
(qualification & (1<<2) !=0) as 项目经理,
(qualification & (1<<3) !=0) as 技术员,
(qualification & (1<<4) !=0) as 特种作业人员,
(qualification & (1<<5) !=0) as 劳务人员 from emp where  id  = 19 

修改 增加标签

-- 在Java 中资质可以是多个 使用 mybatis的 foreach
update emp set  qualification = qualification | (1<<4) where  id  = 19 

查看结果

select id , name,(qualification & (1<<0) !=0) as 监理,
(qualification & (1<<1) !=0) as 安全员,
(qualification & (1<<2) !=0) as 项目经理,
(qualification & (1<<3) !=0) as 技术员,
(qualification & (1<<4) !=0) as 特种作业人员,
(qualification & (1<<5) !=0) as 劳务人员 from emp where  id  = 19