数据库一个字段保存多标志位

项目中有一些场景，需要设置多个标志位，比如发送提醒消息，3天后发送提醒消息，7天后发送提醒，如果每个标志位都占用一个数据库字段就显得太浪费了。所以考虑使用一个INT字段，使用其二进制位保存各个标志

原理

MySQL数据库支持二进制位操作，比如

SELECT * FROM person WHERE hobbies & 0b100

实现示例

使用二进制位保存人员的各种爱好（假设爱好的集合是优先的）

• BitFlag

/**
 * 二进制表示各种标志.
 * @author timxia 
 */
@NoArgsConstructor
public class BitFlag {
    /**
     * 最长支持的标志个数.
     */
    private static final int MAX_FLAG = 32;

    /**
     * 实际的值.
     */
    private BitSet value = new BitSet();

    public BitFlag(int value) {
        this.value = Bits.convert(value);
    }

    public void setFlag(int flagIndex) {
        if (flagIndex >= MAX_FLAG) {
            throw new IndexOutOfBoundsException();
        }
        value.set(flagIndex);
    }

    public boolean hasFlag(int flagIndex) {
        return value.get(flagIndex);
    }

    public void clearFlag(int flagIndex) {
        if (flagIndex >= MAX_FLAG) {
            throw new IndexOutOfBoundsException();
        }
        value.clear(flagIndex);
    }

    public int convertToInt() {
        if (bits.length() > Integer.SIZE) {
            throw new IllegalArgumentException("BitSet is out range of int");
        }
        int value = 0;
        for (int i = 0; i < bits.length(); ++i) {
            value += bits.get(i) ? (1 << i) : 0;
        }
        return value;
    }
}

• BitFlagHandler
  为BitFlag定义TypeHandler，实现BitFlag与数据库字段的转换

public class BitFlagHandler extends BaseTypeHandler<BitFlag> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, BitFlag bitFlag, JdbcType jdbcType) throws SQLException {
        preparedStatement.setInt(i, bitFlag.convertToInt());
    }

    @Override
    public BitFlag getNullableResult(ResultSet resultSet, String s) throws SQLException {
        int anInt = resultSet.getInt(s);
        return new BitFlag(anInt);
    }

    @Override
    public BitFlag getNullableResult(ResultSet resultSet, int i) throws SQLException {
        int anInt = resultSet.getInt(i);
        return new BitFlag(anInt);
    }

    @Override
    public BitFlag getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        int anInt = callableStatement.getInt(i);
        return new BitFlag(anInt);
    }
}

BitFlag的使用

---

• 人员类定义（Person)

/**
 * 人员类定义，其中hobbies使用{@link BitFlag}表示爱好.
 * @author tenamo
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Person {
    private Integer id;
    private String firstName;
    private String lastName;
    private Integer age;
    private BitFlag hobbies;
    private Boolean gender;
}

• SpringBootApplication

@SpringBootApplication
public class BootMybatisApplication implements ApplicationRunner {
    @Resource
    private PersonMapper personMapper;

    public static void main(String[] args) {
        SpringApplication.run(BootMybatisApplication.class, args);
    }

    @Override
    public void run(ApplicationArguments args) {
        Person tenmao = personMapper.selectById(9);

        BitFlag hobbiesInDb = tenmao.getHobbies();

        //设置Flag
        hobbiesInDb.setFlag(5);
        hobbiesInDb.clearFlag(1);

        //保存到数据库
        personMapper.updateHobbies(9, hobbiesInDb);
    }
}

• MyBatis配置(application.yml)

mybatis:
  configuration:
    map-underscore-to-camel-case: true
  type-handlers-package: com.tenmao.boot.mybatis.handler

参考

• BitSet to and from integer/long
• 数据库设计——“多选状态标识”的处理