时间戳

首页 > 解决方案 > 多个引用的数据类型是什么？

php - 多个引用的数据类型是什么？

问题描述

我需要最快的性能方法来检索需要快速提取的整数。这是我的用例：我的网站上有一个“排序/过滤”选项。我有狗的属性，比如长毛、短毛、大号、狗等……我希望用户能够按狗的属性进行过滤。

这是我的桌子：

dogs
- id
- attributes

以下是关于狗的属性的参考（对于这个问题不是很重要，只是给你一个想法）：

0 = adoptable pet
1 = short hair
2 = long hair
3 = fluffy
4 = black
5 = brown
6 = white
7 = golden/yellow
8 = etc...

现在，我将属性保存TEXT在 JSON 格式的数据类型中。例如，["0","4"]或["0"]或["2"]。

所以我想像这样选择所有属性：

SELECT * FROM dogs WHERE attributes LIKE %0% OR attributes LIKE %1% OR attributes LIKE %4% attributes LIKE %7%

如何选择属性包含 0、1、4 或 7（不是和）的所有行。如果一行具有这些属性之一，请选择它们。

处理它的最佳方法是什么？我应该在 MySQL 中存储为 JSON 数据类型还是应该做其他事情？我希望能够SELECT快速地把它拉起来。我觉得我拥有它的方式LIKE不会很快。

最好和最快的方法是什么，尤其是数十万行到数百万行？

提前谢谢你！

标签： phpmysqldatabaseperformancepdo

解决方案

逗号分隔的列表是一个有问题的反模式。（Bill Karwin 在他的《SQL AntiPatterns: Avoiding the Pitfalls of Database Programming》一书中做了一个很好的介绍，所以我不打算深入讨论我们为什么要避免这种情况。相反，我将跳入一个经过验证的模式通常用于实现多值属性。）

规范的关系模式将创建一个表，每行都有一个属性。例如这样的：

CREATE TABLE dog_attribute 
( dog_id  int not null comment 'fk ref dog.id'
, attr    int not null comment '1=adoptable, 2=...'
, PRIMARY KEY (dog_id, attr) 
, CONSTRAINT FK_dog_attribute_dog FOREIGN KEY dog_id REFERENCES dog (id)
  ON UPDATE CASCADE ON DELETE CASCADE
, UNIQUE KEY dog_attribute_UX (attr, dog_id)
);

--

要为给定的 dog_id 添加三个属性，我们添加三行：

INSERT INTO dog_attibute (dog_id, attr) VALUES
 (1,2)
,(1,3)
,(1,5)

--

有几种可能的查询模式。举几个简单的例子：

要找到同时具有属性 2 和属性 3 的 dog_id，我们可以这样做：

SELECT da.dog_id 
  FROM dog_attribute da
 WHERE da.attr IN (2,3) 
 GROUP
    BY da.dog_id 
HAVING COUNT(1) = 2

还有其他查询模式可以提供等效结果，例如

SELECT da.dog_id 
  FROM dog_attribute da
  JOIN dog_attribute dd
    ON dd.dog_id = da.dog_id 
   AND dd.attr = 3
 WHERE da.attr = 2

第一个查询模式可以扩展为找到至少具有两个给定属性集的 dog_id。例如

 WHERE da.attr IN (2,3,5,7)

HAVING COUNT(1) >= 2

第二个查询可以扩展为单个属性添加连接和反连接，

--

要查找具有属性 2 且具有（或两者）属性 3 和 4 且既没有属性 7 也没有属性 8 的行，我们可以执行以下操作：

SELECT da.dog_id 
  FROM dog_attribute da
  LEFT
  JOIN dog_attribute dn 
    ON dn.dog_id = da.dog_id 
   AND dn.attr IN (7,8) 
 WHERE dn.dog_id IS NULL 
   AND da.attr = 2
   AND EXISTS ( SELECT 1 
                  FROM dog_attribute de
                 WHERE de.dog_id = da.dog_id 
                   AND de.attr IN (3,4)
              )

（反连接排除具有属性 7 的 dog_id，...）

如果我无法实现上面演示的表格模式，...如果我绝对必须使用逗号分隔的列表，将多个属性保存在单个列中...对于一组小的静态属性（在表定义中定义，而不是动态添加或删除）我会使用 MySQLSET数据类型。

https://dev.mysql.com/doc/refman/8.0/en/set.html

 attributes  SET('','adoptable','short hair','long hair','fluffy','black','brown','white','golden')

并查询，我们可以这样做：

WHERE FIND_IN_SET('black',t.attributes)
  AND FIND_IN_SET('long hair',t.attributes)

推荐阅读