MySQL5.7.8中引入了json字段，這種類型的字段使用的頻率比較低，但是在實際操作中，有些業務仍然在用，我們以此為例，介紹下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)
首先我們創建了一個表test1，其中id是int字段，info是json字段，插入了三條數據，如上：
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中的內容。其中：
1、$符號代表的是json的根目錄，
2、我們使用$.age相當于取出來了json中的age字段，
3、當然，在函數最前面，應該寫上字段名字info
下面來看json中常用的函數：
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傳回執行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函數，返回最上一層的key個數，如果想取到中間的某一層，則可以使用$的方法，如下：
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函數，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函數檢索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函數，判斷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字段的操作方法的詳細內容，更多關于MySQL json字段的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- 深入淺出講解MySQL的并行復制
- MySQL刪除了記錄不生效的原因排查
- MySQL CHAR和VARCHAR存儲、讀取時的差別
- MySQL學習教程之聚簇索引
- 淺談MySQL大表優化方案
- MySQL8.0中的降序索引
- 詳解mysql中的存儲引擎
- 記一次MySQL的優化案例
- mysql 主從復制如何跳過報錯
- 淺析MySQL并行復制
