MySQL 5.7新支援--------Json類型實戰

1. 背景

　 * 在MySQL 5.7.8中，MySQL支援由RFC 7159定義的本地JSON資料類型，它支援對JSON(JavaScript對象标記)文檔中的資料進行有效通路.

　 * MySQL會對DML JSON資料自動驗證。無效的DML JSON資料操作會産生錯誤.

* 優化的存儲格式。存儲在JSON列中的JSON文檔轉換為一種内部格式，允許對Json元素進行快速讀取通路.

* MySQL Json類型支援建立索引增加查詢性能提升.

2. Json類型所需的存儲空間和值範圍

類型

占用位元組

最大長度

Json

資料長度 + 4 bytes

3. Json相關函數操作

* JSON_OBJECT(string1, string2...) 建立 key-value 類型 Json 對象

<code>mysql> SELECT JSON_OBJECT(</code><code>'k1'</code><code>, </code><code>'v1'</code><code>, </code><code>'k2'</code><code>, </code><code>'v2'</code><code>);</code>

<code>| JSON_OBJECT(</code><code>'k1'</code><code>, </code><code>'v1'</code><code>, </code><code>'k2'</code><code>, </code><code>'v2'</code><code>) |</code>

* JSON_ARRAY(string1, string2...) 建立一個 Json 數組

<code>mysql> SELECT JSON_ARRAY(</code><code>'a'</code><code>, </code><code>'b'</code><code>, </code><code>'c'</code><code>, </code><code>'d'</code><code>);</code>

<code>| JSON_ARRAY(</code><code>'a'</code><code>, </code><code>'b'</code><code>, </code><code>'c'</code><code>, </code><code>'d'</code><code>) |</code>

* JSON_TYPE(object) 判斷并顯示資料類型 [ 值非法會報錯顯示 ]

<code>mysql> SELECT JSON_TYPE(</code><code>'"lisea"'</code><code>);</code>

<code>| JSON_TYPE(</code><code>'"lisea"'</code><code>) |</code>

<code>| STRING |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'["a", "b", 1]'</code><code>);</code>

<code>| ARRAY |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'1'</code><code>);</code>

<code>| INTEGER |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>);</code>

<code>| OBJECT |</code>

* JSON_MERGE(doc1,doc2....) 合并多個Json對象

<code>mysql> SELECT JSON_MERGE(</code><code>'{"k1":"v1"}'</code><code>,</code><code>'{ "k2":"v2"}'</code><code>);</code>

<code>| JSON_MERGE(</code><code>'{"k1":"v1"}'</code><code>,</code><code>'{ "k2":"v2"}'</code><code>) |</code>

<code>mysql> SELECT JSON_MERGE(</code><code>'["k1","k2"]'</code><code>,</code><code>'{ "k3":"v3"}'</code><code>);</code>

<code>| JSON_MERGE(</code><code>'["k1","k2"]'</code><code>,</code><code>'{ "k3":"v3"}'</code><code>) |</code>

* JSON_EXTRACT(object, key) 通過Json key方式擷取Val值

<code>mysql> SELECT JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>);</code>

<code>| JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>) |</code>

<code>mysql> SELECT JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.*'</code><code>);</code>

<code>| JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.*'</code><code>) |</code>

* JSON_SET(object, key, val, key, val....) 通過key修改val值

<code>mysql> SELECT JSON_SET(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'lisea'</code><code>, </code><code>'$.k2'</code><code>, </code><code>'hello'</code><code>);</code>

<code>| JSON_SET(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'lisea'</code><code>, </code><code>'$.k2'</code><code>, </code><code>'hello'</code><code>) |</code>

<code>| {</code><code>"k1"</code><code>: </code><code>"lisea"</code><code>, </code><code>"k2"</code><code>: </code><code>"hello"</code><code>} |</code>

* JSON_INSERT(object, key, val)添加新值到對象中，如果key已存在，不替換val

<code>mysql> SELECT JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>);</code>

<code>| JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>) |</code>

<code>mysql> SELECT JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>);</code>

<code>| JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>) |</code>

* JSON_REPLACE() 替換現有的值并忽略新的值

<code>mysql> SELECT JSON_REPLACE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>);</code>

<code>| JSON_REPLACE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>) |</code>

<code>| {</code><code>"k1"</code><code>: </code><code>"hello"</code><code>, </code><code>"k2"</code><code>: </code><code>"v2"</code><code>} |</code>

* JSON_REMOVE() 通過key移除

<code>mysql> SELECT JSON_REMOVE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>);</code>

<code>| JSON_REMOVE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>) |</code>

* JSON_KEYS() 擷取所有key

<code>mysql> SELECT JSON_KEYS(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>);</code>

* JSON_UNQUOTE() 去掉值的引号

<code>mysql> SELECT JSON_UNQUOTE(</code><code>'"hello"'</code><code>);</code>

<code>| JSON_UNQUOTE(</code><code>'"hello"'</code><code>) |</code>

<code>| hello |</code>

* JSON_DEPTH() 擷取Json對象的深度

<code>mysql> SELECT JSON_DEPTH(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>);</code>

<code>| JSON_DEPTH(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>) |</code>

<code>mysql> SELECT JSON_DEPTH(</code><code>'{}'</code><code>);</code>

<code>| JSON_DEPTH(</code><code>'{}'</code><code>) |</code>

* JSON_VALID() 判斷是否為有效的json格式

<code>mysql> SELECT JSON_VALID(</code><code>'{"k1":"v1"}'</code><code>);</code>

<code>| JSON_VALID(</code><code>'{"k1":"v1"}'</code><code>) |</code>

<code>mysql> SELECT JSON_VALID(</code><code>'{"k1":"v1"'</code><code>);</code>

<code>| JSON_VALID(</code><code>'{"k1":"v1"'</code><code>) |</code>

* JSON_LENGTH() 擷取指定路徑下的長度

長度的計算規則:

标量的長度為1

json array的長度為元素的個數

json object的長度為key的個數

<code>mysql> SELECT JSON_LENGTH(</code><code>'[1, 2, 3]'</code><code>);</code>

<code>| JSON_LENGTH(</code><code>'[1, 2, 3]'</code><code>) |</code>

<code>mysql> SELECT JSON_LENGTH(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>); </code>

<code>| JSON_LENGTH(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>) |</code>

* JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

　　　查詢包含指定字元串的paths，并作為一個json array傳回　

　　　one_or_all："one"表示查詢到一個即傳回；"all"表示查詢所有。

　　　search_str：要查詢的字元串。可以用LIKE裡的'%'或‘_’比對。

　　　path：在指定path下查。

<code>mysql> SELECT JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'one'</code><code>, </code><code>'v2%'</code><code>);</code>

<code>| JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'one'</code><code>, </code><code>'v2%'</code><code>) |</code>

<code>mysql> SELECT JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'all'</code><code>, </code><code>'v2%'</code><code>);</code>

<code>| JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'all'</code><code>, </code><code>'v2%'</code><code>) |</code>

4. 總結

以需求驅動技術，技術本身沒有優略之分，隻有業務之分。

本文轉自asd1123509133 51CTO部落格，原文連結：http://blog.51cto.com/lisea/1943339，如需轉載請自行聯系原作者

MySQL 5.7新支援--------Json類型實戰

繼續閱讀

javascript的for (var i in data)慎用javascript中的for (var i in data)謹慎用

MySQL的4種隔離級别？出現問題

tab滑鼠經過菜單切換

vue （vue2.0）使用總結(從大體結構總結)

vue搭建過程及出現問題

/\B(?=(?:\d{3})+$)/g 一條令人費解的正規表達式

neo4j之cypher使用文檔

适用于JavaScript的ECMAScript 2020規範向前發展

JS生成uuid的四種方法

mysql使用source指令導入.sql檔案

vue-cli簡介（中文翻譯）

sqlServer根據經緯查距離

Ajax發送和擷取json資料到Spring mvc 1.spring mvc後端2.web前段

layui多任務上傳添加進度條

JSONObject包導入異常 java.lang.NoClassDefFoundErrorweb項目的導入包的問題