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

Question

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

这是我的表格:

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...

现在，我将属性保存在 JSON 格式的 TEXT 数据类型中。例如，["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 不会很快。

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

先谢谢你!

Answer 1

逗号分隔列表是一个有问题的反模式。 (Bill Karwin 在他的《SQL AntiPatterns:避免数据库编程的陷阱》一书中做了精彩的介绍，所以我不打算深入讨论为什么我们要避免这种情况。相反，我将跳入一个经过验证的通常用于实现多值属性的模式。)

---

规范的关系模式是创建一个表，每行有一个属性。例如这样的事情:

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，...)

---

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

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)