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

1. 引言

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

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

• 驗(yàn)證：當(dāng)插入或更新 JSON 列時(shí)，MySQL 會(huì)自動(dòng)驗(yàn)證數(shù)據(jù)的 JSON 格式，確保數(shù)據(jù)的完整性。
• 優(yōu)化存儲(chǔ)：JSON 數(shù)據(jù)類型以二進(jìn)制格式存儲(chǔ)，相比純文本存儲(chǔ)更加高效。
• 靈活性：JSON 列可以存儲(chǔ)數(shù)組、對(duì)象、嵌套結(jié)構(gòu)等，為數(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ù)組和對(duì)象。
• 查詢功能：JSON_LENGTH()、JSON_KEYS()、JSON_VALID() 等函數(shù)，用于獲取 JSON 數(shù)據(jù)的長(zhǎng)度、鍵或驗(yàn)證 JSON 數(shù)據(jù)的有效性。
• 其他函數(shù)，如 JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() 等等。

下面的例子看看每個(gè)函數(shù)的具體使用方法：
首先，我們創(chuàng)建一個(gè)名為 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ù)來查詢和修改這個(gè)數(shù)據(jù).

3.1 JSON_EXTRACT()

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

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

3.2 JSON_INSERT()

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

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

3.3 JSON_REPLACE()

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

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

3.4 JSON_REMOVE()

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

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

3.5 JSON_ARRAY() 和 JSON_OBJECT()

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

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

3.6 JSON_KEYS()

• 獲取 JSON 對(duì)象的所有鍵

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

3.7 JSON_VALID()

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

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

3.8 JSON_QUOTE() 和 JSON_UNQUOTE()

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

假設(shè)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ù)對(duì)這條數(shù)據(jù)進(jìn)行操作：

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

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

3.9 JSON_CONTAINS()

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

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

注意，因?yàn)?JSON 中的字符串是被雙引號(hào)包圍的，所以我們?cè)诓樵儠r(shí)也需要對(duì)搜索的字符串值加上雙引號(hào)。

3.9 JSON_CONTAINS_PATH()

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

-- 檢查是否存在 friends 數(shù)組中的對(duì)象的 name 路徑  
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
-- 結(jié)果: 1 (表示 true，因?yàn)榇嬖谠撀窂?

3.10 JSON_ARRAY_APPEND()

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

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

注意：上面的例子中使用了 JSON_SET 而不是 JSON_ARRAY_APPEND，因?yàn)樵?MySQL 8.0.17 之前，JSON_ARRAY_APPEND 的語(yǔ)法有些不同，它要求指定路徑到一個(gè)具體的數(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ù)組的第一個(gè)位置插入 "gaming"  
UPDATE json_example  
SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');  
-- 結(jié)果: interests 數(shù)組現(xiàn)在是 ["gaming", "reading", "music", "traveling"]

4. JSON 索引

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

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

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

4.1 添加虛擬列

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

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

在這里，我們使用了 JSON_EXTRACT() 來獲取 interests 數(shù)組的第一個(gè)元素，并用 JSON_UNQUOTE() 去除引號(hào)，因?yàn)?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)建了一個(gè)索引，這將加速基于該列的查詢。

4.3 查詢優(yōu)化

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

SELECT * FROM json_example WHERE first_interest = 'reading';

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

5. 實(shí)際應(yīng)用場(chǎng)景

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

6. 注意事項(xiàng)

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

7. 結(jié)語(yǔ)

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

到此這篇關(guān)于MySQL JSON類型的功能與應(yīng)用的文章就介紹到這了,更多相關(guān)MySQL JSON類型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論