整合mysql多个bool值字段，用&查询

需求

商品需要有是否上架，是否可用现金，是否可用提货额度，是否可用积分，是否可用金币等一些bool 值的字段；我们可以考虑转化为位的查询运算；
创建表

CREATE TABLE `products`  (
  `id` int(0) UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `price` decimal(10, 2) NOT NULL,
  `type` tinyint(4) NOT NULL DEFAULT 0,
  `created_at` datetime(0) NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL,
  `deleted_at` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
8
9
10

定义

我们定义type 字段为，左起第一位 现金，第二位 积分，第三位 提货额度 第四位 上下架，即

位与运算符 &
参与&运算的两个二进制位都为 1 时，结果就为 1，否则为 0。例如1|1结果为 1，0|0结果为 0，1|0结果为 0，这和逻辑运算中的&&非常类似。

位或运算符 |
参与|运算的两个二进制位有一个为 1 时，结果就为 1，两个都为 0 时结果才为 0。例如1|1结果为 1，0|0结果为0，1|0结果为1，这和逻辑运算中的||非常类似

添加 模拟数据

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, '仅提货', 1300.00, 4, NULL, NULL, NULL);
INSERT INTO `products` VALUES (2, '仅积分', 1355555.00, 2, NULL, NULL, NULL);
INSERT INTO `products` VALUES (3, '仅现金', 15.00, 1, NULL, NULL, NULL);
INSERT INTO `products` VALUES (4, '上架提货', 12222.00, 12, NULL, NULL, NULL);
INSERT INTO `products` VALUES (5, '上架积分', 222.00, 10, NULL, NULL, NULL);
INSERT INTO `products` VALUES (6, '上架现金', 566.00, 9, NULL, NULL, NULL);
INSERT INTO `products` VALUES (7, '上架提货积分', 88.00, 14, NULL, NULL, NULL);
INSERT INTO `products` VALUES (8, '上架提货现金', 66.00, 13, NULL, NULL, NULL);

1
2
3
4
5
6
7
8
9
10
11
12

插入数据

需求1: 可以积分提货及现金都可以的产品,即0111,bindec(0111)->7,插入数据

INSERT INTO `products`(`id`, `name`, `price`, `type`, `created_at`, `updated_at`, `deleted_at`) VALUES (9, '提货积分现金', 777.00, 7, NULL, NULL, NULL);
1

需求2：修改刚插入数据的名称为‘上架提货积分现金’及状态改为上架，
取出数据type=7;
$type = 7|8;//15

UPDATE `test`.`products` SET `name` = '上架提货积分现金', `type` = 15 WHERE `id` = 9
1

查询

需求1：
查找能用积分购买的上架商品

SELECT * FROM `products` where type&8 AND type &2
1

或者

SELECT * FROM `products` where (type&10)=10
1

结果都为

第一种好理解：表中的type与8（1000）与2（0010）同时满足查找，其实第一条的sql 相当于

SELECT * FROM `products` where (type&8)>0 AND (type &2)>0
1

第二种,相当于

SELECT * FROM `products` where (type&(2|8))=(2|8)
1

• 2 的补码为 0010，8 的补码为 1000，按位或运算之后，结果为 1010，即整数 10；
• type 字段值与10（1010）进行&运算，结果=1010（存在上架积分的）

总结

1. 用mysql 的位运算查询，需要比较扎实的基本功。逻辑理解起来也比较绕一点，但是也更方便更简洁了；
2. 那type这个字段的索引不是失效了？其实type 本身是枚举的类型，不适合创建索引；那也就不存在索引失效的问题；
3. 以后我们可以深入探讨下整合后的查询效率问题；