在日常開發(fā)中，隨著項(xiàng)目需求的不斷復(fù)雜化，許多表字段可能會(huì)存儲(chǔ) JSON 格式的數(shù)據(jù)。

例如，我們有一張site_device表，其中有一個(gè)名為detail的字段，保存了設(shè)備的詳細(xì)信息。這些信息存儲(chǔ)為 JSON 數(shù)據(jù)，如下所示：

{
  "deviceType": "ammeter",
  "techParams": {
    "name": "202501241556",
    "deviceNo": "202501241556",
    "gatewayNo": "1829047495952388098",
    "ownership": "top",
    "dataReport": "1"
  },
  "deviceBrand": "HUAWEI",
  "deviceModel": "test",
  "modelConfigId": "1871021778273325058"
}

我們想要查詢出 ownership 為 top 的設(shè)備。ownership 字段嵌套在 techParams 中，因此我們需要使用 MySQL 提供的 JSON 函數(shù)來實(shí)現(xiàn)查詢。

1. 理解 JSON 數(shù)據(jù)的層級結(jié)構(gòu)

在這個(gè)例子中，JSON 的結(jié)構(gòu)可以分解為：

• deviceType：在 JSON 頂層。
• techParams：是一個(gè)嵌套對象，里面包含了 ownership 等字段。
• ownership：目標(biāo)字段，位于 techParams 內(nèi)。

我們需要從 detail 中提取出 techParams.ownership 的值。

2. 使用 MySQL JSON 查詢函數(shù)

MySQL 提供了一系列函數(shù)用于處理 JSON 數(shù)據(jù)：

• JSON_EXTRACT(json_doc, path)：從 JSON 中提取值。
• JSON_UNQUOTE(json_val)：去掉 JSON 提取值的引號，返回純文本。

對于本例來說，我們可以用以下語句來篩選出 ownership 為 top 的記錄：

SELECT *
FROM site_device
WHERE JSON_UNQUOTE(JSON_EXTRACT(detail, '$.techParams.ownership')) = 'top';

語法解釋

JSON_EXTRACT(detail, '$.techParams.ownership')

提取 detail 中 techParams 對象內(nèi)的 ownership 值。

JSON_UNQUOTE(...)

去掉 JSON 提取結(jié)果的引號，使其變?yōu)槠胀ㄗ址?/p>

WHERE ... = 'top'

篩選出 ownership 值等于 top 的記錄。

3. 示例數(shù)據(jù)和運(yùn)行結(jié)果

假設(shè) site_device 表中的數(shù)據(jù)如下：

運(yùn)行查詢后，結(jié)果為：

4. 注意事項(xiàng)

JSON 路徑表達(dá)式 $

JSON 路徑表達(dá)式 $ 表示 JSON 的根，嵌套字段用 . 分隔。例如：$.techParams.ownership。

性能優(yōu)化

如果數(shù)據(jù)量較大，可以通過為 JSON 字段創(chuàng)建虛擬列（Generated Column）并加索引來提升查詢性能。

ALTER TABLE site_device
ADD COLUMN ownership VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(detail, '$.techParams.ownership'))) STORED,
ADD INDEX idx_ownership (ownership);

數(shù)據(jù)規(guī)范化

如果 JSON 數(shù)據(jù)中的字段經(jīng)常被查詢，考慮將這些字段拆分到獨(dú)立的數(shù)據(jù)庫列中，以提高查詢效率。

5. 總結(jié)

MySQL 提供了強(qiáng)大的 JSON 查詢功能，使得我們可以方便地處理結(jié)構(gòu)化的 JSON 數(shù)據(jù)。在本文中，我們通過 JSON_EXTRACT 和 JSON_UNQUOTE 函數(shù)，成功篩選出了目標(biāo)字段值為特定值的記錄。同時(shí)，結(jié)合性能優(yōu)化建議，可以讓你的 JSON 查詢更高效。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論