多属性、多分类MySQL模式设计

柔情只为你懂 2024-04-17 06:03 55阅读 0赞

### 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乎网友的回复，大家也可以参考下。

1.  用JSON数据类型，然后利用JSON\_CONTAINS()函数进行查询
2.  用SET数据类型，然后利用FIND\_IN\_SET()函数进行查询

不过，JSON和SET这两种数据类型都不方便加索引以及利用索引扫描，即便是用了5.7的JSON+虚拟列功能，索引效率也是比较低的。而支持JSON数据类型 **多值索引(multi-valued Indexes)** 也要8.0.17 以上版本才支持。

### 3、总结 ###

这样看来，总的来说，**用二进制转十进制方式来解决本案例需求更为高效**，也欢迎提出更多方案思路。

### 延伸阅读 ###

*  Multi-Valued Indexes，https://dev.mysql.com/doc/refman/8.0/en/create-index.html\#create-index-multi-valued
 *  The SET Type，https://dev.mysql.com/doc/refman/8.0/en/set.html

Enjoy MySQL :)

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

最后，欢迎扫码订阅《乱弹MySQL》专栏，快人一步获取我最新的MySQL技术分享

![640?wx\_fmt=png][640_wx_fmt_png]

[640_wx_fmt_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/500f69ae57494a3a9739d1a212f0136c.png