Хранение json в mysql

Хранение json в mysql

Я храню данные JSON в таблице MySQL, используя следующий код. Он отлично работает, если JSON короткий, но ломается для более длинного текста. «Field_json» – это LONGTEXT.

Ошибка, которую я получаю:

Неверный запрос: у вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с «G», «username»: «C0WB0Y», «lastName»: «", "id": 31874363>, <"pathToPhoto": " 22960 / фото ‘в строке 2

Используйте PDO ( http://www.php.net/manual/en/book.pdo.php ). Расширение mysql устарело от 5.5

Вам нужно избегать кавычек в вашей строке JSON, иначе они завершат SQL-Query, в результате чего вы получите исключение.

У MySQL нет возможности напрямую индексировать документы JSON, но есть альтернатива: генерируемые столбцы.

С момента введения поддержки типа данных JSON в MySQL 5.7.8 не хватает одной вещи: способности индексировать значения JSON. Для того, чтобы обойти это ограничение, можно использовать генерируемые столбцы. Эта возможность, представленная в MySQL 5.7.5, позволяет разработчикам создавать столбцы, содержащие информацию, полученную из других столбцов, предопределенных выражений или вычислений. Генерируя столбец из значений JSON, а затем индексируя его, можно практически индексировать поле с JSON.

Набор данных в формате JSON, используемый в данной статье, можно скачать на Гитхабе. Он содержит список игроков со следующими элементами: идентификатор игрока, его имя и игры, в которые он играл (Battlefield, Crazy Tennis и Puzzler).

Поле Battlefield содержит любимое оружие игрока, его текущий ранг и уровень этого ранга. Crazy Tennis включает в себя количество выигранных и проигранных игр, а Puzzler содержит время, затраченное игроком на прохождение игры. Создадим начальную таблицу:

Этот запрос создает таблицу players , состоящую из идентификатора и JSON-данных, а также устанавливает в поле id первичный ключ.

Нужно построить индекс по полю с JSON. Давайте посмотрим, что нужно добавить в команду CREATE TABLE .

Генерация столбцов

Для создания генерируемых столбцов в операторе CREATE TABLE используется следующий синтаксис:

Ключевыми словами здесь являются GENERATED ALWAYS и AS . Фраза GENERATED ALWAYS необязательна. Она необходима только в том случае, если вы хотите явно указать, что этот столбец таблицы — генерируемый. Необходимо, чтобы слово AS сопровождалось выражением, которое вернет значение для генерируемого столбца.

Cоздаем столбец с именем names_virtual длиной до 20 символов, в котором будем хранить значение поля «name» из объекта JSON. Обращаться к полю «name» в JSON будем с использованием MySQL-оператора ->> , который эквивалентен написанию JSON_UNQUOTE (JSON_EXTRACT (. )) . Эта конструкция вернет значение поля «name» из объекта JSON в качестве результата.

Читайте также:  Как вернуть нижнюю строку на рабочем столе

Этот код означает, что мы берём поле c JSON player_and_games и извлекаем значение из JSON по ключу «name» — дочернее по отношению к корню.

Как и в большинстве определений столбцов, существует ряд ограничений и параметров, которые можно применить к столбцу.

Уникальные для генерируемых столбцов ключевые слова VIRTUAL и STORED указывают на то, будут ли значения сохраняться в таблице.

Ключевое слово VIRTUAL используется по умолчанию. Оно означает, что значения столбца не сохраняются и не занимают место для хранения. Они вычисляются при каждом чтении строки. Если вы создаете индекс с виртуальным столбцом, значение всё же сохраняется — в индексе.

Ключевое слово STORED указывает, что значения вычисляются при записи данных в таблицу: при вставке или обновлении. В этом случае индексу не нужно сохранять значение.

Другие параметры — необязательные ограничения, которые гарантируют, что значения поля будут NULL или NOT NULL , а также добавления ограничений на индекс, например, UNIQUE или PRIMARY KEY . Для гарантии существования значения следует использовать NOT NULL при создании столбца, однако ограничения зависят от варианта использования. В примере будет использоваться NOT NULL , так как у игроков обязательно есть имя.

Запрос, создающий таблицу:

Заполнение таблицы тестовыми данными:

Содержимое таблицы players на Гисте или…

Таблица включает столбец names_virtual , в который вставлены все имена игроков. Структура таблицы players :

Поскольку мы не указали, является ли генерируемый столбец VIRTUAL или STORED , по умолчанию MySQL автоматически сделал столбец VIRTUAL . Чтобы проверить, являются ли столбцы VIRTUAL или STORED , просто запустите вышеуказанный запрос SHOW COLUMNS , и он покажет либо VIRTUAL GENERATED , либо STORED GENERATED .

Теперь, когда мы настроили таблицу и виртуальный столбец, добавим еще четыре столбца, используя операции ALTER TABLE и ADD COLUMN . Они будут содержать уровни Battlefield, выигранные и проигранные игры в теннис и время в Puzzler.

Опять же, запустив запрос SHOW COLUMNS FROM players; , мы видим, что рядом с ними все столбцы указаны как VIRTUAL GENERATED . Это означает, что мы успешно настроили новые созданные VIRTUAL столбцы.

Выполнение запроса SELECT показывает нам все значения из VIRTUAL COLUMNS , которые должны выглядеть так:

После добавления данные и создания генерируемых столбцов, мы можем создавать индекс для каждого из них, чтобы оптимизировать поиск…

Читайте также:  Зоны запрещенные для полетов квадрокоптеров

Индексирование генерируемых столбцов

При установке вторичных индексов на значения генерируемых столбцов VIRTUAL значения сохраняются в индексе. Это дает преимущества: размер таблицы не увеличивается, появляется возможность использования индексов в MySQL.

Давайте сделаем простой запрос к генерируемому столбцу, чтобы увидеть, как он выглядит, прежде чем индексировать его. Изучив детали запроса при выборе names_virtual и имени «Sally», получим следующее:

Для этого запроса MySQL просматривает каждую строку, чтобы найти «Sally». Однако, можно получить совершенно другой результат, добавив индекс к столбцу:

Теперь, выполняя тот же запрос, получаем:

Как видно, индекс в столбце ускорил запрос, просматривая только одну строку вместо шести, используя индекс names_idx . Давайте создадим индексы для остальных виртуальных столбцов, следуя тому же синтаксису, что и names_idx :

Можно проверить, были ли проиндексированы все наши столбцы, запустив:
Код Гисте или…

Теперь, когда созданы несколько индексов в генерируемых столбцах, давайте усложним поиск. В этом примере выбираются идентификаторы, имена, выигранные теннисные игры, уровень Battlefield и время Puzzler для игроков, которые имеют уровень выше 50, а также выигравших 50 теннисных игр. Все результаты будут упорядочены по возрастанию в соответствии с временем в Puzzler. Команда SQL и результаты будут выглядеть так:

Давайте посмотрим, как MySQL выполнял этот запрос:

При использовании индексов win_idx и level_idx MySQL приходилось обращаться к двум столбцам, чтобы вернуть желаемый результат. Если запрос должен выполнить полный просмотр таблицы с миллионом записей, это займёт очень много времени. Однако, с помощью генерируемых столбцов и их индексированием, MySQL показал очень быстрый результат и удобный способ поиска элементов в JSON-данных.

Тем не менее остается один вопрос: для чего нужны STORED генерируемые столбцц? Как их использовать и как они работают?

Хранение значений в генерируемых столбцах

Использование ключевого слова STORED при настройке генерируемого столбца обычно не предпочтительно, поскольку в основном значения в таблице сохраняются дважды: поле с JSON и в STORED столбце. Тем не менее, существует три сценария, когда в MySQL нужно использовать столбец STORED :

  1. индексирование первичных ключей,
  2. полнотекстовый индекс/индекс R-tree,
  3. столбец, который часто выбирается.

Синтаксис добавления генерируемого STORED столбца, совпадает с созданием генерируемых столбцов VIRTUAL , за исключением того, что нужно добавить ключевое слово STORED :

Чтобы посмотреть как использовать STORED , создадим еще одну таблицу. Она будет брать id из данных JSON и хранить его в STORED столбце. Установим PRIMARY KEY для столбца id :

Читайте также:  Как сделать бесплатный интернет на телефоне теле2

Добавим тот же набор данных в player_two , за исключением того, что удалим id , который ранее добавили в операцию INSERT :

После того, как данные были вставлены в таблицу, запустим SHOW COLUMNS в новой таблице, чтобы узнать, как MySQL создал столбцы. Обратите внимание, что поле id теперь — STORED GENERATED и содержит индекс PRIMARY KEY .

Замечание об использовании PRIMARY KEY с генерируемыми столбцами: MySQL не позволит создавать первичные ключи для генерируемых VIRTUAL столбцов. На самом деле, если не указать STORED в поле id , MySQL выдает следующую ошибку:

В то же время, если не устанавливать индекс первичного ключа и попытаться вставить данные, MySQL выдает сообщение об ошибке:

Это означает, что у таблицы нет первичного ключа. Поэтому нужно вернуться и пересоздать таблицу, либо удалить столбец id и добавить генерируемый STORED столбец с первичным ключом, например:

Вывод

В статье показано как эффективно хранить данные JSON в MySQL, а так же как создавать индексы благодаря генерируемым столбцам. Использование генерируемых столбцов позволит размещать индексы по определенным элементам данных JSON. Именно эта гибкость делает MySQL очень привлекательной для использования JSON.

Теория
Реляционные базы подразумевают, что все часто используемые поля должны храниться в отдельных столбцах. В какой-то момент Вам понадобится искать/сортировать по городу, а его хранение внутри JSON сделает эту операцию крайне сложной.
Все преимущества JSON-а сводятся к тому, что в одной записи можно "легко" добавить или удалить какое-то поле, не прибегая к модификации таблицы. Лёгкость написана в кавычках потому, что модификация одного поля выполняется сложнее, чем первоначальная запись всего объекта: взять JSON; преобразовать в объект; модифицировать значение нужного поля; преобразовать в строку; записать её в базу данных. И делать это нужно будет средствами приложения, MySQL на это просто не способен.

Практика
Использование JSON является нормальной практикой. Если возникает необходимость выполнять поиск по какому-то полю, оно выносится в отдельную колонку. Работы по переносу рутинные, но требуют внимательности от программиста, т.к. путь до значения изменился. Например, раньше было user.data.city и стало user.city.

Сейчас набирает популярность PostgreSQL, где работа с JSON выведена на уровень SQL-синтаксиса. Там Вы сможете легко добавлять/модифицировать/удалять отдельные JSON-поля, не прибегая к помощи приложения. Даже индексы поддерживаются.

Ссылка на основную публикацию
Фото авы удаленного вк
Рабочий способ который на 100 процентов поможет вам вернуть и восстановить вашу удаленную фотографию в социальной сети вконтакте. Мы постарались...
Умные часы для детей xiaomi mi bunny
Детские смарт-часы Xiaomi, изготовленные из прочного пластика различных оттенков, предназначены для отображения текущего времени и дополнительной информации (например, о пройденной...
Улучшить качество связи мтс
Усилитель сигнала МТС– специальный прибор, который необходим для того, чтобы предоставлять более сильный сигнал сотовой связи. Невозможно звонить или отправлять...
Фото внутренностей айфон 6
Шаг 1 Время обзора iPhone 6! Давайте посмотрим на некоторые технические спецификации: Процессор Apple A8 с 64-битной архитектурой Копроцессор движения...
Adblock detector