巧用mysql位运算解决多选值存储问题

一．问题场景

工作中经常遇到多选值存储问题，例如：用户有多种认证方式，密码认证、短信认证、扫码认证等，一个用户可能只开启了其中某几种认证方式。

二. 场景分析

比较容易理解的两种实现方式，多字段存储、单个字段拼接存储。

1.多字段存储

每种认证方式用一个字段存储，0表示未开启，1表示已开启。
缺点：每增加一种认证方式都需要添加一个表字段，扩展性差。

2.单字段拼接

单字段存储，已开启的认证方式用逗号(或其他分割符)拼接。例如：开始了密码认证和短信认证，则存储为：密码认证,短信认证。
缺点：不利于查询，需要使用模糊查询，搞不好会影响性能。

三．巧用位运算

1.概述

参考Linux权限控制思路，将每种认证方式对应到二进制位中，例如：密码认证–10000000，短信认证–01000000，扫码认证–00100000，然后将其转换成10进制，密码认证–1， 短信认证–2，扫码认证–4。Mysql存储时使用单字段(auth_method)int类型存储，如果开启了多种认证方式将多种认证方式对应的枚举数值相加后存储，例如开启了密码认证和短信认证，则存储为3(1+2)。

2.sql查询

## 例1：判断用户是否开启了密码认证--1 (满足条件时返回查询结果，没有满足条件时返回为空)
Select * from user where auth_method & 1;

## 例2：判断用户是否开启了密码认证 + 短信认证 (1+2)
Select * from user where auth_method & 3;

## 例2：判断用户是否开启了密码认证 + 短信认证 + 扫码认证 (1+2+4)
Select * from user where auth_method & 7;

3.Java解析与计算

import com.google.common.collect.Lists;
import lombok.Getter;
import org.springframework.util.CollectionUtils;

import java.util.Arrays;
import java.util.List;

@Getter
public enum AuthMethodEnum {

    PASSWORD(1, "密码认证"),
    SMS(2, "短信认证"),
    QR_CODE(4, "扫码认证");

    private Integer method;

    private String name;

    AuthMethodEnum(Integer method, String name) {
        this.method = method;
        this.name = name;
    }

    /**
     * 将mysql存储值解析成多种认证方式
     * @param method
     * @return
     */
    public static List<Integer> parseAuthMethod(Integer method) {
        List<Integer> list = Lists.newArrayList();
        if (null == method) {
            return list;
        }
        AuthMethodEnum[] arr = AuthMethodEnum.values();
        // 需要先将method从大到小排序
        Arrays.sort(arr, (o1, o2) -> {
            if (o1.method > o2.method) {
                return -1;
            } else {
                return 0;
            }
        });
        for (AuthMethodEnum e : arr) {
            if (method >= e.method) {
                list.add(e.method);
                method = method - e.method;
            }
        }
        return list;
    }

    /**
     * 将任意种认证方式计算后得到存储值
     * @param methods
     * @return
     */
    public static Integer calculateAuthMethod(List<Integer> methods) {
        if (CollectionUtils.isEmpty(methods)) {
            return 0;
        }
        return methods.stream().mapToInt(p -> p).sum();
    }

    public static void main(String[] args) {
        System.out.println(parseAuthMethod(8));
    }
}

4.总结

通过位运算的转换，实现了单个字段存储不同的认证状态，增加一个新的认证方式时只需要添加一个枚举值。不仅可以节省存储空间，大大增加了可扩展性，对性能几乎没有影响。