MySQL中JSON 函數(shù)的具體使用

更新時間：2025年07月24日 09:48:54 作者：尋找09之夏

在現(xiàn)代數(shù)據(jù)庫設(shè)計中,JSON 格式的數(shù)據(jù)因其靈活性和可擴展性而變得越來越受歡迎,MySQL 8.0 引入了許多強大的 JSON 函數(shù),使得處理 JSON 數(shù)據(jù)變得更加方便和高效,下面就來介紹一下

前言

在現(xiàn)代數(shù)據(jù)庫設(shè)計中，JSON 格式的數(shù)據(jù)因其靈活性和可擴展性而變得越來越受歡迎。MySQL 8.0 引入了許多強大的 JSON 函數(shù)，使得處理 JSON 數(shù)據(jù)變得更加方便和高效。本文將通過一個簡化的訂單表 orders，展示如何使用這些 JSON 函數(shù)來創(chuàng)建、搜索、修改和驗證 JSON 數(shù)據(jù)，從而優(yōu)化訂單管理系統(tǒng)。

1. 表結(jié)構(gòu)定義

1.1 創(chuàng)建訂單表 orders

首先，我們定義一個簡單的訂單表 orders，其中包含一個主鍵 id 和一個存儲訂單詳細信息的 JSON 字段 data。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

1.2 插入示例數(shù)據(jù)

接下來，我們插入一些示例數(shù)據(jù)，模擬不同水果的訂單信息。

INSERT INTO orders (DATA) VALUES
  ('{"fruit": "Apple", "quantity": 100, "price": 25.0, "labels": ["Fresh", "Sweet"]}'),
  ('{"fruit": "Banana", "quantity": 150, "price": 8.0, "labels": ["Ripe"]}'),
  ('{"fruit": "Cherry", "quantity": 120, "price": 15.0, "labels": ["Small"]}'),
  ('{"fruit": "Apple", "quantity": 50, "price": 12.5, "labels": ["Fresh", "Sweet"]}');

1.3 查詢?nèi)繑?shù)據(jù)

mysql> SELECT * FROM orders;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
|  2 | {"fruit": "Banana", "price": 8.0, "labels": ["Ripe"], "quantity": 150}           |
|  3 | {"fruit": "Cherry", "price": 15.0, "labels": ["Small"], "quantity": 120}         |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50}  |
+----+----------------------------------------------------------------------------------+

2. 使用 JSON 函數(shù)

2.1 創(chuàng)建 JSON 值

JSON_ARRAY(val1, val2, ...)
創(chuàng)建一個 JSON 數(shù)組。val1, val2, ... : 要包含在 JSON 數(shù)組中的值。

mysql> SELECT JSON_ARRAY('Apple', 'Banana', 'Cherry');
+-----------------------------------------+
| JSON_ARRAY('Apple', 'Banana', 'Cherry') |
+-----------------------------------------+
| ["Apple", "Banana", "Cherry"]           |
+-----------------------------------------+

JSON_OBJECT(key1, value1, key2, value2, ...)
創(chuàng)建一個 JSON 對象。key1, key2: JSON 對象的鍵；value1, value2: 與鍵相關(guān)聯(lián)的值。

mysql> SELECT JSON_OBJECT('fruit', 'Apple', 'quantity', 100, 'price', 25.0, 'labels', JSON_ARRAY('Fresh', 'Sweet'));
+-------------------------------------------------------------------------------------------------------+
| JSON_OBJECT('fruit', 'Apple', 'quantity', 100, 'price', 25.0, 'labels', JSON_ARRAY('Fresh', 'Sweet')) |
+-------------------------------------------------------------------------------------------------------+
| {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100}                      |
+-------------------------------------------------------------------------------------------------------+

2.2 搜索 JSON 值

JSON_EXTRACT(json_doc, path[, type])
從 JSON 文檔中選擇的數(shù)據(jù)返回與 path 參數(shù)匹配的部分。

mysql> SELECT id, JSON_EXTRACT(data, '$.price') AS price, JSON_EXTRACT(data, '$.labels') AS labels FROM orders ORDER BY price DESC;
+----+-------+--------------------+
| id | price | labels             |
+----+-------+--------------------+
|  1 | 25.0  | ["Fresh", "Sweet"] |
|  3 | 15.0  | ["Small"]          |
|  4 | 12.5  | ["Fresh", "Sweet"] |
|  2 | 8.0   | ["Ripe"]           |
+----+-------+--------------------+


mysql> SELECT id, JSON_EXTRACT(data, '$.labels[0]') AS label FROM orders;
+----+---------+
| id | label   |
+----+---------+
|  1 | "Fresh" |
|  2 | "Ripe"  |
|  3 | "Small" |
|  4 | "Fresh" |
+----+---------+

JSON_CONTAINS(target, candidate[, path])
函數(shù)用于檢查一個 JSON 文檔（target）是否包含另一個 JSON 文檔或值（candidate），或者檢查在指定的路徑（path）下是否包含該候選者。它返回一個整數(shù)來表示結(jié)果：如果找到匹配項，則返回 1；否則返回 0。

mysql> SELECT id, data FROM orders WHERE JSON_CONTAINS(data, '"Apple"', '$.fruit');
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50}  |
+----+----------------------------------------------------------------------------------+

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
函數(shù)返回 JSON 文檔中指定字符串的路徑。 json_doc，如果 search_str 或 path 參數(shù)為、 path 在文檔中不存在，或者 search_ 如果未找到 str，則返回。

mysql> SELECT id, JSON_SEARCH(data, 'one','%e%', 'a') FROM orders;
+----+-------------------------------------+
| id | JSON_SEARCH(data, 'one','%e%', 'a') |
+----+-------------------------------------+
|  1 | "$.fruit"                           |
|  2 | "$.labels[0]"                       |
|  3 | "$.fruit"                           |
|  4 | "$.fruit"                           |
+----+-------------------------------------+

mysql> SELECT id, JSON_SEARCH(data,'all' ,'%e%') FROM orders;
+----+-------------------------------------------+
| id | JSON_SEARCH(data,'all' ,'%e%')            |
+----+-------------------------------------------+
|  1 | ["$.fruit", "$.labels[0]", "$.labels[1]"] |
|  2 | "$.labels[0]"                             |
|  3 | "$.fruit"                                 |
|  4 | ["$.fruit", "$.labels[0]", "$.labels[1]"] |
+----+-------------------------------------------+

2.3 修改 JSON 值

JSON_SET(json_doc, path, val[, path, val] ...)
函數(shù)在 JSON 文檔中插入或更新數(shù)據(jù)并返回結(jié)果。如果任一參數(shù)是或 path，則返回（如果指定）。

mysql> UPDATE orders SET data = JSON_SET(data, '$.price', 26.0) WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
+----+----------------------------------------------------------------------------------+

JSON_REPLACE(json_doc, path, val[, path, val] ...)
函數(shù)替換 JSON 文檔中的現(xiàn)有值并返回結(jié)果。如果任何參數(shù)為，則返回。

mysql> UPDATE orders SET data = JSON_REPLACE(data, '$.quantity', 110) WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Fresh", "Sweet"], "quantity": 110} |
+----+----------------------------------------------------------------------------------+

mysql> UPDATE orders SET data = JSON_REPLACE(data, '$.labels[0]', 'Crisp?') WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Crisp?", "Sweet"], "quantity": 110} |
+----+----------------------------------------------------------------------------------+

JSON_REMOVE(json_doc, path[, path] ...)
函數(shù)從 JSON 文檔中刪除數(shù)據(jù)并返回結(jié)果。path 參數(shù)從左到右計算。通過評估一個路徑生成的文檔將成為評估下一個路徑所依據(jù)的新值。

mysql> UPDATE orders SET data = JSON_REMOVE(data, '$.quantity') WHERE id = 1;

mysql> SELECT * FROM orders;
+----+---------------------------------------------------------------------------------+
| id | data                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Crisp?", "Sweet"]}                 |
|  2 | {"fruit": "Banana", "price": 8.0, "labels": ["Ripe"], "quantity": 150}          |
|  3 | {"fruit": "Cherry", "price": 15.0, "labels": ["Small"], "quantity": 120}        |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50} |
+----+---------------------------------------------------------------------------------+

2.4 返回 JSON 屬性

JSON_KEYS(json_doc[, path])
函數(shù)以 JSON 數(shù)組的形式返回 JSON 對象的頂級值的鍵。如果指定了 path 參數(shù)，則返回所選路徑的頂級鍵。

mysql> SELECT id, JSON_KEYS(data) AS `keys` FROM orders;
+----+------------------------------------------+
| id | keys                                     |
+----+------------------------------------------+
|  1 | ["fruit", "price", "labels"]             |
|  2 | ["fruit", "price", "labels", "quantity"] |
|  3 | ["fruit", "price", "labels", "quantity"] |
|  4 | ["fruit", "price", "labels", "quantity"] |
+----+------------------------------------------+

2.5 生成 JSON 表

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

從 JSON 文檔中提取數(shù)據(jù)，并將其作為具有指定列的關(guān)系表返回。

mysql> SELECT o.id, jt.fruit, jt.quantity, jt.price, jt.comments
FROM orders o,
JSON_TABLE(
    o.data,
    '$' COLUMNS(
        fruit VARCHAR(255) PATH '$.fruit',
        quantity INT PATH '$.quantity',
        price DECIMAL(10, 2) PATH '$.price',
        comments JSON PATH '$.comments'
    )
) AS jt;
+----+--------+----------+-------+----------+
| id | fruit  | quantity | price | comments |
+----+--------+----------+-------+----------+
|  1 | Apple  | NULL     | 26.00 | NULL     |
|  2 | Banana |      150 | 8.00  | NULL     |
|  3 | Cherry |      120 | 15.00 | NULL     |
|  4 | Apple  |       50 | 12.50 | NULL     |
+----+--------+----------+-------+----------+

2.6 其他 JSON 函數(shù)

JSON_PRETTY(json_val)

格式化 JSON 文檔，使其更易讀。

mysql> SELECT id, JSON_PRETTY(data) FROM orders;
+----+-----------------------------------------------------------------------------------------------+
| id | JSON_PRETTY(data)                                                                             |
+----+-----------------------------------------------------------------------------------------------+
|  1 | {
  "fruit": "Apple",
  "price": 26.0,
  "labels": [
    "Crisp?",
    "Sweet"
  ]
}                  |
|  2 | {
  "fruit": "Banana",
  "price": 8.0,
  "labels": [
    "Ripe"
  ],
  "quantity": 150
}             |
|  3 | {
  "fruit": "Cherry",
  "price": 15.0,
  "labels": [
    "Small"
  ],
  "quantity": 120
}           |
|  4 | {
  "fruit": "Apple",
  "price": 12.5,
  "labels": [
    "Fresh",
    "Sweet"
  ],
  "quantity": 50
} |
+----+-----------------------------------------------------------------------------------------------+

JSON_LENGTH(json_doc[, path] ...)

返回 JSON 文檔的長度。

mysql> SELECT id, JSON_LENGTH(data) AS length FROM orders;
+----+--------+
| id | length |
+----+--------+
|  1 |      3 |
|  2 |      4 |
|  3 |      4 |
|  4 |      4 |
+----+--------+

mysql> SELECT id, JSON_LENGTH(data, '$.labels') AS length FROM orders;
+----+--------+
| id | length |
+----+--------+
|  1 |      2 |
|  2 |      1 |
|  3 |      1 |
|  4 |      2 |
+----+--------+

JSON_VALID(val)
判斷 JSON 值是否有效。返回 0 或 1 以指示該值是否為有效的 JSON。

mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

注意事項

大小寫敏感：大多數(shù) JSON 函數(shù)對大小寫敏感，因此在比較字符串時需要注意。
參數(shù)有效性：當(dāng)輸入?yún)?shù)為空或無效時，函數(shù)可能會返回 NULL。
性能優(yōu)化：對于包含大量 JSON 數(shù)據(jù)的表，直接使用 JSON 函數(shù)可能導(dǎo)致性能下降。為了提高查詢效率，可以考慮使用虛擬列（Generated Columns）結(jié)合索引來加速特定條件下的查詢，或者將經(jīng)常查詢的字段提取到單獨的列中進行索引。

總結(jié)

MySQL 8.0 提供了豐富的 JSON 函數(shù)，使得處理 JSON 數(shù)據(jù)變得更加簡單和高效。通過本文的詳細介紹和實際應(yīng)用示例，讀者可以更好地理解和利用這些函數(shù)，在實際開發(fā)中發(fā)揮其最大價值。合理的設(shè)計和優(yōu)化也是確保系統(tǒng)性能的關(guān)鍵因素之一。通過合理使用這些 JSON 函數(shù)，可以顯著提升數(shù)據(jù)處理的靈活性和效率。希望本文能幫助讀者在使用 MySQL 8.0 處理 JSON 數(shù)據(jù)時更加得心應(yīng)手。

到此這篇關(guān)于MySQL中JSON 函數(shù)的具體使用的文章就介紹到這了,更多相關(guān)MySQL JSON 函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: