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

更新日期: 2019-07-25阅读: 2.7k标签: 模式

这是来自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、总结

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

原文:https://mp.weixin.qq.com/s/XkllGQHGJ94hv-NrUjZ81A

链接: https://fly63.com/article/detial/4938

js设计模式之单例模式,javascript如何将一个对象设计成单例

单例模式是我们开发中一个非常典型的设计模式,js单例模式要保证全局只生成唯一实例,提供一个单一的访问入口,单例的对象不同于静态类,我们可以延迟单例对象的初始化,通常这种情况发生在我们需要等待加载创建单例的依赖。

前端设计模式:从js原始模式开始,去理解Js工厂模式和构造函数模式

工厂模式下的对象我们不能识别它的类型,由于typeof返回的都是object类型,不知道它是那个对象的实例。另外每次造人时都要创建一个独立的person的对象,会造成代码臃肿的情况。

JavaScript设计模式_js实现建造者模式

建造者模式:是将一个复杂的对象的构建与它的表示分离,使得同样的构建过程可以创建不同的表示。工厂类模式提供的是创建单个类的模式,而建造者模式则是将各种产品集中起来进行管理,用来创建复合对象

html和xhtml,DOCTYPE和DTD,标准模式和兼容模式

主要涉及知识点: HTML与XHTML,HTML与XHTML的区别,DOCTYPE与DTD的概念,DTD的分类以及DOCTYPE的声明方式,标准模式(Standard Mode)和兼容模式(Quircks Mode),标准模式(Standard Mode)和兼容模式(Quircks Mode)的区别

前端四种设计模式_JS常见的4种模式

JavaScript中常见的四种设计模式:工厂模式、单例模式、沙箱模式、发布者订阅模式

javascript 策略模式_理解js中的策略模式

javascript 策略模式的定义是:定义一系列的算法,把它们一个个封装起来,并且使它们可以相互替换。 策略模式利用组合,委托等技术和思想,有效的避免很多if条件语句,策略模式提供了开放-封闭原则,使代码更容易理解和扩展, 策略模式中的代码可以复用。

javascript观察者模式_深入理解js中的观察者模式

javascript观察者模式又叫发布订阅模式,观察者模式的好处:js观察者模式支持简单的广播通信,自动通知所有已经订阅过的对象。存在一种动态关联,增加了灵活性。目标对象与观察者之间的抽象耦合关系能够单独扩展以及重用。

Vue中如何使用方法、计算属性或观察者

熟悉 Vue 的都知道 方法methods、计算属性computed、观察者watcher 在 Vue 中有着非常重要的作用,有些时候我们实现一个功能的时候可以使用它们中任何一个都是可以的

我最喜欢的 JavaScript 设计模式

我觉得聊一下我爱用的 JavaScript 设计模式应该很有意思。我是一步一步才定下来的,经过一段时间从各种来源吸收和适应直到达到一个能提供我所需的灵活性的模式。让我给你看看概览,然后再来看它是怎么形成的

Flutter 设计模式 - 简单工厂

在围绕设计模式的话题中,工厂这个词频繁出现,从 简单工厂 模式到 工厂方法 模式,再到 抽象工厂 模式。工厂名称含义是制造产品的工业场所,应用在面向对象中,顺理成章地成为了比较典型的创建型模式

点击更多...

内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!