0、导读
这是来自B乎的一个问答。 当数据同时具备多个属性/分类时，改如何设计表结构和查询？
1、需求描述
我偶尔也会逛逛B乎，看到一些感兴趣的话题也会回复下。
有一次，看到这样的一个话题：
链接： https://www.zhihu.com/question/337083976/answer/767075575 [mysql] 当数据同时属于多个分类时，该怎么查询？ 分类cate字段为[1,2,3,4,5] ，假如要查询满足分类'2'和'5' 的数据该怎么查询？ 我尝试过用 cate like '%2%' AND cate like '%5%'去查。 想问有没有更好的办法，我这样写数据少了还好，多了根本没法查，效率太低了。
恰好我以前做过类似的业务需求设计，所以就回复了这个问题。
2、模式设计思路
这个需求可以有几种不同的解决思路，我们分别展开说一下。
2.1 用bit数据类型
大概思路如下：
1、物品属性列c1 用bit数据类型 来表示，也就是只有0、1两种取值
2、当物品属性具备某个分类属性时，其值为1，否则为0
3、假如共有5个分类，当物品拥有全部分类属性时，则其值为11111，若其不具备第3个分类属性，则其值为11011，在数据库中转成十进制存储
4、上述两种情况下，将二进制转换成十进制表示，即分别是31和27（建议横版观看，可左右滑动）
[root@yejr.me] [zhishutang]> select conv(11111, 2, 10), conv(11011, 2, 10); +--------------------+--------------------+ | conv(11111, 2, 10) | conv(11011, 2, 10) | +--------------------+--------------------+ | 31 | 27 | +--------------------+--------------------+
5、然后，只需要对该列用十进制值进行查询比对就行
6、现在如果想判断是否同时具备2、5两个分类属性时，其二进制表示为01001，转成十进制为9，只需要用条件 where c1=9 即可我们来演示一下：（建议横版观看，可左右滑动）
[root@yejr.me] [zhishutang]>show create table t_bit\G *************************** 1. row *************************** Table: t_bit Create Table: CREATE TABLE `t_bit` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c1` int(10) unsigned NOT NULL DEFAULT '0', `c2` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `c1` (`c1`) ) ENGINE=InnoDB; insert into t_bit select 0,conv(00001, 2, 10), 'item1'; insert into t_bit select 0,conv(00011, 2, 10), 'item2'; insert into t_bit select 0,conv(00111, 2, 10), 'item3'; insert into t_bit select 0,conv(01111, 2, 10), 'item4'; insert into t_bit select 0,conv(11111, 2, 10), 'item5'; insert into t_bit select 0,conv(10111, 2, 10), 'item6'; insert into t_bit select 0,conv(11011, 2, 10), 'item7'; insert into t_bit select 0,conv(11101, 2, 10), 'item8'; insert into t_bit select 0,conv(11110, 2, 10), 'item9'; [root@yejr.me] [zhishutang]>select * from t_bit; +----+----+-------+ | id | c1 | c2 | +----+----+-------+ | 1 | 1 | item1 | | 2 | 3 | item2 | | 3 | 7 | item3 | | 4 | 15 | item4 | | 5 | 31 | item5 | | 6 | 23 | item6 | | 7 | 27 | item7 | | 8 | 29 | item8 | | 9 | 30 | item9 | +----+----+-------+ [root@yejr.me] [zhishutang]>select * from t_bit where c1 = conv(11011,2,10); +----+----+-------+ | id | c1 | c2 | +----+----+-------+ | 7 | 27 | item7 | +----+----+-------+ # 同时我们也注意到这个SQL是可以正常使用索引的 [root@yejr.me] [zhishutang]>desc select * from t_bit where c1 = conv(11011,2,10)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_bit partitions: NULL type: ref possible_keys: c1 key: c1 key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL
下面两种方法是B乎网友的回复，大家也可以参考下。
- 用JSON数据类型，然后利用JSON_CONTAINS()函数进行查询
- 用SET数据类型，然后利用FIND_IN_SET()函数进行查询
不过，JSON和SET这两种数据类型都不方便加索引以及利用索引扫描，即便是用了5.7的JSON+虚拟列功能，索引效率也是比较低的。而支持JSON数据类型 多值索引(multi-valued Indexes) 也要8.0.17 以上版本才支持。
3、总结
这样看来，总的来说，用二进制转十进制方式来解决本案例需求更为高效，也欢迎提出更多方案思路。
