隨著 NoSQL 數(shù)據(jù)庫的興起，JSON 作為一種輕量級的數(shù)據(jù)交換格式受到了廣泛的關注。為了滿足現(xiàn)代應用程序的需求，MySQL 8引入了原生的 JSON 數(shù)據(jù)類型，提供了一系列強大的 JSON 函數(shù)來處理和查詢 JSON 數(shù)據(jù)。本文將深入探討 MySQL 8 中JSON 類型的特性、函數(shù)、索引以及實際應用場景。

1. 引言

在早期的 MySQL 版本中，開發(fā)者通常將 JSON 數(shù)據(jù)以字符串的形式存儲在數(shù)據(jù)庫中，這導致了查詢效率低下和數(shù)據(jù)處理復雜。為了解決這個問題，MySQL 8 引入了原生的 JSON 數(shù)據(jù)類型，允許開發(fā)者以結構化的方式存儲和查詢 JSON 數(shù)據(jù)。

2. JSON 數(shù)據(jù)類型特性

• 驗證：當插入或更新 JSON 列時，MySQL 會自動驗證數(shù)據(jù)的 JSON 格式，確保數(shù)據(jù)的完整性。
• 優(yōu)化存儲：JSON 數(shù)據(jù)類型以二進制格式存儲，相比純文本存儲更加高效。
• 靈活性：JSON 列可以存儲數(shù)組、對象、嵌套結構等，為數(shù)據(jù)的表示提供了極大的靈活性。

3. JSON 函數(shù)

MySQL 8 提供了一系列內(nèi)置函數(shù)來操作和查詢 JSON 數(shù)據(jù)：

• 提取數(shù)據(jù)：使用 JSON_EXTRACT() 函數(shù)可以從 JSON 文檔中提取指定的數(shù)據(jù)片段。
• 修改數(shù)據(jù)：JSON_INSERT()、JSON_REPLACE() 和 JSON_REMOVE() 函數(shù)允許你向 JSON 文檔中添加、替換或刪除數(shù)據(jù)。
• 創(chuàng)建 JSON：JSON_ARRAY() 和 JSON_OBJECT() 函數(shù)用于創(chuàng)建 JSON 數(shù)組和對象。
• 查詢功能：JSON_LENGTH()、JSON_KEYS()、JSON_VALID() 等函數(shù)，用于獲取 JSON 數(shù)據(jù)的長度、鍵或驗證 JSON 數(shù)據(jù)的有效性。
• 其他函數(shù)，如 JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() 等等。

下面的例子看看每個函數(shù)的具體使用方法：
首先，我們創(chuàng)建一個名為 json_example 的表，并插入一條 JSON 數(shù)據(jù)：

CREATE TABLE json_example (  id INT AUTO_INCREMENT PRIMARY KEY,  data JSON  
);  INSERT INTO json_example (data) VALUES (  '{  "name": "John Doe",  "age": 30,  "address": {  "street": "123 Main St",  "city": "Anytown",  "zip": "12345"  },  "phoneNumbers": ["123-456-7890", "987-654-3210"],  "isActive": true  }'  
);

表 json_example 中有一條包含 JSON 數(shù)據(jù)的記錄。接下來，我們將使用不同的 JSON 函數(shù)來查詢和修改這個數(shù)據(jù).

3.1 JSON_EXTRACT()

• 提取 JSON 數(shù)據(jù)中的特定部分

-- 提取 name 字段的值  
SELECT JSON_EXTRACT(data, '$.name') AS extracted_name FROM json_example;  
-- 結果: "John Doe"  -- 提取 address 對象的 city 字段的值  
SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM json_example;  
-- 結果: "Anytown"

3.2 JSON_INSERT()

• 向 JSON 數(shù)據(jù)中插入新的部分，如果路徑已存在則不會替換。

-- 在 phoneNumbers 數(shù)組后面插入一個新的電話號碼  
UPDATE json_example  
SET data = JSON_INSERT(data, '$.phoneNumbers[2]', '555-123-4567');  
-- 此時 phoneNumbers 變?yōu)?["123-456-7890", "987-654-3210", "555-123-4567"]

3.3 JSON_REPLACE()

• 替換 JSON 數(shù)據(jù)中的部分，如果路徑不存在則不會添加。

-- 替換 name 字段的值  
UPDATE json_example  
SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');  
-- 此時 name 變?yōu)?"Jane Smith"

3.4 JSON_REMOVE()

• 從 JSON 數(shù)據(jù)中移除指定的部分。

-- 移除 phoneNumbers 數(shù)組中的第一個電話號碼  
UPDATE json_example  
SET data = JSON_REMOVE(data, '$.phoneNumbers[0]');  
-- 此時 phoneNumbers 變?yōu)?["987-654-3210", "555-123-4567"]

3.5 JSON_ARRAY() 和 JSON_OBJECT()

• 創(chuàng)建 JSON 數(shù)組和對象

-- 創(chuàng)建一個新的 JSON 數(shù)組  
SELECT JSON_ARRAY('a', 1, TRUE);  
-- 結果: ["a", 1, true]  -- 創(chuàng)建一個新的 JSON 對象  
SELECT JSON_OBJECT('key1', 'value1', 'key2', 2);  
-- 結果: {"key1": "value1", "key2": 2}
JSON_LENGTH() - 獲取 JSON 文檔或數(shù)組的長度。
sql
-- 獲取 phoneNumbers 數(shù)組的長度  
SELECT JSON_LENGTH(data->'$.phoneNumbers') AS phone_numbers_length FROM json_example;  
-- 結果: 2 (因為 phoneNumbers 數(shù)組現(xiàn)在有兩個元素)

3.6 JSON_KEYS()

• 獲取 JSON 對象的所有鍵

-- 獲取 JSON 對象的所有鍵  
SELECT JSON_KEYS(data) AS object_keys FROM json_example;  
-- 結果: ["name", "age", "address", "phoneNumbers", "isActive"]

3.7 JSON_VALID()

• 驗證 JSON 數(shù)據(jù)的有效性。

-- 驗證 data 列是否包含有效的 JSON  
SELECT JSON_VALID(data) AS is_valid_json FROM json_example;  
-- 結果: 1 (表示 true，因為 data 列包含有效的 JSON)

3.8 JSON_QUOTE() 和 JSON_UNQUOTE()

• 將字符串轉換為 JSON 格式的字符串，以及反向操作。

假設json_example 表中存在這樣一條數(shù)據(jù)

INSERT INTO json_example (data) VALUES (  '{  "name": "John",  "interests": ["reading", "music"],  "friends": [  {"name": "Alice", "age": 28},  {"name": "Bob", "age": 32}  ]  }'  
);

現(xiàn)在我們將使用上述函數(shù)對這條數(shù)據(jù)進行操作：

-- 使用 JSON_QUOTE 將普通字符串轉換為 JSON 字符串  
SELECT JSON_QUOTE('Hello, World!') AS quoted_string;  
-- 結果: ""Hello, World!""  -- 使用 JSON_UNQUOTE 將 JSON 字符串轉換回普通字符串  
SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;  
-- 結果: Hello, World!

請注意，在實際的數(shù)據(jù)列上使用這些函數(shù)時，你通常會對已存儲的 JSON 值或要插入的值進行操作。

3.9 JSON_CONTAINS()

• 檢查 JSON 文檔是否包含指定的值。

-- 檢查 interests 數(shù)組是否包含 "reading"  
SELECT JSON_CONTAINS(data->'$.interests', '"reading"') AS contains_reading FROM json_example;  
-- 結果: 1 (表示 true，因為 interests 數(shù)組包含 "reading")

注意，因為 JSON 中的字符串是被雙引號包圍的，所以我們在查詢時也需要對搜索的字符串值加上雙引號。

3.9 JSON_CONTAINS_PATH()

• 檢查 JSON 文檔是否包含指定的路徑。

-- 檢查是否存在 friends 數(shù)組中的對象的 name 路徑  
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
-- 結果: 1 (表示 true，因為存在該路徑)

3.10 JSON_ARRAY_APPEND()

• 向 JSON 數(shù)組追加元素。

-- 向 interests 數(shù)組追加 "traveling"  
UPDATE json_example  
SET data = JSON_SET(data, '$.interests[2]', 'traveling');  
-- 注意：這里使用了 JSON_SET，因為 JSON_ARRAY_APPEND 需要指定路徑到具體數(shù)組  
-- 在 MySQL 8.0.17 及更高版本中，可以使用 JSON_ARRAY_APPEND 正確地追加元素  
-- 例如: JSON_ARRAY_APPEND(data, '$.interests', 'traveling')

注意：上面的例子中使用了 JSON_SET 而不是 JSON_ARRAY_APPEND，因為在 MySQL 8.0.17 之前，JSON_ARRAY_APPEND 的語法有些不同，它要求指定路徑到一個具體的數(shù)組元素。從 8.0.17 開始，JSON_ARRAY_APPEND 可以正確地追加到數(shù)組末尾。
正確的 JSON_ARRAY_APPEND 用法如下：

-- 向 interests 數(shù)組追加 "traveling"（適用于 MySQL 8.0.17 及更高版本）  
UPDATE json_example  
SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');

3.11 JSON_ARRAY_INSERT()

• 在 JSON 數(shù)組的指定位置插入元素。

-- 在 interests 數(shù)組的第一個位置插入 "gaming"  
UPDATE json_example  
SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');  
-- 結果: interests 數(shù)組現(xiàn)在是 ["gaming", "reading", "music", "traveling"]

4. JSON 索引

為了提高查詢性能，MySQL 8 支持為 JSON 列創(chuàng)建索引。但由于 JSON 數(shù)據(jù)的靈活性，直接對整個 JSON 文檔創(chuàng)建索引并不高效。因此，MySQL 引入了虛擬列（Virtual Columns）的概念。

• 虛擬列：虛擬列允許你根據(jù) JSON 列中的值生成一個新的列，并為這個新列創(chuàng)建索引。這樣，當你根據(jù) JSON 數(shù)據(jù)中的某個字段進行查詢時，MySQL 可以使用索引來加速查詢。（關于虛擬列我將在之后的文章詳解）
• 創(chuàng)建索引：通過結合使用 JSON_EXTRACT() 函數(shù)和虛擬列，你可以輕松地為 JSON 數(shù)據(jù)中的特定字段創(chuàng)建索引。

基于上面的json_example 表，我們來看下為json字段創(chuàng)建索引

4.1 添加虛擬列：

我們將添加一個名為 first_interest 的虛擬列，該列將存儲 interests 數(shù)組的第一個元素。

ALTER TABLE json_example  
ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;

在這里，我們使用了 JSON_EXTRACT() 來獲取 interests 數(shù)組的第一個元素，并用 JSON_UNQUOTE() 去除引號，因為 JSON_EXTRACT() 返回的是 JSON 格式的字符串。

4.2 為虛擬列創(chuàng)建索引：

CREATE INDEX idx_first_interest ON json_example(first_interest);

現(xiàn)在，我們?yōu)?first_interest 列創(chuàng)建了一個索引，這將加速基于該列的查詢。

4.3 查詢優(yōu)化：

現(xiàn)在，我們可以基于 first_interest 列進行查詢，并利用索引來加速查詢過程。

SELECT * FROM json_example WHERE first_interest = 'reading';

由于我們?yōu)?first_interest 創(chuàng)建了索引，這個查詢將會更加高效。但是，請注意，這種方法僅適用于查詢 interests 數(shù)組的第一個元素。如果你需要查詢數(shù)組中的其他元素，你可能需要采用其他策略，比如使用全文搜索、倒排索引或者將 JSON 數(shù)據(jù)規(guī)范化到關系型結構中。

5. 實際應用場景

• 配置文件存儲：應用程序的配置信息通常以 JSON 格式表示。使用 MySQL 8 的 JSON 數(shù)據(jù)類型，你可以輕松地將這些配置信息存儲在數(shù)據(jù)庫中，并使用 JSON 函數(shù)進行查詢和修改。
• 日志記錄：日志條目通常以結構化的格式存儲，JSON 是一個理想的選擇。通過將日志數(shù)據(jù)存儲在 JSON 列中，你可以輕松地分析和查詢?nèi)罩緮?shù)據(jù)。
• 與前端集成：使用 JSON 與后端進行數(shù)據(jù)交換。使用 MySQL 8 的 JSON 支持，你可以簡化數(shù)據(jù)庫與前端之間的數(shù)據(jù)交互。

6. 注意事項

• 性能：雖然 MySQL 8 提供了對 JSON 的支持，但與傳統(tǒng)的關系型數(shù)據(jù)相比，JSON 查詢可能仍然不夠高效。
• 數(shù)據(jù)驗證：雖然 MySQL 會驗證 JSON 數(shù)據(jù)的格式，但它不會驗證數(shù)據(jù)的業(yè)務規(guī)則或完整性。
• 復雜性：JSON 數(shù)據(jù)的結構可能比傳統(tǒng)的關系型數(shù)據(jù)更復雜，這可能會增加查詢和維護的難度。

7. 結論

MySQL 8 的 JSON 數(shù)據(jù)類型為存儲和查詢 JSON 數(shù)據(jù)提供了強大的支持。通過內(nèi)置的 JSON 函數(shù)和虛擬列索引，開發(fā)者可以高效地處理 JSON 數(shù)據(jù)，滿足現(xiàn)代應用程序的需求。如果你正在開發(fā)需要存儲和查詢 JSON 數(shù)據(jù)的應用程序，不妨考慮使用 MySQL 8 的 JSON 功能來簡化你的工作。