Использование JSON в MySQL

Использование JSON в MySQL База данных

Обратите внимание, что любая база данных будет принимать документы JSON как одностроковый BLOB-объект. Однако MySQL и PostgreSQL поддерживают проверенные данные JSON в реальных парах ключ / значение, а не в базовой строке.

Нормализация — это метод, используемый для оптимизации структуры базы данных. Правило первой нормальной формы (1NF) гласит, что каждый столбец должен содержать одно значение, что явно нарушается при хранении многозначных документов JSON.

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

Тем не менее, есть хорошие варианты использования JSON для редко заполненных данных или настраиваемых атрибутов.

Создать таблицу с полем JSON

Рассмотрим магазин по продаже книг. У всех книг есть идентификатор, ISBN, название, издатель, количество страниц и другие четкие данные о взаимоотношениях. Предположим, вы хотите добавить к каждой книге любое количество тегов категорий. Вы можете добиться этого в SQL, используя:

  • тег таблица, в которой хранится имя каждого тега с уникальным идентификатором, и
  • tagmap стол со многими ко многим записям отображения книг идентификаторов в идентификаторы тегов

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

CREATE TABLE `book` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Обратите внимание, что столбцы JSON не могут иметь значение по умолчанию, использоваться в качестве первичного ключа, использоваться в качестве внешнего ключа или иметь индекс. Вы можете создавать вторичные индексы для сгенерированных виртуальных столбцов, но проще и практичнее сохранять значение в отдельном поле, если индексы требуются.

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

Целые документы JSON можно передавать в операторах INSERT или UPDATE. Например, наши книжные теги могут быть переданы в виде массива (внутри строки):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON также можно создать с помощью этих:

  • Функция JSON_ARRAY (), создающая массивы. Например:

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

  • Функция JSON_OBJECT (), которая создает объекты. Например:
    -- returns {"a": 1, "b": 2}:
    SELECT JSON_OBJECT('a', 1, 'b', 2);
  • Функция JSON_QUOTE (), которая цитирует строку как значение JSON. Например:
    -- returns "[1, 2, \"abc\"]":
    SELECT JSON_QUOTE('[1, 2, "abc"]');
  • или можно (CAST anyValue AS JSON).
Читайте также:  5 лучших программ для анализа данных

Функция JSON_TYPE () позволяет проверять типы значений JSON. Он должен возвращать OBJECT, ARRAY, скалярный тип (INTEGER, BOOLEAN и т. Д.), NULL или ошибку. Например:

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Функция JSON_VALID () возвращает 1, если JSON действителен, или 0 в противном случае:

-- returns 1:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Попытка вставить недопустимый документ JSON вызовет ошибку, и вся запись не будет вставлена ​​/ обновлена.

Поиск данных JSON

Функция JSON_CONTAINS () принимает документ JSON, в котором выполняется поиск, и другой документ для сравнения. Он возвращает 1, когда найдено совпадение. Например:

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

Аналогичная функция JSON_SEARCH () возвращает путь к заданному совпадению или NULL, если совпадения нет. Он передал документ JSON, в котором выполняется поиск, ’one’чтобы найти первое совпадение или ’all’найти все совпадения, и строку поиска (где %соответствует любому количеству символов и _соответствует одному символу идентично LIKE). Например:

-- all books with tags starting 'Java':
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

Пути JSON

Путь JSON нацелен на значения и может использоваться для извлечения или изменения частей документа JSON. Функция JSON_EXTRACT () демонстрирует это, извлекая одно или несколько значений:

-- returns "SitePoint":
SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Все определения пути начинаются с символа, $за которым следуют другие селекторы:

  • точка, за которой следует имя, например $.website
  • [N] где N — позиция в массиве с нулевым индексом
  • .[*]подстановочные оценивает все элементы объекта
  • [*]подстановочные оценивает все элементы массива
  • в prefix**suffixшаблонные принимает значение всех путей, которые начинаются с префикса имени и в конце с именем суффикс
Читайте также:  Различные типы баз данных, что вы должны знать

Следующие примеры относятся к следующему документу JSON:

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

Примеры путей:

  • $.a возвращается 1
  • $.c возвращается [3, 4]
  • $.c[1] возвращается 4
  • $.d.e возвращается 5
  • $**.e возвращается [5]

Извлечение путей JSON в запросах

Вы можете извлечь имя и первый тег таблицы своей книги, используя запрос:

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

Для более сложного примера предположим, что у вас есть таблица пользователей с данными профиля JSON. Например:

я бы имя профиль
1 Крейг {«Электронная почта»: [«craig@email1.com», «craig@email2.com»], «twitter»: «@craigbuckler»}
2 SitePoint {«Электронная почта»: [], «твиттер»: «@sitepointdotcom»}

Вы можете извлечь имя Twitter, используя путь JSON. Например:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

Вы можете использовать путь JSON в предложении WHERE, чтобы возвращать только пользователей с учетной записью Twitter:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Изменение части документа JSON

Есть несколько функций MySQL для изменения частей документа JSON с использованием записи пути. Они включают:

  • JSON_SET(doc, path, val[, path, val]…): вставляет или обновляет данные в документе
  • JSON_INSERT(doc, path, val[, path, val]…): вставляет данные в документ
  • JSON_REPLACE(doc, path, val[, path, val]…): заменяет данные в документе
  • JSON_MERGE(doc, doc[, doc]…): объединяет два или более документа
  • JSON_ARRAY_APPEND(doc, path, val[, path, val]…): добавляет значения в конец массива
  • JSON_ARRAY_INSERT(doc, path, val[, path, val]…): вставляет массив в документ
  • JSON_REMOVE(doc, path[, path]…): удаляет данные из документа

Поэтому вы можете добавить «технический» тег к любой книге, в которой уже есть тег «JavaScript»:

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Дальнейшая информация

Руководство MySQL предоставляет дополнительную информацию о типе данных JSON и связанных функциях JSON.

Опять же, я призываю вас не использовать JSON, если в этом нет крайней необходимости. Вы можете эмулировать всю документно-ориентированную базу данных NoSQL в MySQL, но это сведет на нет многие преимущества SQL, и вы также можете переключиться на настоящую систему NoSQL! Тем не менее, типы данных JSON могут сэкономить усилия для более неясных требований к данным в приложении SQL.

Оцените статью
bestprogrammer.ru
Добавить комментарий