MySQL中json字段的操作方法

更新時間：2020年11月06日 09:56:00 作者：AsiaYe

這篇文章主要介紹了MySQL中json字段的操作方法，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下

MySQL5.7.8中引入了json字段，這種類型的字段使用的頻率比較低，但是在實際操作中，有些業(yè)務仍然在用，我們以此為例，介紹下json字段的操作方法：

還是從例子看起：

mysql> create table test1(id int,info json);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values (1,'{"name":"yeyz","age":26}'),(2,'{"name":"zhangsan","age":30}'),(3,'{"name":"lisi","age":35}');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+------+---------------------------------+
| id  | info              |
+------+---------------------------------+
|  1 | {"age": 26, "name": "yeyz"}   |
|  2 | {"age": 30, "name": "zhangsan"} |
|  3 | {"age": 35, "name": "lisi"}   |
+------+---------------------------------+
3 rows in set (0.00 sec)

首先我們創(chuàng)建了一個表test1，其中id是int字段，info是json字段，插入了三條數(shù)據(jù)，如上：

mysql> select * from test1 where json_extract(info,"$.age")>=30;
+------+---------------------------------+
| id  | info              |
+------+---------------------------------+
|  2 | {"age": 30, "name": "zhangsan"} |
|  3 | {"age": 35, "name": "lisi"}   |
+------+---------------------------------+
2 rows in set (0.00 sec)

我們可以通過json_extract的方法得到json中的內(nèi)容。其中：

1、$符號代表的是json的根目錄，

2、我們使用$.age相當于取出來了json中的age字段，

3、當然，在函數(shù)最前面，應該寫上字段名字info

下面來看json中常用的函數(shù)：

a、json_valid判斷是否是json字段，如果是，返回1，如果不是，返回0

mysql> select json_valid(2);
+---------------+
| json_valid(2) |
+---------------+
|       0 |
+---------------+
1 row in set (0.01 sec)
mysql> select json_valid('{"num":2}');
+-------------------------+
| json_valid('{"num":2}') |
+-------------------------+
|            1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select json_valid('2');
+-----------------+
| json_valid('2') |
+-----------------+
|        1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select json_valid('name');
+--------------------+
| json_valid('name') |
+--------------------+
|         0 |
+--------------------+
1 row in set (0.00 sec)

這里需要注意的是，如果傳入了字符串2，那么，返回結果是1

b、json_keys傳回執(zhí)行json字段最上一層的key值

mysql> select json_keys('{"name":"yeyz","score":100}');
+------------------------------------------+
| json_keys('{"name":"yeyz","score":100}') |
+------------------------------------------+
| ["name", "score"]            |
+------------------------------------------+
1 row in set (0.01 sec)
mysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}');
+----------------------------------------------------------------+
| json_keys('{"name":"yeyz","score":{"math":100,"English":95}}') |
+----------------------------------------------------------------+
| ["name", "score"]                       |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
#如果有多層，可以在最后面使用$的方法，拿到其中的某一層的目錄
mysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score');
+--------------------------------------------------------------------------+
| json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score') |
+--------------------------------------------------------------------------+
| ["math", "English"]                           |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

c、json_length函數(shù)，返回最上一層的key個數(shù)，如果想取到中間的某一層，則可以使用$的方法，如下：

mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+---------------------------------------------------------------------------+
| json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') |
+---------------------------------------------------------------------------+
|                                     3 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score');
+-------------------------------------------------------------------------------------+
| json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score') |
+-------------------------------------------------------------------------------------+
|                                          2 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

d、json_depth函數(shù)，json文件的深度，測試例子如下：

mysql> select json_depth('{"aaa":1}'),json_depth('{}');
+-------------------------+------------------+
| json_depth('{"aaa":1}') | json_depth('{}') |
+-------------------------+------------------+
|            2 |        1 |
+-------------------------+------------------+
1 row in set (0.00 sec)

mysql> select json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+--------------------------------------------------------------------------+
| json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') |
+--------------------------------------------------------------------------+
|                                    3 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

這里需要注意的是，形如{'aa':1}這種形式的json，其深度是2

e、json_contains_path函數(shù)檢索json中是否有一個或者多個成員。

mysql> set @j='{"a":1,"b":2,"c":{"d":4}}';
Query OK, 0 rows affected (0.00 sec)
#one的意思是只要包含一個成員，就返回1
mysql> select json_contains_path(@j,'one','$.a','$.e');
+------------------------------------------+
| json_contains_path(@j,'one','$.a','$.e') |
+------------------------------------------+
|                    1 |
+------------------------------------------+
1 row in set (0.00 sec)
#all的意思是所有的成員都包含，才返回1
mysql> select json_contains_path(@j,'all','$.a','$.e');
+------------------------------------------+
| json_contains_path(@j,'all','$.a','$.e') |
+------------------------------------------+
|                    0 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_contains_path(@j,'one','$.c.d');
+--------------------------------------+
| json_contains_path(@j,'one','$.c.d') |
+--------------------------------------+
|                  1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains_path(@j,'one','$.a.d');
+--------------------------------------+
| json_contains_path(@j,'one','$.a.d') |
+--------------------------------------+
|                  0 |
+--------------------------------------+
1 row in set (0.00 sec)

f、json_type函數(shù)，判斷json中的成員的類型，需要和json_extract結合起來使用。

mysql> select * from test1;
+------+---------------------------------+
| id  | info              |
+------+---------------------------------+
|  1 | {"age": 26, "name": "yeyz"}   |
|  2 | {"age": 30, "name": "zhangsan"} |
|  3 | {"age": 35, "name": "lisi"}   |
+------+---------------------------------+
3 rows in set (0.00 sec)
#判斷name的類型
mysql> select json_type(json_extract(info,"$.name")) from test1;
+----------------------------------------+
| json_type(json_extract(info,"$.name")) |
+----------------------------------------+
| STRING                 |
| STRING                 |
| STRING                 |
+----------------------------------------+
3 rows in set (0.00 sec)
#判斷age的類型
mysql> select json_type(json_extract(info,"$.age")) from test1;
+---------------------------------------+
| json_type(json_extract(info,"$.age")) |
+---------------------------------------+
| INTEGER                |
| INTEGER                |
| INTEGER                |
+---------------------------------------+
3 rows in set (0.00 sec)
#判斷name和age組合起來的類型，可以看到是array
mysql> select json_type(json_extract(info,"$.name","$.age")) from test1;
+------------------------------------------------+
| json_type(json_extract(info,"$.name","$.age")) |
+------------------------------------------------+
| ARRAY                     |
| ARRAY                     |
| ARRAY                     |
+------------------------------------------------+
3 rows in set (0.00 sec)

g、*的作用，所有的值,看下面的例子。

{
 "a":1,
 "b":2,
 "c":
   {
    "d":4
   }
 "e":
   {
   "d":
     {
     "ddd":
     "5"
     }
   }
}
mysql> set @j='{"a":1,"b":2,"c":{"d":4},"e":{"d":{"ddd":"5"}}}';
Query OK, 0 rows affected (0.00 sec)
#所有成員
mysql> select json_extract(@j,'$.*');
+---------------------------------------+
| json_extract(@j,'$.*')        |
+---------------------------------------+
| [1, 2, {"d": 4}, {"d": {"ddd": "5"}}] |
+---------------------------------------+
1 row in set (0.00 sec)
#所有成員中的d成員
mysql> select json_extract(@j,'$.*.d');
+--------------------------+
| json_extract(@j,'$.*.d') |
+--------------------------+
| [4, {"ddd": "5"}]    |
+--------------------------+
1 row in set (0.00 sec)

以上就是MySQL中json字段的操作方法的詳細內(nèi)容，更多關于MySQL json字段的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

mysql安裝配置方法圖文教程(CentOS7)
這篇文章主要為大家詳細介紹了centos7下mysql安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-04-04
MySQL數(shù)據(jù)庫遷移全過程
本文詳細解析了MySQL數(shù)據(jù)庫遷移的整個過程,包括準備工作、遷移方法、注意事項和優(yōu)缺點,文章介紹了三種常見的遷移方法：使用mysqldump導出和導入、使用ibd文件遷移和使用目錄整體遷移,每種方法都有其優(yōu)缺點,選擇合適的方法取決于具體的遷移需求和環(huán)境
2025-02-02
有效查詢MySQL表中重復數(shù)據(jù)的方法和技巧分享
在MySQL數(shù)據(jù)庫中,偶爾會遇到需要查找表中出現(xiàn)的重復數(shù)據(jù)的情況,這種情況下,我們可以通過編寫一些SQL查詢語句輕松地找到并處理這些重復行,本文將介紹一些常見的方法和技巧,幫助你有效地查詢MySQL表中的重復數(shù)據(jù),需要的朋友可以參考下
2023-10-10
修改MySQL所有表的編碼或修改某個字段的編碼步驟詳解
這篇文章主要給大家介紹了關于修改MySQL所有表的編碼或修改某個字段編碼的相關資料,在進行數(shù)據(jù)庫編碼更改之前,需要先確定目標編碼格式,常見的編碼格式有UTF-8、GBK等,需要的朋友可以參考下
2023-12-12
Mysql索引常見問題匯總
這篇文章主要介紹了Mysql索引常見問題匯總，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下
2020-10-10
Mysql 本地計算機無法啟動 mysql 服務錯誤 1067：進程意外終止。
初學php接觸mysql，遇到一些問題，卸載重裝后，無法啟動mysql服務，網(wǎng)絡上有很多種說法，我這里將我解決這個問題的辦法提出
2009-12-12
MySQL 請選擇合適的列
如果你的表結構設計不良或你的索引設計不佳，那么請你優(yōu)化你的表結構設計和給予合適的索引，這樣你的查詢性能就能提高幾個數(shù)量級?！獢?shù)據(jù)越大，索引的價值越能體現(xiàn)出來
2012-05-05
Windows7 64位安裝最新版本MySQL服務器的圖文教程
本文通過圖文并茂的形式給大家介紹了Windows7 64位安裝最新版本MySQL服務器的教程，非常不錯，具有一定的參考借鑒價值，需要的朋友參考下吧
2018-06-06
MySQL 百萬級數(shù)據(jù)的4種查詢優(yōu)化方式
本文講解了MySQL 百萬級數(shù)據(jù)的4種查詢優(yōu)化方式，大家可以根據(jù)自身需求，選擇適合自己的優(yōu)化方式
2021-06-06
MySQL中如何重建表
這篇文章主要介紹了MySQL中如何重建表問題。具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-02-02