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

发布时间:2021-08-13作者:laosun阅读(1057)

    需求

    有一个用户表(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));

    结果:

    image.png

    返回结果没有问题,符合需求。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)));

    结果:

    image.png

    返回结果没有问题,符合需求。


    按位修改标签时,同样也是使用&运算,如:第三位由1修改为0,1101 & 1011 = 1001。



0 +1

版权声明

 数据库  mysql  sql

 请文明留言

0 条评论