Mysql 5.7 新特性之 json 類型的增刪改查操作和用法

近幾年來，nosql大行其道，json更是火的一塌糊涂，作為數據庫的元老，mysql在5.7版本中添加了對json數據的支持。這片博客主要介紹mysql json類型的增刪改查等基本操作的用法。

首先創建一個數據庫learnjson，并創建一張表json_start：

插入數據 ：insert操作

在json_start表中我們添加了一個json類型的字段：person;下面我們就來插入一些數據，

json類型的數據插入方式和普通數據插入方式一樣，不同的是插入的是json字符串:

需要注意的是，在插入json類型的字符串時，mysql會對json字符串進行格式校驗，如果格式有誤，就無法插入，并且默認情況下，json數據的大小也是有限制的，默認大小和LONGTEXT ,LONGBLOB 一樣，同時他還受限于mysql配置項：max_allowed_packet. 的限制（默認為4M）.

我們觀察數據庫的結果：

數據已經添加到數據庫中了，我們試著在插入幾條數據：

結果：

你會發現，雖然最后一條記錄的json內容和其他兩條記錄的內容完全不一樣甚至結構都不一樣，但是依然能插入成功，因為mysql在插入時只對格式進行校驗，格式通過了，就當成字符串存儲，并不會考慮與其他記錄是否保持一致，事實上這么做沒必要。但這同時引入了一個使用時需要注意的事項，就是在定義表的時候就必須盡可能的表以及字段的功能，數據結構明確清楚，并且避免一個字段多種數據混合存儲。

更新記錄：update 追加內容：

mysqljson數據跟新操作根據json數據的類型范圍分為列表操作和對象操作兩種類型，都可以直接說那個merge操作。

下面我們試著給第一條記錄追加一個manager屬性，很顯然這是一個對象操作,執行語句如下：

原理就是將原來的json對象和要添加的對象合并，稍后將會描述這其中的合并規則。執行結果如下：

再給第三條記錄追加一個對象：

 更新記錄：追加內容到數組：

追加到數組可以使用JSON_APPEND()(mysql 5.7.9或更早的版本) 或者JSON_ARRAY_APPEND()方法，該操作將指定的對象添加到數組末尾，例如：

執行結果：

更新記錄：update 修改json屬性： 

mysql提供一個非常靈活的本地方法：json_set(doc,path,value),其中doc為要修改的json列，path為要修改的屬性的對象定位表達式，value為新值，例如：

結果為：

 需要注意的是，當使用json_set（）方法去修改一整個對象或者數組時，mysql或將value當成字符串來解析保存，當你的value中包含”時，或自動進行轉義，這樣存儲起來很浪費空間，同時也會產生問題，如果需要修改整個對象，建議使用Json_object(key，value，key2,value2....)來格式化你的對象，例如：

執行結果為：

刪除記錄： 

刪除記錄使用JSON_REMOVE(doc,path1，path2,...)方法,例如：

執行結果為：

查詢記錄：

查詢所有的屬性key,使用json_keys(doc)方法：

結果如下：

查詢指定的記錄中的某個屬性或者值，使用Json_extract(doc,path)方法，例如：

結果如下：

或者：

當然，你也可以使用通配符‘*’來實現多值查詢，例如：

 結果如下：

需要注意的是，查詢返回的結果始終是一個包含查詢結果的數組！

擴展： 

創建一個表 t1，basic_info 字段為JSON類型:

一、插入一條數據：

1、查找 JSON 中的某個字段：

比如查詢 id=1 的 basic_info 中的 name 字段，可以用以下兩種方式：

mysql> select basic_info->’$.name’ from t1 where id =1;
+———————————+
| basic_info->’$.name’ |
+———————————+
| “小明” |
+———————————+

或者：

mysql> select JSON_EXTRACT(basic_info, ‘$.name’) from t1 where id =1;
+——————————————————+
| JSON_EXTRACT(basic_info, ‘$.name’) |
+——————————————————+
| “小明” |
+——————————————————+
1 row in set (0.04 sec)

2、根據JSON中的某個字段查詢表中記錄：

比如查詢name為小明的記錄：

檢索JSON數據的方法，文檔：https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

二、修改 JSON

修改JSON中字段的方法，文檔：https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

1、修改 JSON 中的某個字段：

比如我們修改 id =1 的 basic_info 中的age為 10 歲：

可以使用JSON_REPLACE() 或者 JSON_SET() 函數：

2、往 JSON 中插入一個新的字段：

比如往 basic_info 中插入一個性別“gender”字段：

可以使用JSON_INSERT() 或者 JSON_SET() 函數：

JSON_SET()，JSON_INSERT() 和JSON_REPLACE() 函數的區別：

• JSON_SET() 支持替換現有值，并且支持添加不存在的值。
• JSON_INSERT() 插入值而不替換現有值。
• JSON_REPLACE() 僅替換現有值。

還有一些其他的函數，詳見對應官方文檔。