MySQL實戰——JSON資料類型

MySQL JSON類型簡介

JavaScript Object Notation(JSON)是一種輕量級的、基于文本的，獨立于語言的資料交換格式。它源自ECMAScript程式設計語言标準。

對于MySQL資料庫，我們往往是将它作為一種關系型資料庫，存放關系型資料庫二維表。其實，從MySQL 5.7.8開始，MySQL已支援JSON資料類型，能夠高效通路JSON文檔中的資料。可以說是向着NoSQL存儲功能邁出了一步。

MySQL 8 大幅改進了對 JSON 的支援，為 schema-less 模式的 JSON 文檔提供了多文檔事務支援和完整的 ACID 合規性。還添加了基于路徑查詢參數從 JSON 字段中抽取資料的JSON_EXTRACT()函數，以及用于将資料分别組合到 JSON 數組和對象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG()聚合函數。

MySQL資料庫原本可以将JSON格式的字元串當做String類型存儲，但與之相比，JSON資料類型有以下優勢：

(1)自動驗證存儲在JSON列中的JSON文檔。無效的文檔會産生錯誤。

(2)優化了存儲格式。存儲在JSON類型中的JSON文檔被轉換為允許對文檔元素進行快速讀取通路的内部格式。當讀取以這種二進制格式存儲的JSON值時，不需要從文本内容中解析該值，而能夠直接通過鍵或數組索引查找子對象或嵌套值。

存儲JSON文檔所需的空間與LONGBLOB或LONGTEXT大緻相同；MySQL使用utf8mb4字元集和utf8mb4_bin排序規則處理JSON類型的資料，因為utf8mb4_bin是二進制排序規則，是以JSON值是區分大小寫的。

下面介紹JSON類型資料的建立、查詢、修改以及相關内置函數。

建立

首先建立一個帶有JSON類型字段的表，在一個表中，基本資料類型和JSON類型是可以共存的。示例為了簡單起見，建立一個隻包含JSON類型字段的表，如下：

create table sys_doc (doc json);

有兩種方法将JSON資料存入該表。

• 第一種方法，使用MySQL JSON_OBJECT()函數，這種方式下，若JSON值原本存在雙引号，需要使用反斜杠對每個引号字元進行轉義，例如，将如下這個鍵值對作為JSON資料存入表中：

mascot: The MySQL mascot is a dolphin named "Sakila".

INSERT語句為：

//JSON_OBJECT()函數構造JSON資料對象
//JSON資料内容中原本的引号需要用反斜杠轉義
INSERT INTO sys_doc VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));           

• 第二種方法，将JSON字元串作為參數傳入INSERT語句，這種情況下，需要使用雙反斜杠轉義JSON值中原本存在的雙引号，如下所示：

//直接将JSON字元串存入JSON類型
//使用雙反斜杠可以防止MySQL執行轉義處理，而是将字元串文本傳遞給存儲引擎進行處理。
INSERT INTO sys_doc VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');           

通過執行SELECT，可以看到查詢出的JSON列值中存在反斜杠，如下所示：

mysql> SELECT doc FROM sys_doc;
+---------------------------------------------------------+
| doc |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+           

若要查找這個鍵值對對應的值，可以使用列路徑操作符->，如下：

select doc->"$.mascot" from sys_doc;           

查詢結果：

"Our mascot is a dolphin named \"Sakila\"."

這個查詢結果保留了反斜杠以及值起始位置的引号。要想隻顯示所需的值，而不包括起始位置引号和轉義符，可以使用内聯路徑運算符->>，如下所示：

select doc->>"$.mascot" from sys_doc;           

查詢結果：

Our mascot is a dolphin named "Sakila".

查詢

将下面這個JSON對象存入上文所建資料庫表sys_doc，并以這個JSON對象展示相關JSON函數使用(該示例JSON對象表示了一個學生的聯考成績記錄，包括姓名、年齡、總分、國文數學英語以及理綜各科分數)：

{
    "name":"王小虎",
    "age":17,
    "totalScore":582,
    "gender":"男",
    "chinese":120,
    "math":105,
    "english":118,
    "science":[
        88,
        85,
        66
    ]
}           

存入以上JSON資料的INSERT語句為：

//JSON_OBJECT()函數建立JSON資料對象
INSERT INTO sys_doc VALUES (JSON_OBJECT('name', '王小虎', 'age', 17, 'totalScore', 582, 'gender', '男', 'chinese', 120, 'math', 105, 'english', 118, 'science', JSON_ARRAY(88, 85, 66)));           

• 查詢“王小虎”同學的總分：

select JSON_EXTRACT(doc, "$.totalScore") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

• 查詢“王小虎”同學的實體分數(假設"science":[88,85,66]分别表示理科綜合中的實體、化學、生物各科的分數)：

select JSON_EXTRACT(doc, "$.science[0]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

可以看到，通路數組元素的位置索引是以0開頭的整數。

• 查詢“王小虎”同學的理綜各科分數，以下三種方式都實作了相同的效果：

//數組元素索引與to關鍵字配合使用
select JSON_EXTRACT(doc, "$.science[0 to 2]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';
//數組元素索引last關鍵字
select JSON_EXTRACT(doc, "$.science[last-2 to last]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';
//表示全部數組元素的通配符*
select JSON_EXTRACT(doc, "$.science[*]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

• 除了JSON_EXTRACT()函數，還可以用JSON_VALUE()函數，如下：

//JSON_VALUE()函數查詢某個字段的值
select JSON_VALUE(doc, "$.science") FROM sys_doc WHERE JSON_VALUE(doc, "$.name") = '王小虎';           

• 查詢出JSON文檔中所有的KEY值：

//JSON_KEYS()函數列出JSON中所有key
select JSON_KEYS(doc) from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

• 查詢出JSON文檔中所有的VALUE值：

//通配符查出JSON中所有value
select JSON_EXTRACT(doc, "$.*") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

這些JSON相關的函數中，$表示正在操作的文檔。下文還将展示更多範例。

修改

• JSON_REPLACE()函數，将“王小虎”的年齡(age)改為20，“生物”分數修改為100：

//JSON_REPLACE()函數
update sys_doc set doc = JSON_REPLACE(doc, '$.age', 20, '$.science[last]', 100) WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

• JSON_SET()函數，将年齡(age)改為21，“生物”分數修改為99：

//JSON_SET()函數
update sys_doc set doc = JSON_SET(doc, '$.age', 21, '$.science[last]', 99) WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

此外，JSON_INSERT()函數也可以實作修改功能，他們三者的差別是函數處理文檔中現有值和不存在值的方式不同。具體如下：

1. JSON_SET()将替換現有路徑的值，并為不存在的路徑添加值。
2. JSON_INSERT()添加新值，但不替換現有值。
3. JSON_REPLACE()替換現有值并忽略新值。

删除

JSON_REMOVE()實作對JSON文檔内容删除，參數為一個或多個指定要從文檔中删除的值的路徑。

• 删除“王小虎”的年齡(age)、生物科目分數，如下：

//JSON_REMOVE()函數
update sys_doc set doc = JSON_REMOVE(doc, '$.age', '$.science[last]') WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';           

删除後的文檔如下，沒有了“age”字段以及“science”字段中的最後一個分數：

{
    "math": 105,
    "name": "王小虎",
    "gender": "男",
    "chinese": 120,
    "english": 118,
    "science": [
        88,
        85
    ],
    "totalScore": 582
}             

格式化

JSON_PRETTY()以可讀格式列印JSON文檔，例如：

select JSON_PRETTY('{"name":{"CN":"張三", "EN":"zhangsan"},"science":[120,98,90]}');           

将會輸入下面格式化的JSON文檔：

{
    "name": {
        "CN": "張三",
        "EN": "zhangsan"
    },
    "science": [
        120,
        98,
        90
    ]
}