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

2022-09-06 18:08:23

插入資料：insert操作

在json_start表中我們新增了一個json型別的欄位：person;下面我們就來插入一些資料，

json型別的資料插入方式和普通資料插入方式一樣，不同的是插入的是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資料的大小也是有限制的，預設大小和LONGTEXT ,LONGBLOB 一樣，同時他還受限於mysql設定項：max_allowed_packet. 的限制（預設為4M）.

我們觀察資料庫的結果：

資料已經新增到資料庫中了，我們試著在插入幾條資料：

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屬性，很顯然這是一個物件操作,執行語句如下：

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

原理就是將原來的json物件和要新增的物件合併，稍後將會描述這其中的合併規則。執行結果如下：

再給第三條記錄追加一個物件：

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()方法，該操作將指定的物件新增到陣列末尾，例如：

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為新值，例如：

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....)來格式化你的物件，例如：

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,...)方法,例如：

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)方法：

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

當然，你也可以使用萬用字元‘*’來實現多值查詢，例如：

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型別:

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;

一、插入一條資料：

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、根據JSON中的某個欄位查詢表中記錄：

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

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() 函數：

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() 函數：

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() 僅替換現有值。

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

到此這篇關於Mysql 5.7 新特性之 json 型別的增刪改查的文章就介紹到這了,更多相關Mysql json 型別的增刪改查內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

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

目錄

插入資料：insert操作

更新記錄：update 追加內容：

更新記錄：追加內容到陣列：

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

刪除記錄：

查詢記錄：

擴充套件：

一、插入一條資料：

1、查詢 JSON 中的某個欄位：

2、根據JSON中的某個欄位查詢表中記錄：

二、修改 JSON

1、修改 JSON 中的某個欄位：

2、往 JSON 中插入一個新的欄位：

熱門文章

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

目錄

插入資料 ：insert操作

更新記錄：update 追加內容：

更新記錄：追加內容到陣列：

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

刪除記錄：

查詢記錄：

擴充套件：

一、插入一條資料：

1、查詢 JSON 中的某個欄位：

2、根據JSON中的某個欄位查詢表中記錄：

二、修改 JSON

1、修改 JSON 中的某個欄位：

2、往 JSON 中插入一個新的欄位：

熱門文章

插入資料：insert操作