开发者社区> xaubllxwtvaqiu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 多属性、多分类MySQL模式设计
+关注继续查看

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、总结

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



            </div>

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
19 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
11 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
11 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
33 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
32 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
24 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
12 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
28 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
34 0
多属性、多分类MySQL模式设计
多属性、多分类MySQL模式设计
34 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
从理论到实践,深度解析MySQL Group Replication
立即下载
MySQL 5.7优化不求人
立即下载
One Box: 解读事务与分析一体化数据库 HybridDB for MySQL
立即下载