MySQL中JSON 函數(shù)的具體使用
更新時(shí)間：2025年07月24日 09:48:54 作者：尋找09之夏
在現(xiàn)代數(shù)據(jù)庫設(shè)計(jì)中,JSON 格式的數(shù)據(jù)因其靈活性和可擴(kuò)展性而變得越來越受歡迎,MySQL 8.0 引入了許多強(qiáng)大的 JSON 函數(shù),使得處理 JSON 數(shù)據(jù)變得更加方便和高效,下面就來介紹一下
前言
在現(xiàn)代數(shù)據(jù)庫設(shè)計(jì)中，JSON 格式的數(shù)據(jù)因其靈活性和可擴(kuò)展性而變得越來越受歡迎。MySQL 8.0 引入了許多強(qiáng)大的 JSON 函數(shù)，使得處理 JSON 數(shù)據(jù)變得更加方便和高效。本文將通過一個(gè)簡化的訂單表 orders，展示如何使用這些 JSON 函數(shù)來創(chuàng)建、搜索、修改和驗(yàn)證 JSON 數(shù)據(jù)，從而優(yōu)化訂單管理系統(tǒng)。
1. 表結(jié)構(gòu)定義
1.1 創(chuàng)建訂單表 orders
首先，我們定義一個(gè)簡單的訂單表 orders，其中包含一個(gè)主鍵 id 和一個(gè)存儲(chǔ)訂單詳細(xì)信息的 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)建一個(gè) 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)建一個(gè) JSON 對(duì)象。key1, key2: JSON 對(duì)象的鍵；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ù)用于檢查一個(gè) JSON 文檔（target）是否包含另一個(gè) JSON 文檔或值（candidate），或者檢查在指定的路徑（path）下是否包含該候選者。它返回一個(gè)整數(shù)來表示結(jié)果：如果找到匹配項(xiàng)，則返回 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ì)算。 通過評(píng)估一個(gè)路徑生成的文檔將成為評(píng)估下一個(gè)路徑所依據(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 對(duì)象的頂級(jí)值的鍵。 如果指定了 path 參數(shù)，則返回所選路徑的頂級(jí)鍵。
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 |
+---------------------+-----------------------+注意事項(xiàng)
- 大小寫敏感：大多數(shù) JSON 函數(shù)對(duì)大小寫敏感，因此在比較字符串時(shí)需要注意。
- 參數(shù)有效性：當(dāng)輸入?yún)?shù)為空或無效時(shí)，函數(shù)可能會(huì)返回 NULL。
- 性能優(yōu)化：對(duì)于包含大量 JSON 數(shù)據(jù)的表，直接使用 JSON 函數(shù)可能導(dǎo)致性能下降。為了提高查詢效率，可以考慮使用虛擬列（Generated Columns）結(jié)合索引來加速特定條件下的查詢，或者將經(jīng)常查詢的字段提取到單獨(dú)的列中進(jìn)行索引。
總結(jié)
MySQL 8.0 提供了豐富的 JSON 函數(shù)，使得處理 JSON 數(shù)據(jù)變得更加簡單和高效。通過本文的詳細(xì)介紹和實(shí)際應(yīng)用示例，讀者可以更好地理解和利用這些函數(shù)，在實(shí)際開發(fā)中發(fā)揮其最大價(jià)值。合理的設(shè)計(jì)和優(yōu)化也是確保系統(tǒng)性能的關(guān)鍵因素之一。通過合理使用這些 JSON 函數(shù)，可以顯著提升數(shù)據(jù)處理的靈活性和效率。希望本文能幫助讀者在使用 MySQL 8.0 處理 JSON 數(shù)據(jù)時(shí)更加得心應(yīng)手。
到此這篇關(guān)于MySQL中JSON 函數(shù)的具體使用的文章就介紹到這了,更多相關(guān)MySQL JSON 函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL中LAG()函數(shù)和LEAD()函數(shù)的使用
這篇文章主要介紹了MySQL中LAG()函數(shù)和LEAD()函數(shù)的使用,包括窗口函數(shù)的基本用法，LAG()和LEAD()函數(shù)介紹，本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-08-08
這篇文章主要介紹了MySQL的安裝以及基本的管理命令和設(shè)置,是搭建MySQL環(huán)境的基礎(chǔ),需要的朋友可以參考下2015-11-11
linux 安裝 mysql 8.0.19 詳細(xì)步驟及問題解決方法
這篇文章主要介紹了linux 安裝 mysql 8.0.19 詳細(xì)步驟,本文給大家列出了常見問題及解決方法，通過實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-02-02
Mysql5.7.18版本（二進(jìn)制包安裝）自定義安裝路徑教程詳解
這篇文章主要介紹了Mysql5.7.18版本（二進(jìn)制包安裝）自定義安裝路徑教程詳解,需要的朋友可以參考下2017-07-07
在MySQL中,?NOT EXISTS子句是否使用索引取決于子查詢中關(guān)聯(lián)字段是否建立了合適的索引,下面就來介紹一下mysql的not exists走不走索引,感興趣的可以了解一下2025-07-07
這篇文章主要給大家介紹了關(guān)于MySQL聯(lián)結(jié)表介紹及使用的相關(guān)資料,聯(lián)結(jié)SQL最強(qiáng)大的功能之一就是能在數(shù)據(jù)檢索查詢的執(zhí)行中聯(lián)結(jié)表,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03
這篇文章主要介紹了mysql共享鎖與排他鎖用法,結(jié)合實(shí)例形式分析了mysql共享鎖與排他鎖相關(guān)概念、原理、用法及操作注意事項(xiàng),需要的朋友可以參考下2019-09-09
MySQL處理重復(fù)數(shù)據(jù)的學(xué)習(xí)筆記
在本篇文章里小編給大家分享的是一篇關(guān)于MySQL處理重復(fù)數(shù)據(jù)的學(xué)習(xí)筆記，需要的朋友們可以參考下。2020-03-03
今天小編就為大家分享一篇關(guān)于B-樹的刪除過程介紹，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-01-01
這篇文章主要介紹了MySQL表的四種分區(qū)類型,RANGE分區(qū),LIST分區(qū),HASH分區(qū),KEY分區(qū),需要的朋友可以參考下2024-03-03