mysql位移运算解决业务数据标签属性分组的问题

发布时间：2021-08-13作者：laosun阅读（1209）

需求

有一个用户表（t_user）,在业务中我们通过对用户数据建立用户标签的形式，实现精准描绘用户。这里的标签就是用户在业务环境中的本身属性。

例：

用户属性：性别，是否持卡，是否活跃，是否单身。4个属性（当然，属性可能会很多，4个只是例子）。
需求1：期望查所有活跃用户；
需求2：期望查询别为男，持卡，活跃的所有用户；

设计

之前思考了很多种方案，但每一种都觉得不好。如最容易想到的是建一个属性表（或在当前表加字段）来关联，这样是有问题的，如果属性扩展了，表结构需要变动，程序需要相应调整，并且数据可能占用更多的磁盘空间。基于redis的bitmap有感而发，只需要在当前表增加一个字段，“标签”列"tag (bigint)"，该字段以整数的形式存储二进制数据，bigint对应java中的Long类型64位，所以可以最多支持64个用户标签。

如：
（二进制4位）1101，
（第四位1）性别：男，
（第三位1）是否持卡：是，
（第二位0）是否活跃：否，
（第一位1）是否单身：是。

使用按位与（&）运算匹配具体的某一位。

基本思路有了，接下来就是看是否可行。

需求DEMO实现

表：t_user

CREATE TABLE `t_user_demo` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入数据：

INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '甲', 15);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '乙', 14);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '丙', 13);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '丁', 11);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '戊', 7);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '己', 1);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '庚', 2);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '辛', 4);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '任', 8);
INSERT INTO `user_group`.`t_user_demo`(`id`, `name`, `tag`) VALUES (0, '癸', 9);

需求1的sql实现：

-- 查所有活跃用户,活跃用户是第idx=2位
-- 使用 & 运算 (tag & 0010) == 0010  =>  tag & (1 << (2-1))
set @idx:=2;	
select id,name,tag,bin(tag) tag_bin from t_user_demo where (tag & (1 << (@idx-1))) = (1 << (@idx-1));

结果：

返回结果没有问题，符合需求。tag_bin字段是十进制转二进制后的形式，方便观察。

需求2的sql实现：

-- 多个位的查询，比如：性别：男（第四位1），持卡（第三位1），活跃（第二位1），
-- 第四位：1 << (4-1)  第三位 1 << (3-1) 第二位：1 << (2-1)
-- (1 << (4-1)) + (1 << (3-1)) + (1 << (2-1)) = 1000 + 100 + 10 = 1110
select id,name,tag,bin(tag) tag_bin from t_user_demo 
where (tag & ((1 << (4-1)) + (1 << (3-1)) + (1 << (2-1)))) = ((1 << (4-1)) + (1 << (3-1)) + (1 << (2-1)));

结果：