MySQL中高效查詢JSON字符串字段的方法詳解
前言
在現(xiàn)代應(yīng)用開發(fā)中，JSON 格式因其靈活性和可讀性被廣泛用于存儲(chǔ)半結(jié)構(gòu)化數(shù)據(jù)。許多開發(fā)者選擇將 JSON 字符串直接存入 MySQL 的 TEXT 或 VARCHAR 字段中，以避免頻繁修改表結(jié)構(gòu)。然而，當(dāng)需要基于 JSON 內(nèi)部字段進(jìn)行檢索時(shí)（例如“找出所有設(shè)備類型為‘溫濕度傳感器’的記錄”），如何編寫高效、安全且可維護(hù)的 SQL 語句，成為了一個(gè)關(guān)鍵問題。
一、問題場(chǎng)景與錯(cuò)誤做法
1.1 典型數(shù)據(jù)示例
假設(shè)有一張物聯(lián)網(wǎng)設(shè)備上報(bào)日志表 device_telemetry，其中 payload 字段存儲(chǔ)如下 JSON 字符串：
{
"device_type": "temperature_humidity_sensor",
"model": "TH-S200",
"readings": {
"temperature_celsius": 23.5,
"humidity_percent": 62.8
},
"battery_level": 87,
"status": "online"
}
目標(biāo)：檢索所有 device_type 字段值為 “temperature_humidity_sensor” 的記錄。
1.2 常見但錯(cuò)誤的做法：使用LIKE
許多初學(xué)者會(huì)寫出如下 SQL：
SELECT * FROM device_telemetry WHERE payload LIKE '%temperature_humidity_sensor%';
問題分析：
- 誤匹配風(fēng)險(xiǎn)高：若其他字段（如
model或日志描述）恰好包含該字符串，也會(huì)被命中； - 無法區(qū)分字段語義：不能確保該值一定出現(xiàn)在
device_type字段； - 性能低下：
LIKE '%...%'無法使用索引，導(dǎo)致全表掃描； - 編碼與轉(zhuǎn)義隱患：若 JSON 中包含轉(zhuǎn)義字符（如
\"），匹配可能失敗。
結(jié)論：永遠(yuǎn)不要用 LIKE 查詢 JSON 內(nèi)容。
二、正確方法：使用 MySQL 原生 JSON 函數(shù)
自 MySQL 5.7 起，官方提供了完整的 JSON 支持，包括數(shù)據(jù)類型、函數(shù)和操作符。即使你的字段是 TEXT 類型，只要內(nèi)容是合法 JSON，也可使用這些函數(shù)解析。
2.1 核心函數(shù)與操作符
| 函數(shù)/操作符 | 說明 |
|---|---|
| JSON_EXTRACT(json_doc, path) | 提取指定路徑的 JSON 值，返回帶引號(hào)的字符串（如 "temperature_humidity_sensor"） |
| -> | 等價(jià)于 JSON_EXTRACT()，語法糖 |
| ->> | 等價(jià)于 JSON_UNQUOTE(JSON_EXTRACT())，返回去引號(hào)的純字符串 |
| JSON_UNQUOTE(value) | 去除 JSON 字符串的雙引號(hào) |
| JSON_VALID(json_doc) | 判斷是否為合法 JSON |
2.2 推薦寫法：使用->>操作符
SELECT * FROM device_telemetry WHERE payload->>'$.device_type' = 'temperature_humidity_sensor';
優(yōu)勢(shì)：
- 語法簡(jiǎn)潔、可讀性強(qiáng)；
- 自動(dòng)解引用（unquote），直接返回字符串值；
- 與標(biāo)準(zhǔn) SQL 風(fēng)格一致。
2.3 兼容寫法（適用于舊代碼或強(qiáng)調(diào)顯式）
SELECT * FROM device_telemetry WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.device_type')) = 'temperature_humidity_sensor';
兩者功能完全等價(jià)，但前者更現(xiàn)代、更推薦。
三、處理邊界情況：數(shù)據(jù)合法性校驗(yàn)
實(shí)際生產(chǎn)環(huán)境中，payload 字段可能包含以下非法內(nèi)容：
NULL- 空字符串
'' - 非 JSON 格式的字符串（如
"invalid json"） - 字段缺失（如沒有
device_type鍵）
若直接使用 ->>，遇到非法 JSON 會(huì)返回 NULL，可能導(dǎo)致查詢結(jié)果不符合預(yù)期，甚至在嚴(yán)格模式下報(bào)錯(cuò)。
3.1 安全查詢：加入JSON_VALID校驗(yàn)
SELECT * FROM device_telemetry WHERE JSON_VALID(payload) AND payload->>'$.device_type' = 'temperature_humidity_sensor';
建議：在所有涉及 JSON 解析的查詢中，優(yōu)先加入 JSON_VALID() 判斷，提升魯棒性。
3.2 處理字段缺失：使用COALESCE或IFNULL
若某些記錄沒有 device_type 字段，payload->>'$.device_type' 返回 NULL。若需將其視為空字符串：
SELECT * FROM device_telemetry WHERE JSON_VALID(payload) AND COALESCE(payload->>'$.device_type', '') = 'temperature_humidity_sensor';
四、多條件組合查詢
JSON 中常包含多個(gè)字段，需聯(lián)合過濾。例如：device_type = 'smart_lock' AND method = 'fingerprint'。
4.1 注意：JSON 中的數(shù)字類型
在 JSON 中，"battery_level": 87 是一個(gè)整數(shù)，但 ->> 操作符始終返回字符串。因此：
-- ? 錯(cuò)誤：類型不匹配（字符串 vs 整數(shù)）
WHERE payload->>'$.battery_level' < 50;
-- ? 正確方式一：轉(zhuǎn)換為數(shù)值
WHERE CAST(payload->>'$.battery_level' AS UNSIGNED) < 50;
-- ? 更嚴(yán)謹(jǐn)（防止非數(shù)字）：
WHERE JSON_VALID(payload)
AND CAST(
CASE
WHEN payload->>'$.battery_level' REGEXP '^[0-9]+$'
THEN payload->>'$.battery_level'
ELSE '0'
END AS UNSIGNED
) < 50;
對(duì)于浮點(diǎn)數(shù)（如溫度 23.5），應(yīng)使用 DECIMAL 或 DOUBLE：
WHERE CAST(payload->>'$.readings.temperature_celsius' AS DECIMAL(5,2)) > 23.0;
最佳實(shí)踐：對(duì)數(shù)值型 JSON 字段，務(wù)必顯式轉(zhuǎn)換類型后再比較，避免字符串字典序錯(cuò)誤（如 '100' < '50' 為真）。
五、性能瓶頸與優(yōu)化策略
5.1 性能問題根源
對(duì) payload->>'$.device_type' 的查詢屬于函數(shù)表達(dá)式，MySQL 無法直接使用普通 B-tree 索引加速，導(dǎo)致每次查詢都需全表掃描并逐行解析 JSON。
在百萬級(jí)設(shè)備日志下，此類查詢可能耗時(shí)數(shù)秒甚至超時(shí)。
5.2 優(yōu)化方案一：使用生成列（Generated Column） + 索引（推薦）
MySQL 5.7+ 支持虛擬生成列（Virtual Generated Column），可自動(dòng)從 JSON 中提取字段并建立索引。
步驟 1：添加生成列
ALTER TABLE device_telemetry ADD COLUMN extracted_device_type VARCHAR(64) GENERATED ALWAYS AS (payload->>'$.device_type') VIRTUAL;
VIRTUAL表示不物理存儲(chǔ)，節(jié)省空間；- 若需更高查詢性能，可使用
STORED（物理存儲(chǔ)，占用磁盤）。
步驟 2：為生成列創(chuàng)建索引
CREATE INDEX idx_device_type ON device_telemetry(extracted_device_type);
步驟 3：改寫查詢語句
SELECT * FROM device_telemetry WHERE extracted_device_type = 'temperature_humidity_sensor';
效果：
- 查詢走索引，速度提升百倍以上；
- 語句簡(jiǎn)潔，無 JSON 解析開銷；
- 自動(dòng)維護(hù)，無需應(yīng)用層同步。
適用場(chǎng)景：高頻查詢的 JSON 子字段（如 device_type, status, event）。
5.3 優(yōu)化方案二：冗余字段（適用于核心業(yè)務(wù)字段）
若 device_type 是業(yè)務(wù)主鍵之一，建議直接將其作為獨(dú)立字段存儲(chǔ)：
ALTER TABLE device_telemetry ADD COLUMN device_type VARCHAR(64); -- 應(yīng)用層寫入時(shí)同時(shí)填充 device_type 和 payload CREATE INDEX idx_device_type ON device_telemetry(device_type);
優(yōu)勢(shì)：最高效、最兼容、最易維護(hù)。
原則：高頻查詢字段不應(yīng)藏在 JSON 中。
六、版本兼容性說明
| 功能 | MySQL 5.6 | MySQL 5.7 | MySQL 8.0+ |
|---|---|---|---|
| JSON_EXTRACT | ? 不支持 | ? 支持 | ? 支持 |
| -> / ->> 操作符 | ? | ? | ? |
| JSON_VALID | ? | ? | ? |
| 生成列（Generated Column） | ? | ?（5.7.6+） | ?（增強(qiáng)） |
| JSON 數(shù)據(jù)類型 | ? | ? | ?（性能優(yōu)化） |
建議：生產(chǎn)環(huán)境至少使用 MySQL 5.7.22+ 或 8.0 LTS。
七、完整示例
-- 1. 創(chuàng)建表
CREATE TABLE device_telemetry (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
device_id VARCHAR(36) NOT NULL,
event_time DATETIME(3) NOT NULL,
payload TEXT NOT NULL
);
-- 2. 插入測(cè)試數(shù)據(jù)
INSERT INTO device_telemetry (device_id, event_time, payload) VALUES
('d8a3b1e4-5c2f-4f8a-9e1d-0a2b3c4d5e6f', '2026-01-10 14:23:11.456',
'{"device_type":"temperature_humidity_sensor","model":"TH-S200","readings":{"temperature_celsius":23.5,"humidity_percent":62.8},"battery_level":87,"status":"online"}'),
('a1b2c3d4-e5f6-7890-1234-567890abcdef', '2026-01-10 15:01:33.120',
'{"device_type":"smart_lock","model":"LOCK-X9","event":"unlock_success","user_id":"U10045","method":"fingerprint","battery_level":45,"status":"locked_after_5s"}');
-- 3. 安全查詢
SELECT * FROM device_telemetry
WHERE JSON_VALID(payload)
AND payload->>'$.device_type' = 'temperature_humidity_sensor';
-- 4. 添加生成列（優(yōu)化）
ALTER TABLE device_telemetry
ADD COLUMN extracted_device_type VARCHAR(64) AS (payload->>'$.device_type') VIRTUAL;
CREATE INDEX idx_device_type ON device_telemetry(extracted_device_type);
-- 5. 高效查詢
SELECT * FROM device_telemetry WHERE extracted_device_type = 'temperature_humidity_sensor';
八、最佳實(shí)踐
| 場(chǎng)景 | 推薦方案 |
|---|---|
| 偶爾查詢、數(shù)據(jù)量小 | 直接使用 payload->>'$.field' = ? + JSON_VALID |
| 高頻查詢、中大數(shù)據(jù)量 | 生成列 + 索引（首選） |
| 核心業(yè)務(wù)字段（如設(shè)備類型、狀態(tài)） | 拆分為獨(dú)立字段，不要放入 JSON |
| 復(fù)雜嵌套 JSON 查詢 | 考慮 NoSQL 或應(yīng)用層解析 |
| 必須兼容 MySQL 5.6 | 避免 JSON，改用關(guān)系型設(shè)計(jì) |
到此這篇關(guān)于MySQL中高效查詢JSON字符串字段的方法詳解的文章就介紹到這了,更多相關(guān)MySQL查詢JSON字段內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql 動(dòng)態(tài)生成測(cè)試數(shù)據(jù)
mysql 動(dòng)態(tài)生成測(cè)試數(shù)據(jù)的語句，方便測(cè)試數(shù)據(jù)。2009-08-08
這篇文章主要給大家介紹了關(guān)于Mysql join連接查詢的相關(guān)資料，文中介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-10-10
超詳細(xì)mysql left join,right join,inner join用法分析
比較詳細(xì)的mysql的幾種連接功能分析，只要你看完就能學(xué)會(huì)的好東西2008-08-08
本文對(duì)Mysql分區(qū)表的局限性做了一些總結(jié)，因?yàn)閭€(gè)人能力以及測(cè)試環(huán)境的 原因，有可能有錯(cuò)誤的地方，還請(qǐng)大家看到能及時(shí)指出，當(dāng)然有興趣的朋友可以去官方網(wǎng)站查閱。2017-03-03
mysql自動(dòng)插入百萬模擬數(shù)據(jù)的操作代碼
這篇文章主要介紹了mysql自動(dòng)插入百萬模擬數(shù)據(jù)的示例代碼，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定參考借鑒價(jià)值,需要的朋友可以參考下2021-10-10
一次mysql遷移的方案與踩坑實(shí)戰(zhàn)記錄
這篇文章主要給大家介紹了一次mysql遷移的方案與踩坑的相關(guān)資料,MySQL遷移是DBA日常維護(hù)中的一個(gè)工作,遷移究其本義,無非是把實(shí)際存在的物體挪走,保證該物體的完整性以及延續(xù)性,需要的朋友可以參考下2021-08-08
對(duì)于mysql主備實(shí)例，seconds_behind_master是衡量master與slave之間延時(shí)的一個(gè)重要參數(shù)。通過在slave上執(zhí)行"show slave status;"可以獲取seconds_behind_master的值。2021-05-05
這篇文章主要介紹了詳解mysql不等于null和等于null的寫法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
這篇文章主要介紹了MySQL8.0 密碼過期策略的相關(guān)資料，幫助大家更好的理解和使用MySQL8.0的新功能，感興趣的朋友可以了解下2020-11-11
今天小編就為大家分享一篇關(guān)于Mysql5.7修改root密碼教程，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-02-02