MySQL：老版本的替代JSON /在一列中存储和查询多个值

Question

我在写一个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操作系统，并具有重量无论是文件，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 | 

Answer 1

假设DOCUMENT_ID /键/值组合是唯一的，你可以这样做：

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;