1. Введение

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

2. Характеристики типа данных JSON

• Проверка: при вставке или обновлении JSON столбец, MySQL Будет автоматическипроверятьданныеиз JSON Формат,убеждатьсяданныеизчестность。
• Оптимизированное хранилище: JSON. Тип данных хранится в двоичном формате, что более эффективно, чем хранение обычного текста.
• Гибкость: столбец JSON может хранить множество、объект、Вложенные структуры и т.д.,Обеспечивает большую гибкость отображения данных.

3. Функция JSON

MySQL 8 предоставляет ряд встроенных функций для управления и запроса данных JSON:

• извлекатьданные：использовать JSON_EXTRACT() функция Можетот JSON изобразение указывает изданный фрагмент в документе.
• Исправлятьданные：JSON_INSERT()、JSON_REPLACE() и JSON_REMOVE() Функция позволяет вам К JSON Добавить, заменить или удалить данные из документа.
• создавать JSON：JSON_ARRAY() и JSON_OBJECT() функцияиспользуется длясоздавать JSON множествоиобъект。
• Функции запроса: JSON_LENGTH(), JSON_KEYS(), JSON_VALID() и т. д. Функция получения данных JSON, длины ключа или проверки JSON. данныеиз эффективности.
• Другие, такие как JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() и т. д. д.

Следующие примеры показывают, как использовать каждую функцию:

Сначала мы создаем таблицу с именем json_example и вставляем фрагмент данных JSON:

CREATE TABLE json_example (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  data JSON  
);  
  
INSERT INTO json_example (data) VALUES (  
  '{  
    "name": "John Doe",  
    "age": 30,  
    "address": {  
      "street": "123 Main St",  
      "city": "Anytown",  
      "zip": "12345"  
    },  
    "phoneNumbers": ["123-456-7890", "987-654-3210"],  
    "isActive": true  
  }'  
);

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

3.1 JSON_EXTRACT()

• извлекать JSON данныесерединаизконкретная часть

-- извлекать name Поле значения  
SELECT JSON_EXTRACT(data, '$.name') AS extracted_name FROM json_example;  
-- результат: "John Doe"  
  
-- извлекать address объект из city Поле значения  
SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM json_example;  
-- результат: "Anytown"

3.2 JSON_INSERT()

• Вставить новую часть в данные JSON.,еслипуть Уже сохраненосуществоватьне будетзаменять。

-- существовать phoneNumbers Вставьте новый номер телефона после многих  
UPDATE json_example  
SET data = JSON_INSERT(data, '$.phoneNumbers[2]', '555-123-4567');  
-- в это время phoneNumbers становиться ["123-456-7890", "987-654-3210", "555-123-4567"]

3.3 JSON_REPLACE()

• заменять JSON данныесерединаизчасть,еслипуть Не существуетсуществоватьне будетдобавить в。

-- заменять name Поле значения  
UPDATE json_example  
SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');  
-- в это время name становиться "Jane Smith"

3.4 JSON_REMOVE()

• от JSON данныесередина Удалятьобозначениеизчасть。

-- Удалять phoneNumbers Первый номер телефона во многих серединаиз  
UPDATE json_example  
SET data = JSON_REMOVE(data, '$.phoneNumbers[0]');  
-- в это время phoneNumbers становиться ["987-654-3210", "555-123-4567"]

3.5 JSON_ARRAY() и JSON_OBJECT()

• создавать JSON множествоиобъект

-- создать новое из JSON множество  
SELECT JSON_ARRAY('a', 1, TRUE);  
-- результат: ["a", 1, true]  
  
-- создать новое из JSON объект  
SELECT JSON_OBJECT('key1', 'value1', 'key2', 2);  
-- результат: {"key1": "value1", "key2": 2}
JSON_LENGTH() - получать JSON Документ или множество длины.
sql
-- получать phoneNumbers множествоиздлина  
SELECT JSON_LENGTH(data->'$.phoneNumbers') AS phone_numbers_length FROM json_example;  
-- результат: 2 (потому что phoneNumbers многие сейчас существуют, имеет два элемента)

3.6 JSON_KEYS()

• Получить все ключи объекта JSON

-- Получить все ключи объекта JSON  
SELECT JSON_KEYS(data) AS object_keys FROM json_example;  
-- результат: ["name", "age", "address", "phoneNumbers", "isActive"]

3.7 JSON_VALID()

• проверять JSON данныеизэффективность。

-- проверять data Содержит ли столбец действительные JSON  
SELECT JSON_VALID(data) AS is_valid_json FROM json_example;  
-- результат: 1 (поверхность Показывать правда, потому что data Столбец содержит действительные из JSON)

3.8 JSON_QUOTE() и JSON_UNQUOTE()

• Преобразовать строку в JSON Форматизнить,и контр-К операции.

Предположим, что в таблице json_example есть такой фрагмент данных.

INSERT INTO json_example (data) VALUES (  
  '{  
    "name": "John",  
    "interests": ["reading", "music"],  
    "friends": [  
      {"name": "Alice", "age": 28},  
      {"name": "Bob", "age": 32}  
    ]  
  }'  
);

Теперь мы будем использовать вышеуказанную функцию для работы с этим фрагментом данных:

-- использовать JSON_QUOTE Преобразовать обычную строку в JSON нить  
SELECT JSON_QUOTE('Hello, World!') AS quoted_string;  
-- результат: ""Hello, World!""  
  
-- использовать JSON_UNQUOTE Воля JSON Преобразовать нить обратно в обычную нить  
SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;  
-- результат: Hello, World!

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

3.9 JSON_CONTAINS()

• Проверяет, содержит ли документ JSON указанное значение.

-- исследовать interests Многие ли содержат "reading"  
SELECT JSON_CONTAINS(data->'$.interests', '"reading"') AS contains_reading FROM json_example;  
-- результат: 1 (поверхность Показывать правда, потому что interests множество Включать "reading")

Обратите внимание: поскольку строки в JSON заключены в двойные кавычки, нам также необходимо добавить двойные кавычки к значению строки поиска при запросе.

3.9 JSON_CONTAINS_PATH()

• Проверяет, содержит ли документ JSON указанный путь.

-- исследовать Стоит ли сохранятьсуществовать friends множествосерединаизобъект из name путь  
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
-- результат: 1 (поверхность Показывать правда, потому чтожитьсуществовать Долженпуть)

3.10 JSON_ARRAY_APPEND()

• К JSON множестводобавить элемент。

-- К interests множестводобавление "traveling"  
UPDATE json_example  
SET data = JSON_SET(data, '$.interests[2]', 'traveling');  
-- Примечание: здесь используйте JSON_SET, потому что JSON_ARRAY_APPEND нуждатьсяобозначениепутьконкретномумножество  
-- существовать MySQL 8.0.17 и вышесередина,Можетиспользовать JSON_ARRAY_APPEND Добавляйте элементы правильно  
-- Например: JSON_ARRAY_APPEND(data, '$.interests', 'traveling')

Примечание. В приведенном выше примере вместо JSON_ARRAY_APPEND используется JSON_SET, поскольку до MySQL 8.0.17 синтаксис JSON_ARRAY_APPEND немного отличался и требовал указания пути к определенному элементу массива. Начиная с версии 8.0.17, JSON_ARRAY_APPEND корректно добавляется в конец массива.

правильный JSON_ARRAY_APPEND Использование следующее:

-- К interests множестводобавление «путешествие» (относится к MySQL 8.0.17 и более поздние версии)  
UPDATE json_example  
SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');

3.11 JSON_ARRAY_INSERT()

• JSON существует множество вставляет элемент в указанную позицию.

-- существовать interests множество вставить на первую позицию "gaming"  
UPDATE json_example  
SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');  
-- результат: interests множествосейчассуществоватьда ["gaming", "reading", "music", "traveling"]

4. Индекс JSON

Чтобы улучшить производительность запросов, MySQL 8 поддерживает индексацию столбцов JSON. Однако из-за гибкости данных JSON прямая индексация всего документа JSON неэффективна. Поэтому MySQL вводит концепцию виртуальных столбцов (Virtual Columns).

• виртуальный столбец：виртуальный столбец позволяет базировать JSON Значение из в столбце создает новый столбец из, и Создать для этого нового столбца. индекс. Таким образом, когда вы основываете JSON При запросе определенного поля данных MySQL Можетиспользоватьиндексдля ускорения запросов。（овиртуальный столбеця Волясуществоватьпослеиз Подробное объяснение статьи）
• Создать индекс：путем объединенияиспользовать JSON_EXTRACT() функцияивиртуальный столбец, вы легко можете за JSON данныесерединаизконкретные поля Создать индекс。

На основе приведенной выше таблицы json_example давайте рассмотрим создание индекса для поля json.

4.1 Добавление виртуальных столбцов

Мы добавим фиктивный столбец с именем first_interest, в котором будет храниться первый элемент массива интересов.

ALTER TABLE json_example  
ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;

Здесь мы используем JSON_EXTRACT(), чтобы получить первый элемент массива интересов, и используем JSON_UNQUOTE(), чтобы удалить кавычки, поскольку JSON_EXTRACT() возвращает строку в формате JSON.

4.2 Создание индексов для виртуальных столбцов

CREATE INDEX idx_first_interest ON json_example(first_interest);

Теперь мы создаем индекс для столбца first_interest, который ускорит запросы на основе этого столбца.

4.3 Оптимизация запросов

Теперь мы можем выполнять запросы на основе столбца first_interest и использовать индекс для ускорения процесса запроса.

SELECT * FROM json_example WHERE first_interest = 'reading';

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

5. Практические сценарии применения

• Хранилище файлов конфигурации. Информация о конфигурации приложения обычно хранится JSON Представление формата. использовать MySQL 8 из JSON данныетип,ты Можетлегко Воля Эта информация о конфигурациижитьмагазинсуществоватьданные Библиотекасередина,ииспользовать JSON Функция запроса и изменения.
• Ведение журнала: записи журнала обычно хранятся в структурированном формате JSON. это идеализвыбирать。проходить Волябревноданныежитьмагазинсуществовать JSON Столбец вы можете легко анализировать и запрашивать данные журнала.
• Интеграция с интерфейсом: используйте JSON обмен данными с бэкендом. использовать MySQL 8 из JSON Благодаря поддержке вы сможете упростить взаимодействие между библиотекой данных и интерфейсом.

6. Меры предосторожности

• Производительность: Хотя MySQL 8 предусмотрено JSON из поддерживается, но по сравнению с традиционными реляционными данными JSON Запрос все еще может быть недостаточно эффективным.
• проверка данных: Хотя MySQL проверяет формат данных JSON, он не проверяет бизнес-правила или целостность данных.
• Сложность: JSON Структура данных может быть более сложной, чем традиционная реляционная структура данных, что может увеличить сложность запросов и обслуживания.

7. Заключение

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

Навыки обновляются благодаря обмену ими, и каждый раз, когда я получаю новые знания, мое сердце переполняется радостью. Искренне приглашаем вас подписаться на публичный аккаунт 『 Код на тридцать пять 』 , для получения дополнительной технической информации.