MySQL JSON поля

С MySQL 5.7 появился нативный тип данных JSON с валидацией, оптимизированным хранением и богатым набором функций. Это удобно для хранения конфигураций, метаданных и полуструктурированных данных.

Создание таблицы с JSON полем

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    meta JSON,
    settings JSON
);

-- Вставка данных
INSERT INTO users (name, meta, settings) VALUES (
    'Alice',
    '{"age": 30, "tags": ["vip", "active"], "address": {"city": "Kyiv"}}',
    JSON_OBJECT('theme', 'dark', 'lang', 'uk')
);

Извлечение данных

-- Оператор -> (возвращает JSON)
SELECT meta->'$.age' AS age FROM users;

-- Оператор ->> (возвращает текст, без кавычек)
SELECT meta->>'$.address.city' AS city FROM users;

-- JSON_EXTRACT (то же, что ->)
SELECT JSON_EXTRACT(meta, '$.tags[0]') FROM users;

-- Фильтрация по JSON-значению
SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

Обновление JSON данных

-- Установить значение
UPDATE users
SET meta = JSON_SET(meta, '$.age', 31, '$.verified', TRUE)
WHERE id = 1;

-- Добавить в массив
UPDATE users
SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'premium')
WHERE id = 1;

-- Удалить ключ
UPDATE users
SET meta = JSON_REMOVE(meta, '$.address')
WHERE id = 1;

Индексация JSON полей

-- MySQL не индексирует JSON напрямую.
-- Используйте виртуальный (generated) столбец:
ALTER TABLE users
ADD COLUMN city VARCHAR(100)
GENERATED ALWAYS AS (meta->>'$.address.city') STORED;

CREATE INDEX idx_city ON users(city);

-- Теперь запрос использует индекс:
SELECT * FROM users WHERE city = 'Kyiv';

Полезные JSON функции

-- Проверить валидность JSON
SELECT JSON_VALID('{"a":1}');  -- 1
SELECT JSON_VALID('bad json'); -- 0

-- Получить тип значения
SELECT JSON_TYPE(meta->'$.tags'); -- ARRAY

-- Слияние объектов
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}'); -- {"a":1,"b":2}

-- Длина массива
SELECT JSON_LENGTH(meta->'$.tags') FROM users;

Не злоупотребляйте JSON: Если поле часто используется в WHERE или JOIN — вынесите его в обычный столбец с индексом. JSON удобен для гибких метаданных, не для реляционных связей.

Типичные ошибки при работе с JSON

Ошибка	Причина	Решение
ERROR 3140 (22032): Invalid JSON text	В столбец JSON пытаются вставить строку, которая не является валидным JSON (например, одинарные кавычки вместо двойных)	Проверить строку через `JSON_VALID()` перед вставкой на стороне приложения
ERROR 3143 (22030): Invalid JSON path expression	Опечатка в пути, например `$.adress` вместо `$.address`, или путь начинается не с `$`	Сверить путь через `JSON_EXTRACT(meta, '$.address')` в интерактивном режиме
ERROR 3168 (22018): JSON path expression does not contain any '[]' or '' wildcard	В функции, ожидающей путь с wildcard (например, некоторых агрегатных), указан точный путь без маски	Использовать `$.tags[*]` вместо `$.tags[0]` там, где нужна выборка по всем элементам

Производительность: JSON против отдельных столбцов

Сравнение проверено на таблице из миллиона строк: выборка по обычному индексированному столбцу VARCHAR идёт за миллисекунды, а фильтрация по JSON-полю без generated column — это type: ALL в EXPLAIN, полный перебор таблицы, потому что MySQL не может построить индекс по произвольному JSON-пути напрямую.

Способ хранения	Поиск по значению	Изменение схемы
Обычный столбец + индекс	Быстрый, type=ref в EXPLAIN	Требует ALTER TABLE
JSON без generated column	Full Table Scan, type=ALL	Не требует ALTER TABLE
JSON + generated column + индекс	Быстрый, как обычный столбец	Требует ALTER TABLE на добавление generated column

Когда JSON-поля не подходят

JSON в MySQL хорош для данных, структура которых меняется от записи к записи и которые редко участвуют в фильтрации или JOIN — настройки пользователя, метаданные интеграций, версионированные снепшоты. Если планируется регулярно искать, сортировать или агрегировать по значению внутри JSON, это сигнал, что поле должно быть отдельным реляционным столбцом или отдельной таблицей: JSON не заменяет нормализацию, а обходит её, и цена обхода — либо полный скан, либо избыточные generated columns на каждый нужный путь.

Проверка плана запроса с JSON-фильтром

-- Без индекса — везде ALL и высокий rows
EXPLAIN SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

-- После добавления generated column и индекса
EXPLAIN SELECT * FROM users WHERE city = 'Kyiv';
-- ожидаем type=ref, rows заметно меньше

-- Проверить, какой индекс реально используется
SHOW INDEX FROM users WHERE Key_name = 'idx_city';

Про построение обычных индексов и правило порядка столбцов в составных индексах — в статье про индексы MySQL, а про чтение вывода EXPLAIN в деталях — в статье про разбор плана запроса.