<optgroup id="srizi"></optgroup>

<optgroup id="srizi"></optgroup>

系統城裝機大師 - 固鎮縣祥瑞電腦科技銷售部宣傳站!

當前位置:首頁 > 數據庫 > Mysql > 詳細頁面

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

時間:2022-09-11來源:www.jasafloorhardener.com作者:電腦系統城

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

首先創建一個數據庫learnjson,并創建一張表json_start:

1
2
3
4
create table json_start (
    id int auto_increment PRIMARY KEY not null,
    person json not NULL
);

插入數據 :insert操作

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

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

1
2
3
4
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數據的大小也是有限制的,默認大小和LONGTEXT ,LONGBLOB 一樣,同時他還受限于mysql配置項:max_allowed_packet. 的限制(默認為4M).

我們觀察數據庫的結果:

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

1
2
3
4
5
6
7
8
9
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"}]'
);

結果:

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

更新記錄:update 追加內容:

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

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

1 UPDATE json_start set person = json_MERGE(person,'{"manager":[{"name":"laoda","age":"38","gender":"男"}]}') where id=1;

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

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

1 UPDATE json_start set person = json_merge(person,'[{"name":"大眾","model":"k1","price":"600000000RMB"}]') where id=3;

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

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

1 update json_start set person = json_ARRAY_APPEND(person,"$",JSON_OBJECT("name","瑪莎拉蒂","model","ah","price","168RMB")) where id=3;

執行結果:

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

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

1 UPDATE json_start set person = json_set(person,'$.coder[0].name','張三三三三三') where id = 1

結果為:

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

1
2
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;

執行結果為:

刪除記錄: 

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

1
2
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;

執行結果為:

查詢記錄:

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

 

1 select id,json_keys(person) from json_start

結果如下:

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

1 select id,json_extract(person,"$.coder[0].name") from json_start where id = 1

結果如下:

或者:

1 select id,json_extract(person,"$.coder[0]") from json_start where id = 1

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

1
2
3
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

 結果如下:

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

擴展: 

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

1
2
3
4
5
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;

一、插入一條數據:

1 INSERT INTO `t1`(`id`, `basic_info`) VALUES (1, '{\"age\": 9, \"name\": \"小明\", \"class\": 3}');

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

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

1
2
3
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、根據JSON中的某個字段查詢表中記錄:

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

1
2
3
select * from t1 where basic_info->'$.name' = '小明';
//或者
select * from t1 where JSON_EXTRACT(basic_info,'$.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() 函數:

1
2
3
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() 函數:

1
2
3
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() 函數的區別:

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

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

分享到:

相關信息

  • Ubuntu安裝Mysql+啟用遠程連接的完整過程

    建立到服務器的遠程連接 用apt-get安裝mysql 設置root密碼 編輯配置文件監聽遠程連接 允許root賬號使用遠程連接 檢查ubuntu自帶的防火墻狀態 檢查云服務器廠商的防火墻狀態 測試連接...

    2022-06-20

  • MySQL數據庫實驗實現簡單數據庫應用系統設計

    一、實驗目的 二、實驗要求 三、實現內容及步驟 1、學生表:student 2、寢室表:dormitory 3、管理員表:admin 四、實驗總結...

    2022-06-20

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

久久大香久久久尹人尹人