一 前言
MySQL 5.7.8 之後 支援 JSON （由rfc7159規定）資料類型，其能在字段中使用json 類型，做到了自動校驗是否為json類型資料，否則插入資料會報異常；其次，儲存json資料内部做到了優化儲存，能夠快速讀取json類型資料，比如無需将二進制json轉為文本形式後讀取；
《SQL-你真的了解什麼SQL麼？》
《SQL-小白最佳入門sql查詢一》
《SQL-小白最佳入門sql查詢二》
《SQL- 多年開發人員都不懂的插入與更新删除操作注意點》
《SQL-SQL事物操作》
《SQL-Mysql資料類型》
《SQL-mysql視圖的前世今生》
《SQL-mysql儲存過程》
《SQL-mysql遊标與觸發器》
《SQL-mysql使用者權限管理》
《SQL-mysql架構入門》
《SQL-mysql鎖等待與死鎖》
公衆号：
知識追尋者知識追尋者(Inheriting the spirit of open source, Spreading technology knowledge;)
二 操作 JOSN
建表語句如下，為 area 字段 聲明為 JSON 類型；
CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '訂單編号',
`order_name` varchar(255) DEFAULT NULL COMMENT '訂單名稱',
`create_time` datetime DEFAULT NULL COMMENT '建立時間',
`year` year(4) DEFAULT NULL COMMENT '年份',
`area` json DEFAULT NULL COMMENT '地區',
PRIMARY KEY (`id`),
UNIQUE KEY `order_name` (`order_name`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; MySQL 中支援 json 對象 和json 數組，他們之間可以互相嵌套；json類似map，在java中json就是map得實作類，初學者若不懂何為json，就當作map來用，即儲存 key - value 形式得資料結構；
注意點是 json 資料 得key 必須是字元串，可以有key無value；
2.1 插入 josn資料
插入 json 對象，east 得值 為 50 ， sourth 值為65 得 json對象；
INSERT INTO `order`(`order_name`, `year`, `area`)
VALUES ('荷小花的訂單', 2020, '{"east": "50", "south": "65"}'); 等效于使用
JSON_OBJECT
函數， 示例如下
INSERT INTO `order`(`order_name`, `year`, `area`)
VALUES ('荷小花的訂單', 2020, JSON_OBJECT("east", "50", "south", "65")); 插入 json 數組
INSERT INTO `order`(`order_name`, `year`, `area`)
VALUES ('荷小花的訂單', 2020, '[{"east": "50", "south": "65"}]'); 等效于使用
JSON_ARRAY
函數，示例如下
INSERT INTO `order`(`order_name`, `year`, `area`)
VALUES ('荷小花的訂單', 2020, JSON_ARRAY("east", "50", "south", "65")); 2.2 查詢json資料
使用 column - path 路徑符
->
查詢 指定key 得值，
select order_name, area -> '$.east' from `order` 注意，如果json 資料中不存在 east 得鍵，則列出為null值
輸出如下
--------- ------
荷小花的訂單 "50"
荷小花的訂單 也可以使用 ->> 符号, 不同之處是使用
->>
更加直覺，輸出得json資料最外層不會攜帶雙引号，内層資料中存在雙引号不會有反斜杠轉義；
select order_name, area ->> '$.east' from `order` 使用 單引号 代替 雙引号
select order_name, area ->> "$.east" from `order`
我們 也可以使用
JSON_EXTRACT
函數 達到同樣得效果；
SELECT order_name,JSON_EXTRACT(area, '$.east') from `order`;
2.3 修改json資料
使用
JSON_SET
設定 json key 得 值
id = 1 得 area 資料如下
{"north": "55", "south": "66"} 現在将south 值改為 60 的語句示例如下
update `order` set area = json_set(area, '$[0].south', '60') where id = '1' 其中 $[0] 代表 json 中的第一個對象 ,以此類推 $[1] 為 josn 中的第二個對象；
示例
["6","2",{"east": "50", "south": "65"}] $[0] 為"6"，$[1] 為 "2" , $[2] 為 {"east": "50", "south": "65"}; $[2].east 為 "50" , 或者
$[2][1]
;
如果上面$[*] 表達式式了解困難也可以使用如下方式
update `order` set area = json_set(area, '$.south', '60') where id = '1' tip: 如果更改整個json值 與 平時的更新資料方式一緻
2.4 删除json中的資料
使用 json_remove 可以達到效果；
示例: 删除json 中的 south 鍵
update `order` set area =json_remove(area, '$.south') where id = '1' 三 jsom函數
3.1 cast
cast 函數 是特殊函數，可以使用
CAST(expr AS type)
函數進行資料類型得轉換，此函數 與 convert 用法 類似 ，即 期望得表達式轉為期望得類型；
比如 将 字元串
知識追尋者
從預設類型轉為utf8類型
SELECT CONVERT('知識追尋者' USING utf8); 如下情況下查詢是字元串，非json資料
select '{"east": "50", "south": "65"}' as str
再來看看 cast 函數使用，将字元串轉為json 類型
select cast( '{"east": "50", "south": "65"}' as json ) 3.2 JSON_TYPE
JSON_TYPE 函數 會嘗試 去解析 參數為json值，
示例
select JSON_TYPE('{"east": "50", "south": "65"}') 得到結果為json 對象
OBJECT 示例
select JSON_TYPE('[{"east": "50", "south": "65"}]') 得到結果為json數組
ARRAY 示例
select JSON_TYPE('hello') 輸出為異常，無效得json
Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0. 3.3 JSON_MERGE
JSON_MERGE 函數 即 将合并多個json文檔； 合并規則如下
- 如果都是json array，合并為json array；
- 如果都是json object，合并為json object；
- 如果有多種類型資料，則将非json array的元素封裝成json array再按照如上規則進行合并；
示例
select json_merge('["west","20"]', '{"east": "50", "south": "65"}')
輸出
["west", "20", {"east": "50", "south": "65"}] 3.4 JSON_VALID
JSON_VALID 函數 為 校驗是否是json 函數，是傳回 1 ，否則 傳回0；
示例如下，傳回1；
SELECT JSON_VALID('{"east": "50", "south": "65"}') 示例如下，傳回 0；
SELECT JSON_VALID('hello')
3.5 JSON_INSERT
JSON_INSERT 函數 向 json 中添加新的值，不會改變已經存在的值；
id = 1 的資料如下
{"north": "55"} 更新語句如下
update `order` set area =JSON_INSERT(area, '$.north', 55 ,'$.south', "60") where id = '1' 更改結果如下
{"north": "55", "south": "60"} 3.6 JSON_REPLACE
JSON_REPLACE 替換現有的值，如果存在新的值不會添加；
id = 1 的資料如下
{"north": "55", "south": "60"} 更新語句如下
update `order` set area =JSON_REPLACE (area, '$.north', "50" ,'$.east', "60")
where id = '1'
更改結果如下
{"north": "50", "south": "60"} 3.7 JSON_SEARCH
JSON_SEARCH ， 傳回路徑，支援傳回單個和傳回多個；
id = 1 的資料如下
[{"north": "50", "south": "60"}, {"north": "50", "south": "70"}] 查詢 一個值為 50 的key路徑
select JSON_SEARCH(area, 'one', '50') from `order` where id = '1'
輸出
"$[0].north" 查詢 所有值為 50 的key路徑
select JSON_SEARCH(area, 'all', '50') from `order` where id = '1' 輸出
["$[0].north", "$[1].north"] 3.8 JSON_KEYS
傳回json頂級值底下所有key
id = 1 的資料如下
[{"north": "50", "south": "60"}, {"north": "50", "south": "70"}] 查詢json資料中第一個json對象所有的key
select JSON_KEYS(area, '$[0]') from `order` where id = '1' 輸出
["north", "south"] ![json 插入資料_MongoDB如何一次插入多條json資料背景解決方式總結[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)