Mysql?5.7?新特性之?json?類型的增刪改查操作和用法
近幾年來，nosql大行其道，json更是火的一塌糊涂，作為數(shù)據(jù)庫的元老，mysql在5.7版本中添加了對json數(shù)據(jù)的支持。這片博客主要介紹mysql json類型的增刪改查等基本操作的用法。
首先創(chuàng)建一個數(shù)據(jù)庫learnjson，并創(chuàng)建一張表json_start：
create table json_start ( id int auto_increment PRIMARY KEY not null, person json not NULL );
插入數(shù)據(jù) ：insert操作
在json_start表中我們添加了一個json類型的字段：person;下面我們就來插入一些數(shù)據(jù)，
json類型的數(shù)據(jù)插入方式和普通數(shù)據(jù)插入方式一樣，不同的是插入的是json字符串:
INSERT INTO json_start VALUES(null,'{"coder":[{"name":"張三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
"teacher":[{"name":"劉二","age":"22","gender":"女"},{"name":"趙三","age":"23","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
"student":[{"name":"張三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');需要注意的是，在插入json類型的字符串時，mysql會對json字符串進行格式校驗，如果格式有誤，就無法插入，并且默認情況下，json數(shù)據(jù)的大小也是有限制的，默認大小和LONGTEXT ,LONGBLOB 一樣，同時他還受限于mysql配置項：max_allowed_packet. 的限制（默認為4M）.
我們觀察數(shù)據(jù)庫的結果：
數(shù)據(jù)已經(jīng)添加到數(shù)據(jù)庫中了，我們試著在插入幾條數(shù)據(jù)：
INSERT INTO json_start VALUES(null,'{"handsome":[{"name":"山山","age":"18","gender":"男"},{"name":"張一山","age":"26","gender":"男"},{"name":"bug4j","age":"18","gender":"男"}],
"beauty":[{"name":"劉亦菲","age":"18","gender":"女"},{"name":"xixi","age":"23","gender":"女"},{"name":"周冬雨","age":"15","gender":"女"}],
"fatty":[{"name":"zhangqi","age":"38","gender":"男"},{"name":"pangci","age":"30","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');
INSERT INTO json_start VALUES(null,
'[{"name":"奔馳","model":"C260","price":"200RMB"},
{"name":"勞斯萊斯","model":"定制版","price":"166RMB"},
{"name":"奧迪","model":"A6","price":"200000000RMB"}]'
);結果：
你會發(fā)現(xiàn)，雖然最后一條記錄的json內(nèi)容和其他兩條記錄的內(nèi)容完全不一樣甚至結構都不一樣，但是依然能插入成功，因為mysql在插入時只對格式進行校驗，格式通過了，就當成字符串存儲，并不會考慮與其他記錄是否保持一致，事實上這么做沒必要。但這同時引入了一個使用時需要注意的事項，就是在定義表的時候就必須盡可能的表以及字段的功能，數(shù)據(jù)結構明確清楚，并且避免一個字段多種數(shù)據(jù)混合存儲。
更新記錄：update 追加內(nèi)容：
mysqljson數(shù)據(jù)跟新操作根據(jù)json數(shù)據(jù)的類型范圍分為列表操作和對象操作兩種類型，都可以直接說那個merge操作。
下面我們試著給第一條記錄追加一個manager屬性，很顯然這是一個對象操作,執(zhí)行語句如下：
UPDATE json_start set person = json_MERGE(person,'{"manager":[{"name":"laoda","age":"38","gender":"男"}]}') where id=1;原理就是將原來的json對象和要添加的對象合并，稍后將會描述這其中的合并規(guī)則。執(zhí)行結果如下：
再給第三條記錄追加一個對象：
UPDATE json_start set person = json_merge(person,'[{"name":"大眾","model":"k1","price":"600000000RMB"}]') where id=3;更新記錄：追加內(nèi)容到數(shù)組：
追加到數(shù)組可以使用JSON_APPEND()(mysql 5.7.9或更早的版本) 或者JSON_ARRAY_APPEND()方法，該操作將指定的對象添加到數(shù)組末尾，例如：
update json_start set person = json_ARRAY_APPEND(person,"$",JSON_OBJECT("name","瑪莎拉蒂","model","ah","price","168RMB")) where id=3;執(zhí)行結果：
更新記錄：update 修改json屬性：
mysql提供一個非常靈活的本地方法：json_set(doc,path,value),其中doc為要修改的json列，path為要修改的屬性的對象定位表達式，value為新值，例如：
UPDATE json_start set person = json_set(person,'$.coder[0].name','張三三三三三') where id = 1
結果為：
需要注意的是，當使用json_set（）方法去修改一整個對象或者數(shù)組時，mysql或將value當成字符串來解析保存，當你的value中包含”時，或自動進行轉義，這樣存儲起來很浪費空間，同時也會產(chǎn)生問題，如果需要修改整個對象，建議使用Json_object(key，value，key2,value2....)來格式化你的對象，例如：
UPDATE json_start set person = json_set(person,'$.coder[0]',JSON_OBJECT("age",18,"name","張sisisisi","gender","男")) where id = 1;
UPDATE json_start set person = json_set(person,"$[0]",JSON_OBJECT("age",18,"name","張wuwuwuuwu","gender","男")) where id = 3;執(zhí)行結果為：
刪除記錄：
刪除記錄使用JSON_REMOVE(doc,path1，path2,...)方法,例如：
update json_start set person = json_REMOVE(person,"$[0]") where id = 3; update json_start set person = json_REMOVE(person,"$.coder[0]") where id = 1;
執(zhí)行結果為：
查詢記錄：
查詢所有的屬性key,使用json_keys(doc)方法：
select id,json_keys(person) from json_start
結果如下：
查詢指定的記錄中的某個屬性或者值，使用Json_extract(doc,path)方法，例如：
select id,json_extract(person,"$.coder[0].name") from json_start where id = 1
結果如下：
或者：
select id,json_extract(person,"$.coder[0]") from json_start where id = 1
當然，你也可以使用通配符‘*’來實現(xiàn)多值查詢，例如：
select id,json_extract(person,"$.coder[*].name") from json_start where id = 1 UNION All select id,json_extract(person,"$.*[*].name") from json_start where id = 1
結果如下：
需要注意的是，查詢返回的結果始終是一個包含查詢結果的數(shù)組！
擴展：
創(chuàng)建一個表 t1，basic_info 字段為JSON類型:
CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `basic_info` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
一、插入一條數(shù)據(jù)：
INSERT INTO `t1`(`id`, `basic_info`) VALUES (1, '{\"age\": 9, \"name\": \"小明\", \"class\": 3}');1、查找 JSON 中的某個字段：
比如查詢 id=1 的 basic_info 中的 name 字段，可以用以下兩種方式：
select basic_info->'$.name' from t1 where id =1; //或者 select JSON_EXTRACT(basic_info, '$.name') from t1 where id =1;
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、根據(jù)JSON中的某個字段查詢表中記錄：
比如查詢name為小明的記錄：
select * from t1 where basic_info->'$.name' = '小明'; //或者 select * from t1 where JSON_EXTRACT(basic_info,'$.name') = '小明';
檢索JSON數(shù)據(jù)的方法，文檔：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() 函數(shù)：
update t1 set basic_info = JSON_REPLACE(basic_info, '$.age', 10) where id =1; //或者 update t1 set basic_info = JSON_SET(basic_info, '$.age', 10) where id =1;
2、往 JSON 中插入一個新的字段：
比如往 basic_info 中插入一個性別“gender”字段：
可以使用JSON_INSERT() 或者 JSON_SET() 函數(shù)：
update t1 set basic_info = JSON_INSERT(basic_info, '$.gender', '男') where id =1; //或者 update t1 set basic_info = JSON_SET(basic_info, '$.gender', '男') where id =1;
JSON_SET()，JSON_INSERT() 和JSON_REPLACE() 函數(shù)的區(qū)別：
- JSON_SET() 支持替換現(xiàn)有值，并且支持添加不存在的值。
- JSON_INSERT() 插入值而不替換現(xiàn)有值。
- JSON_REPLACE() 僅替換現(xiàn)有值。
還有一些其他的函數(shù)，詳見對應官方文檔。
到此這篇關于Mysql 5.7 新特性之 json 類型的增刪改查的文章就介紹到這了,更多相關Mysql json 類型的增刪改查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
下面小編就為大家?guī)硪黄狹ySQL按照漢字的拼音排序簡單實例。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-01-01
這篇文章主要介紹了阿里云centos7安裝mysql8.0.22的詳細教程，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-11-11
這篇文章主要介紹了MYSQL中的json數(shù)據(jù)操作,主要包括基礎查詢操作，JSON函數(shù)操作，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
MySQL如何快速批量插入1000w條數(shù)據(jù)
這篇文章主要給大家介紹了關于MySQL如何快速批量插入1000w條數(shù)據(jù)的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
MySQL是一個開源的關系型數(shù)據(jù)庫管理系統(tǒng)，支持多種操作語言，其中最基礎、最常用的命令之一就是SELECT語句，所以本文就來和大家聊聊Select查詢語句的幾個高級用法吧2023-05-05
這篇文章主要介紹了用HAProxy來檢測MySQL復制的延遲的教程,HAProxy需要使用到PHP腳本,需要的朋友可以參考下2015-04-04
MySQL數(shù)據(jù)類型中DECIMAL的用法實例詳解
這篇文章主要介紹了MySQL數(shù)據(jù)類型中DECIMAL的用法實例詳解的相關資料,希望通過本文能幫助到大家，需要的朋友可以參考下2017-10-10
IDEA中的庫（Libraries）就是用來存放外部jar包,我們的項目或模塊需要某些jar包時,可以從這里把包導入到模塊依賴（Dependencies）中,本文給大家介紹Idea 如何導入Mysql8.0驅動jar包,感興趣的朋友一起看看吧2023-12-12
MySQL大量臟數(shù)據(jù)如何只保留最新的一條(最新推薦)
這篇文章主要介紹了MySQL大量臟數(shù)據(jù),如何只保留最新的一條,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04
mysql內(nèi)連接,連續(xù)兩次使用同一張表,自連接方式
這篇文章主要介紹了mysql內(nèi)連接,連續(xù)兩次使用同一張表,自連接方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12