MySQL5.7中的JSON基本操作指南
更新時間：2019年03月22日 11:10:18 作者：南丞_
這篇文章主要給大家介紹了關于MySQL5.7中JSON的基本操作，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
前言
因為項目需要，存儲字段存儲成了JSON格式，在項目中是將查詢出來的值通過jackson轉成相應的bean進行處理的，覺得不夠簡單方便。
MySQL從5.7版本開始就支持JSON格式的數(shù)據(jù)，操作用起來挺方便的。
建表
在新建表時字段類型可以直接設置為json類型，比如我們創(chuàng)建一張表：
mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);
json類型字段可以為NULL
插入數(shù)據(jù)：
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');
json類型的字段必須時一個有效的json字符串
可以使用JSON_OBJECT()函數(shù)構造json對象：
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));
使用JSON_ARRAY()函數(shù)構造json數(shù)組：
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));
現(xiàn)在查看test_user表中的數(shù)據(jù)：
mysql> select * from test_user;
+----+-----------+--------------------------------------------+
| id | name | info |
+----+-----------+--------------------------------------------+
| 1 | xiaoming | {"age": 18, "sex": 1, "nick_name": "小萌"} |
| 2 | xiaohua | {"age": 17, "sex": 0} |
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]} |
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)
查詢
表達式： 對象為json列->'$.鍵', 數(shù)組為json列->'$.鍵[index]'
mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; +-----------+---------------------+---------------+------------------+ | name | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | +-----------+---------------------+---------------+------------------+ | xiaoming | "小萌" | 1 | NULL | | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+---------------------+---------------+------------------+ 3 rows in set (0.04 sec)
等價于：對象為JSON_EXTRACT(json列 , '$.鍵') ，數(shù)組為JSON_EXTRACT(json列 , '$.鍵[index]')
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 | "小萌" | 1 | NULL | | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+-----------------------------------+-----------------------------+--------------------------------+ 3 rows in set (0.04 sec)
不過看到上面"小萌"是帶雙引號的，這不是我們想要的，可以用JSON_UNQUOTE函數(shù)將雙引號去掉
mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; +----------+-----------------------------------+ | name | JSON_UNQUOTE(info->'$.nick_name') | +----------+-----------------------------------+ | xiaoming | 小萌 | +----------+-----------------------------------+ 1 row in set (0.05 sec)
也可以直接使用操作符->>
mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming'; +----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | xiaoming | 小萌 | +----------+----------------------+ 1 row in set (0.06 sec)
當然屬性也可以作為查詢條件
mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌'; +----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | xiaoming | 小萌 | +----------+----------------------+ 1 row in set (0.05 sec)
值得一提的是，可以通過虛擬列對JSON類型的指定屬性進行快速查詢。
創(chuàng)建虛擬列:
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='小萌'; +----------+-----------+ | name | nick_name | +----------+-----------+ | xiaoming | 小萌 | +----------+-----------+ 1 row in set (0.05 sec)
更新
使用JSON_INSERT()插入新值，但不會覆蓋已經(jīng)存在的值
mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;
看下結果
mysql> select * from test_user where id=2;
+----+---------+--------------------------------------------+-----------+
| id | name | info | nick_name |
+----+---------+--------------------------------------------+-----------+
| 2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花 |
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)
使用JSON_SET()插入新值，并覆蓋已經(jīng)存在的值
mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小張') 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": "小張"} | 小張 |
+----+-----------+---------------------------------------------------------------+-----------+
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": "小花"} | 小花 |
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)
可以看到tag沒有更新進去
刪除
使用JSON_REMOVE()刪除JSON元素
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": "小萌"} | 小萌 |
+----+----------+----------------------------------+-----------+
1 row in set (0.05 sec)
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。
相關文章
Mysql中FIND_IN_SET函數(shù)的使用及問題
FIND_IN_SET函數(shù)用于返回字符串str在字符串列表str_list中的位置,本文主要介紹了Mysql中FIND_IN_SET函數(shù)的使用及問題,具有一定的參考價值,感興趣的可以了解一下2024-07-07
這篇文章主要介紹了Mysql查詢所有表和字段信息,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04
將MySQL的表數(shù)據(jù)全量導入clichhouse庫中
這篇文章主要介紹了將MySQL的表數(shù)據(jù)全量導入clichhouse庫中，詳細介紹全量導出MySQL數(shù)據(jù)到clickhouse表的相關內容，需要的小伙伴可以參考一下2022-03-03
我在用mysql 保存微信昵稱，當插入昵稱數(shù)據(jù)的時候，報錯。接下來通過本文給大家介紹mysql保存微信昵稱特殊字符的方法，需要的朋友一起看看吧2018-06-06
這篇文章主要為大家詳細介紹了mysql 8.0.11 winx64安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-07-07
MySQL INSERT INTO SELECT時自增Id不連續(xù)問題及解決
這篇文章主要介紹了INSERT INTO SELECT時自增Id不連續(xù)問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
這篇文章主要介紹了MySQL中對于索引的基本增刪查改操作總結,索引可以提高MySQL的檢索速度,需要的朋友可以參考下2016-01-01
linux環(huán)境下配置mysql5.6支持IPV6連接的方法
本文主要介紹在linux系統(tǒng)下，如何配置mysql支持IPV6的連接，本文圖文并茂給大家介紹的非常詳細，具有參考借鑒價值，需要的朋友參考下吧2018-01-01
MySQL中建表時可空（NULL）和非空（NOT NULL）的用法詳解
這篇文章主要介紹了MySQL中建表時可空（NULL）和非空（NOT NULL）的用法詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-07-07
這篇文章主要介紹了MySQL UPDATE 語句一個“經(jīng)典”的坑，一個非常有意思的話題，感興趣的小伙伴可以參考下面文章的具體內容哦2021-10-10