MySQL新特性JSON的應用是怎樣的

這篇文章將為大家詳細講解有關MySQL新特性JSON的應用是怎樣的，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

MySQL5.7之JSON使用

MySQL 支持原生的 JSON 數據類型，列不能設置默認值

從MySQL 5.7.8 開始，MySQL支持原生的JSON格式，即有獨立的json類型，用于存放 json格式的數據。

JSON 格式的數據并不是以string格式存儲于數據庫而是以內部的binary 格式，以便于快速的定位到json 格式中值

在插入和更新操作時MySQL會對JSON 類型做校驗，已檢查數據是否符合json格式，如果不符合則報錯。

同時5.7.8 版本提供了四種JSON相關的函數，從而不用遍歷全部數據

a 創建: JSON_ARRAY(), JSON_MERGE(), JSON_OBJECT()

b 修改: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), 

JSON_SET(), and JSON_UNQUOTE()

c 查詢: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(),JSON_SEARCH().

d 屬性: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE() JSON_VALID(). 

CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT,jdoc json)

1、插入 JSON

插入 json 格式的字符串，可以是對象的形式，也可以是數組的形式

INSERT INTO t1(jdoc)VALUES('{"key1":"value1","key2":"value2"}')

INSERT INTO t1(jdoc)VALUE('[1,2,3]')

2、查看插入的JSON數據類型:json_type()

SELECT json_type(jdoc) FROM t1

mysql> select json_type('"abc"') ;

+--------------------+

| json_type('"abc"') |

+--------------------+

| STRING             |

+--------------------+

mysql> select * from t1;

+----+--------------------------------------+

| id | jdoc                                 |

+----+--------------------------------------+

|  1 | {"key1": "value1", "key2": "value2"} |

|  2 | [1, 2, 3]                            |

+----+--------------------------------------+

2 rows in set (0.00 sec)

mysql> select json_type(jdoc) from t1;

+-----------------+

| json_type(jdoc) |

+-----------------+

| OBJECT          |

| ARRAY           |

+-----------------+

mysql> select json_type('["a","b",1]');

+--------------------------+

| json_type('["a","b",1]') |

+--------------------------+

| ARRAY                    |

+--------------------------+

1 row in set (0.00 sec)

3、用專門的函數將字符串變成JSON數組格式存儲，json_object()函數根據鍵值對返回JSON對象

json_array

mysql> select json_array("a",1,now());

+----------------------------------------+

| json_array("a",1,now())                |

+----------------------------------------+

| ["a", 1, "2017-11-02 01:47:38.000000"] |

+----------------------------------------+

json_object

mysql> select json_object("key1",1,"key2","abc");

+------------------------------------+

| json_object("key1",1,"key2","abc") |

+------------------------------------+

| {"key1": 1, "key2": "abc"}         |

+------------------------------------+

1 row in set (0.00 sec)

json_merge():將兩個或兩個以上的JSON文檔合并成一個列表對象

mysql> select json_merge('["a",1,2,3]','{"key1":"value"}');

+----------------------------------------------+

| json_merge('["a",1,2,3]','{"key1":"value"}') |

+----------------------------------------------+

| ["a", 1, 2, 3, {"key1": "value"}]            |

+----------------------------------------------+

若是多個列表的JSON格式會被合并成一個列表存儲

mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');

+-----------------------------------------------------+

| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |

+-----------------------------------------------------+

| [1, 2, "a", "b", true, false]                       |

+-----------------------------------------------------+

1 row in set (0.00 sec)

若多個對象有相同的鍵，值會合并成一個數組。

4、將JSON值自定義一個變量

mysql> set  @a=json_object("key1","sharesoe");

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;

+----------------------+

| @a                   |

+----------------------+

| {"key1": "sharesoe"} |

+----------------------+

1 row in set (0.00 sec)

5、查詢和修改JSON值

 通過提取JSON中鍵獲取值

mysql> select json_extract(jdoc,'$.key1'),json_extract(jdoc,'$.key2')from t1;

+-----------------------------+-----------------------------+

| json_extract(jdoc,'$.key1') | json_extract(jdoc,'$.key2') |

+-----------------------------+-----------------------------+

| "value1"                    | "value2"                    |

+-----------------------------+-----------------------------+

1 row in set (0.00 sec)  

查詢 json 中的數據用 column->path 的形式，其中對象類型 path 這樣表示 $.path, 而數組類型則是 $[index]

mysql> select id,jdoc->'$.key1',jdoc->'$.key2' from t1;

+----+----------------+----------------+

| id | jdoc->'$.key1' | jdoc->'$.key2' |

+----+----------------+----------------+

|  1 | "value1"       | "value2"       |

+----+----------------+----------------+

可以用 JSON_UNQUOTE 函數將雙引號去掉，從 MySQL 5.7.13 起也可以通過這個操作符 ->> 這個和 JSON_UNQUOTE 是等價的

mysql> select id,jdoc->>'$.key1',jdoc->>'$.key2' from t1;

+----+-----------------+-----------------+

| id | jdoc->>'$.key1' | jdoc->>'$.key2' |

+----+-----------------+-----------------+

|  1 | value1          | value2          |

+----+-----------------+-----------------+

1 row in set (0.00 sec)

mysql> select id,json_unquote(jdoc->'$.key1'),jdoc->'$.key2' from t1;

+----+------------------------------+----------------+

| id | json_unquote(jdoc->'$.key1') | jdoc->'$.key2' |

+----+------------------------------+----------------+

|  1 | value1                       | "value2"       |

+----+------------------------------+----------------+

 JSON 作為條件進行搜索。因為 JSON 不同于字符串，所以如果用字符串和 JSON 字段比較，是不會相等的

mysql> select * from t1 where jdoc='{"key1":"value1","key2":"value2"}';

Empty set (0.00 sec)

可以通過 CAST 將字符串轉成 JSON 的形式

mysql> select * from t1 where jdoc=cast('{"key1":"value1","key2":"value2"}' as json);

+----+--------------------------------------+

| id | jdoc                                 |

+----+--------------------------------------+

|  1 | {"key1": "value1", "key2": "value2"} |

+----+--------------------------------------+

通過 JSON 中的元素進行查詢, 對象型的查詢同樣可以通過 column->path

mysql> select * from t1 where jdoc->'$.key1'="value1";

+----+--------------------------------------+

| id | jdoc                                 |

+----+--------------------------------------+

|  1 | {"key1": "value1", "key2": "value2"} |

+----+--------------------------------------+

要特別注意的是，JSON 中的元素搜索是嚴格區分變量類型的，比如說整型和字符串是嚴格區分的

用JSON_CONTAINS 函數，但和 column->path 的形式有點相反的是，JSON_CONTAINS 第二個參數是不接受整數的，若第2個位字符也是有問題

select * from t1 where JSON_CONTAINS(jdoc,"value1","$.key1");

更新 JSON

如果是整個 json 更新的話，和插入時類似的。但如果要更新 JSON 下的元素，MySQL 并不支持 column->path 的形式

JSON_INSERT() 插入新值，但不會覆蓋已經存在的值

mysql> update t1 set jdoc=json_insert(jdoc,'$.key1','abcd','$.name','www.sharesoe.com') where id=1;

Query OK, 1 row affected (0.11 sec)

mysql> select * from t1;

+----+------------------------------------------------------------------+

| id | jdoc                                                             |

+----+------------------------------------------------------------------+

|  1 | {"key1": "value1", "key2": "value2", "name": "www.sharesoe.com"} |

+----+------------------------------------------------------------------+

1 row in set (0.00 sec)

JSON_SET() 插入新值，并覆蓋已經存在的值

mysql> update t1 set jdoc=json_set(jdoc,'$.key1','abcd','$.host','www.sharesoe.com') where id=1;

Query OK, 1 row affected (0.39 sec)

mysql> select * from t1;

+----+--------------------------------------------------------------------------------------------+

| id | jdoc                                                                                       |

+----+--------------------------------------------------------------------------------------------+

|  1 | {"host": "www.sharesoe.com", "key1": "abcd", "key2": "value2", "name": "www.sharesoe.com"} |

+----+--------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

JSON_REPLACE() 只替換存在的值

mysql> update t1 set jdoc=json_replace(jdoc,'$.key1','abcd1','$.host1','www.sharesoe.com') where id=1;

Query OK, 1 row affected (0.14 sec)

mysql> select * from t1;

+----+---------------------------------------------------------------------------------------------+

| id | jdoc                                                                                        |

+----+---------------------------------------------------------------------------------------------+

|  1 | {"host": "www.sharesoe.com", "key1": "abcd1", "key2": "value2", "name": "www.sharesoe.com"} |

+----+---------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

JSON_REMOVE() 刪除 JSON 元素

mysql> update t1 set jdoc=json_remove(jdoc,'$.key1','$.host') where id=1;

Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;

+----+------------------------------------------------+

| id | jdoc                                           |

+----+------------------------------------------------+

|  1 | {"key2": "value2", "name": "www.sharesoe.com"} |

+----+------------------------------------------------+

1 row in set (0.00 sec)

對于使用MySQL varchar 存儲json的業務是一個福音。在設計表的時候，避免大字段存儲。

一來是減少不必要的查詢帶來的IO，帶寬，內存方面的影響 二來是 避免因為表大小太大導致的ddl 時間成本增加系統風險。

關于MySQL新特性JSON的應用是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。