php - MySQL:较旧版本的JSON替代品/在一列中存储和查询多个值

我正在编写一个PHP软件包，其中需要存储一组“文档”，每个文档都有自己的属性，这些属性可以在数量，名称和类型上有所不同，就像不同类型产品的属性可能有所不同（例如，鞋子可能具有材质，颜色和样式，但智能手机可能具有操作系统，重量，尺寸等）

| id  | name       |
|-----|------------|
| 1   | Acme Shoe  |
| 2   | Acme Phone |

我希望能够按其属性查询我的所有文档或产品。查询的范围可以从非常简单的WHERE attribute_a = value_a到更复杂的嵌套子句集，例如WHERE ((attribute_a = value_a OR attribute_a > value_b) AND attribute_b LIKE '%pattern%')

我的理想方案是使用MySQL 5.7+和MariaDB 10.2+提供的本机JSON支持来存储每个文档的属性，并使用方便的JSON_EXTRACT函数提取要查询的任何属性。

| id  | name       | attributes                             |
|-----|------------|----------------------------------------|
| 1   | Acme Shoe  | {"material":"canvas","color":"black"}  |
| 2   | Acme Phone | {"os":"android","weight":100}          |


SELECT *
FROM documents
WHERE (
    JSON_EXTRACT(attributes, "$.weight") = 1
    OR JSON_EXTRACT(attributes, "$.weight") > 99
)
AND JSON_EXTRACT(attributes, "$.os") LIKE '%droid%'

不幸的是，我的软件包需要能够支持旧版本的MySQL和MariaDB。我曾考虑过将JSON存储在TEXT或LONGTEXT字段中，并使用REGEX进行比较时解析出所需的属性值，但我可以想象这将非常耗费资源且速度很慢。如果我错了，请纠正我。

就目前而言，我觉得我被迫寻求EAV类型的解决方案：

| id  | name       |
|-----|------------|
| 1   | Acme Shoe  |
| 2   | Acme Phone |


| id  | document_id | key      | value   |
|-----|-------------|----------|---------|
| 1   | 1           | material | canvas  |
| 2   | 1           | color    | black   |
| 3   | 2           | os       | android |
| 4   | 2           | weight   | 100     |

使用一个WHERE子句查找文档相对简单：

SELECT DISTINCT(document_id)
FROM document_attributes
WHERE key = 'material'
AND value = 'canvas'

但是，我不知道如何实现更复杂的WHERE子句。特别是，问题在于属性存储在单独的行中。例如。


获取具有画布材料且颜色为黑色的文档。
获取具有android os并且权重为1或大于99的文档。


任何意见或建议，将不胜感激。



编辑

在对EAV方法进行了一些考虑之后，到目前为止，我设法做到的最好的办法是针对查询中涉及的每个属性将属性表重复地连接到文档表中。从那里，我可以在WHERE子句中使用每个属性的值。例如，选择属性为“材质”为“画布”或“重量”大于99的所有产品：

SELECT d.id AS id, a1.value AS material, a2.value AS weight
FROM documents AS d
LEFT JOIN attributes AS a1 ON a1.document_id = d.id AND a1.name = 'material'
LEFT JOIN attributes AS a2 ON a2.document_id = d.id AND a2.name = 'weight'
WHERE a1.value = 'canvas'
AND a2.value > 99

这似乎产生：

| id | material | weight |
|----|----------|--------|
| 1  | canvas   | NULL   |
| 2  | NULL     | 100    |

最佳答案

假设document_id / key / value组合是唯一的，则可以执行以下操作：

SELECT document_id FROM example
WHERE `key`='material' AND `value`='canvas'
OR    `key`='color' AND `value`='black'
GROUP BY document_id
HAVING COUNT(*) = 2;

SELECT document_id FROM example
WHERE `key`='os' AND `value`='android'
OR    (`key`='weight' AND (`value` = 1) OR (`value` > 99))
GROUP BY document_id
HAVING COUNT(*) = 2;