Якщо ви працювали з SQL базами даних, то, напевно, погодитеся що проектувати структуру здається простіше, ніж є насправді. SQL бази даних вірять в структуру, саме тому і називаються Structured Query Language.
З іншого боку сьогодні не менше популярні NoSQL бази даних, які також називаються schema-less - вони жорстко не прив'язані до заданої структурі і не мають структурних обмежень.
Звичайно кожен інструмент має свою область застосування, іноді потрібні комбіновані рішення. Що якщо ви можете жорстко структурувати деякі частини програми, а деякі зробити гнучкими? MySQL в версії 5.7.8 представив новий тип даних - JSON, які дозволяє вирішити цю проблему.
Навіщо використовувати JSON
На даний момент ви напевно питаєте себе навіщо потрібно використовувати JSON, коли MySQL тож непогано справляється зі своїми функціями.
Давайте розглянемо на прикладі. Припустимо ви створюєте веб-додаток де вам потрібно зберігати призначені для користувача настройки в базі даних. Як правило ви можете створити окрему сторінку з полями id. user_id і values або зберігати їх в якусь форматований рядок і парсити в Рантайм. Це непогано працює якщо у вас трохи користувачів. Якщо ж у вас тисяча користувачів і п'ять конфігураційних ключів, то це вже п'ять тисяч записів, які тим не менш складають лише малу частину вашої програми. Розбір форматованої рядки в Рантайм лише створює додаткове навантаження. Використання JSON в даному випадку може заощадити ваші ресурси, зменшити кількість записів в базі і кількість додаткових запитів.
Перед тим як ми зануримося в вивчення JSON функціоналу, нам потрібен приклад бази для роботи. Так що спочатку накидаємо структуру. Ми будемо розглядати випадок використання інтернет-магазину, де знаходиться кілька брендів і безліч електроніки. Оскільки різні гаджети мають різні атрибути (порівняйте Macbook з пилососом), як правило використовується модель Entity-Attribute-Value (EAV) модель. Але ми відмовимося від неї, так як будемо використовувати JSON.
Наша база даних буде називатися store і мати три таблиці - brands. categories і products. Таблиці brands і categories будуть дуже схожі і мати тільки поля id і name.
Тепер створимо таблицю products з полями id, name. brand_id. category_id і attributes`.
Наша структура визначає два зовнішніх ключа для полів brand_id і category_id. посилаються на таблиці brands і categories відповідно.
Поле attributes було визначено як JSON, що дозволить нам використовувати доступний в MySQL JSON-функціонал для цього поля.
Ось така структура бази даних у нас вийшла в підсумку.
Створення запису в таблиці з JSON полем досить просте. Все що потрібно - це додати валідний JSON в значення цього поля в insert запиті.
Також замість того, щоб самому кодувати JSON об'єкт ви можете використовувати вбудовану функцію JSON_OBJECT. Ця функція приймає список пар ключ-значення виду JSON_OBJECT (key1, value1, key2, value2, ... key (n), value (n)) і повертає JSON об'єкт.
Варто звернути увагу на функцію JSON_ARRAY. яка повертає масив JSON об'єктів.
Якщо ви вкажете один ключ кілька разів - тільки перша пара ключ-значення буде використана. Також ключі об'єктів упорядковано і очищаються від прогалин на початку.
Ще одна функція для роботи з JSON - JSON_MERGE. Функція приймає кілька JSON об'єктів і повертає один об'єднаний.
У JSON_MERGE ми передаємо тільки об'єкти. Деякі з них були створені з використанням JSON_OBJECT. яку ви розбирали раніше. Якщо в JSON_MERGE ключ повторюється кілька разів, то його значення будуть об'єднані в масив.
Ми можемо перевірити результат з використання функції JSON_TYPE. яка повертає тип значення, що зберігається.
Отже, ми маємо кілька продуктів в нашій базі.
Для звичайних MySQL запитів умова WHERE виглядає досить просто - потрібно вказати поле, оператор і значення. Для JSON полів це не буде працювати.
Для вибору потрібних рядків по JSON полю потрібно бути знайомим з поняттям шляху. Найпростіші приклади шляху - css селектори. Друга частина пазла - функція JSON_EXTRACT. яка приймає в якості аргументу шлях для переміщення по JSON об'єкту.
Наприклад ми цікавимося в телевізорах з USD або HDMI портом.
Для поновлення JSON значень потрібно використовувати JSON_INSERT. JSON_REPLACE і JSON_SET функції. Ці функції також вимагають шлях щоб позначити які част JSON об'єкта потрібно змінити. Висновок цих функцій - Дійсний JSON об'єкт з застосованими змінами.
Шлях $ .chipset визначає розташування властивості chipset в корені об'єкта.
Давайте відновимо властивість chipset за допомогою JSON_REPLACE.
Наостанок у нас залишилася функція JSON_SET і ми використовуємо її для поновлення телевізорів.
Ці функції здаються ідентичними, але є різниця в їх поведінці. JSON_INSERT додає властивість до об'єкта тільки якщо воно ще не існує. JSON_REPLACE замінює властивість тільки якщо воно існує. JSON_SET додає властивість або замінює його якщо воно вже існує.
Тут ми розглянемо два випадки видалення даних.
Перший - видалення конкретного ключа-значення з ваших JSON полів, другий - видалення рядків цілком з умовою по JSON полях.
Наприклад ми більше не надаємо mount_type інформації для камер і хочемо видалити її для всіх камер. Ми зробимо це з використанням JSON_REMOVE. яка повертає оновлений JSON без зазначеного в шляху ключа.