MySQL поддерживает данные в формате JSON начиная с версии 5.7, с которой легко работать.
Построение таблиц При создании новой таблицы тип поля может быть установлен непосредственно в тип JSON, например, при создании таблицы:
mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);
Поле типа JSON может быть ПУСТЫМ
Вставка данных:
MySQL > INSERT INTO test_user (`name `, `info `) VALUES (`xiaoming', ```sex': 1, `age': 18, `nick_name': `Xiaomeng');
Допустимая строка JSON, если поле типа JSON должно быть
Функция JSON_OBJECT() может использоваться для построения объектов JSON:
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));
Функция JSON_ARRAY() используется для построения массива JSON:
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));
Теперь посмотрите на данные в таблице test_user:
mysql> select * from test_user;
+----+-----------+--------------------------------------------+
| id | name | info |
+----+-----------+--------------------------------------------+
| 1 | Xiaoming | {age: 18, `sex': 1, `nick_name': `Xiaomeng'}|
| 2 | xiaohua | {"age": 17, "sex": 0} |
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]} |
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)запрос Выражение: объектом является столбец JSON – >’$. ключ’, массив-столбец JSON – >’$. ключ [индекс]’
mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; +-----------+---------------------+---------------+------------------+ | name | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | +-----------+---------------------+---------------+------------------+ | Xiaoming | "Xiaomeng" | 1 | NULL| | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+---------------------+---------------+------------------+ 3 rows in set (0.04 sec)
Эквивалентно: объекту JSON_EXTRACT (столбец json,’$. ключ’), массив JSON_EXTRACT (столбец json,’$. ключ [индекс]’)
mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]') from test_user; +-----------+-----------------------------------+-----------------------------+--------------------------------+ | name | JSON_EXTRACT(info, '$.nick_name') | JSON_EXTRACT(info, '$.sex') | JSON_EXTRACT(info, '$.tag[0]') | +-----------+-----------------------------------+-----------------------------+--------------------------------+ | Xiaoming | "Xiaomeng" | 1 | NULL| | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+-----------------------------------+-----------------------------+--------------------------------+ 3 rows in set (0.04 sec)
Но смотрите выше “Xiaomeng” с двойными кавычками, а это не то, что мы хотим, вы можете использовать функцию JSON_UNQUOTE для удаления двойных кавычек.
mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; +----------+-----------------------------------+ | name | JSON_UNQUOTE(info->'$.nick_name') | +----------+-----------------------------------+ | Xiaoming | Xiaomeng| +----------+-----------------------------------+ 1 row in set (0.05 sec)
Оператор – > также может использоваться напрямую.
mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming'; +----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | Xiaoming | Xiaomeng| +----------+----------------------+ 1 row in set (0.06 sec)
Конечно, атрибуты также могут использоваться в качестве условий запроса.
MySQL > select name, Info - >'$. nick_name'from test_user where Info - >'$. nick_name'='Xiaomeng'; +----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | Xiaoming | Xiaomeng| +----------+----------------------+ 1 row in set (0.05 sec)
Стоит отметить, что указанные атрибуты типа JSON могут быть быстро запрошены с помощью виртуальных столбцов.
Создание виртуальных столбцов:
mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;
Обратите внимание на оператора – >.
При использовании он совпадает с обычным типом запроса столбцов:
MySQL > select name, nick_name from test_user where nick_name='Xiaomeng'; +----------+-----------+ | name | nick_name | +----------+-----------+ | Xiaoming | Xiaomeng| +----------+-----------+ 1 row in set (0.05 sec)
Для обновления Вставляйте новые значения с помощью функции JSON_INSERT(), но не переопределяйте существующие значения
MySQL > UPDATE test_user SET info = JSON_INSERT (info,'$. sex', 1,'$. nick_name','floret') where id = 2;
Посмотрите на результат
mysql> select * from test_user where id=2;
+----+---------+--------------------------------------------+-----------+
| id | name | info | nick_name |
+----+---------+--------------------------------------------+-----------+
| 2 | Xiaohua | {age": 17, `sex': 0, `nick_name': `floret'} | floret|
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)Вставьте новые значения с помощью JSON_SET() и переопределите существующие значения
MySQL > UPDATE test_user SET info = JSON_INSERT (info,'$. sex', 0,'$. nick_name','Zhang') where id = 3;
Посмотрите на результат
mysql> select * from test_user where id=3;
+----+-----------+---------------------------------------------------------------+-----------+
| id | name | info | nick_name |
+----+-----------+---------------------------------------------------------------+-----------+
| 3 | Xiaozhang | {"age": 19,""sex": 1," "tag": [3, 5, 90], "nick_name", "Xiao Zhang"} | Xiao Zhang|
+----+-----------+---------------------------------------------------------------+-----------+
1 row in set (0.06 sec)Замените только существующие значения на JSON_REPLACE()
mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;
Посмотрите на результат
mysql> select * from test_user where id=2;
+----+---------+--------------------------------------------+-----------+
| id | name | info | nick_name |
+----+---------+--------------------------------------------+-----------+
| 2 | Xiaohua | {age": 17, `sex': 1, `nick_name': `floret'} | floret|
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)Вы можете видеть, что тег не обновляется.
удалить
Удалите элементы JSON с помощью функции JSON_REMOVE()
mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;
Посмотрите на результат
mysql> select * from test_user where id=1;
+----+----------+----------------------------------+-----------+
| id | name | info | nick_name |
+----+----------+----------------------------------+-----------+
| 1 | Xiaoming | {"age": 18, "nick_name": "Xiaomeng"} | Xiaomeng|
+----+----------+----------------------------------+-----------+
1 row in set (0.05 sec)