MySQL: альтернатива JSON для более старых версий / Хранение & amp; запрос нескольких значений в одном столбце

Я пишу пакет 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%')

Мой идеальный сценарий — использовать встроенную поддержку JSON, предоставляемую MySQL 5.7+ и MariaDB 10.2+, для хранения атрибутов каждого документа и использования удобного 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    |