mysql 多字段分类汇总 mysql多选字段

✨这里是第七人格的博客。小七，欢迎您的到来~✨

🍅系列专栏:【架构思想】🍅

✈️本篇内容:MySQL位运算解决多选值存储问题✈️

🍱本篇收录完整代码地址：无🍱

楔子

小七新入某司，习熟其代码之时，观一SQL，不禁心生迷惑，遂询作者（00后之小邬同学），了其思路，不甚感概，故作此文！

什么是多选值存储问题

工作中经常遇到多选值存储问题，比如某个配置项是星期一到星期天的任意组合。再比如用户修改密码的方式有邮箱验证、手机短信验证、回答密保问题等等，一个用户有可能只开启了其中某几种方式。

多选值存储问题常见解决方案

方案一：多字段存储

一个值对应一个字段。

星期一	星期二	星期三	星期四	星期五	星期六	星期日
0	1	0	1	0	1	0

方案二：单字段拼接

单字段存储数据，多个值用分隔符区分。比如以如下方式存储：[星期一,星期二]。JSON存储方式也差不多是这个意思。

方案三：多表关联

使用主表和子表的方式。

方案四：单字段存储，位与查询

该方案主要的思路是，单字段存储的不再是某个具体的类型，而是聚合值。参考Linux权限控制思路，比如 1代表星期一，2代表星期二，那么3则代表星期一和星期二。当然这些代表值并不是胡乱定义的，接下来小七将结合一个具体的例子，实践说明一下。

实践

首先我们为星期一到星期天这七天都定义一个二进制数。定义规则如下：每一个二进制位都代表是否选中，1代表选中，0代表未选中，那么周一到周天对应的二进制数如下：

星期一：0000001

星期二：0000010

......

星期天：1000000

为了方便读者理解，小七建了以下映射表格（注：week_ten为对应的10进制数）：

如果我们需要选中星期一和星期二，那么

0000001+0000010即可，结果为0000011；同理如果我们需要选中周一到周天，那么二进制表示值为1111111。

这样操作有什么好处呢？举个例子，我们一眼就可以看出来

周四（0001000）肯定不在（0000011）中，但它在（1111111）中。

这个值的比较正好符合位运算中的位与。

1、我们要判断周四（0001000）是否在（0000011）的中

0001000

& 

0000011

---------

0000000 转为10进制为 0

2、我们要判断周四（0001000）是否在（1111111）的中

0001000

& 

1111111

---------

0001000 转为10进制为 8

为了方便演示，小七新建了一张配置表如下：

-- 判断周四在哪些规则中
SELECT * FROM weeks WHERE weeks & 8 ;

-- 同时包含周一和周四的规则
SELECT * FROM weeks WHERE weeks & 8 and weeks & 1;

总结

如果数据范围有限，且变更不频繁的场景，可以考虑使用位与运算解决多选值存储的问题。

附录

文中实践的表结构与数据

-- ----------------------------

-- Table structure for week

-- ----------------------------

DROP TABLE IF EXISTS `week`;
CREATE TABLE `week`  (
  `id` int(0) NOT NULL,
  `week_ten` int(0) NULL DEFAULT NULL,
  `week_bin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------

-- Records of week

-- ----------------------------

INSERT INTO `week` VALUES (1, 1, '0000001', '星期一');
INSERT INTO `week` VALUES (2, 2, '0000010', '星期二');
INSERT INTO `week` VALUES (3, 4, '0000100', '星期三');
INSERT INTO `week` VALUES (4, 8, '0001000', '星期四');
INSERT INTO `week` VALUES (5, 16, '0010000', '星期五');
INSERT INTO `week` VALUES (6, 32, '0100000', '星期六');
INSERT INTO `week` VALUES (7, 64, '1000000', '星期天');


-- ----------------------------
-- Table structure for weeks
-- ----------------------------
DROP TABLE IF EXISTS `weeks`;
CREATE TABLE `weeks`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `weeks` int(0) NULL DEFAULT NULL,
  `weeks_bin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of weeks
-- ----------------------------
INSERT INTO `weeks` VALUES (1, 1, '0000001');
INSERT INTO `weeks` VALUES (2, 127, '1111111');
INSERT INTO `weeks` VALUES (3, 8, '0001000');
INSERT INTO `weeks` VALUES (4, 3, '0000011');