MySQL 中查詢 VARCHAR 類型 JSON 數(shù)據(jù)的問題記錄
在數(shù)據(jù)庫設(shè)計(jì)中，有時(shí)我們會(huì)將 JSON 數(shù)據(jù)存儲(chǔ)在 VARCHAR 或 TEXT 類型字段中。這種方式雖然靈活，但在查詢時(shí)需要特別注意。本文將詳細(xì)介紹如何在 MySQL 中有效查詢存儲(chǔ)為 VARCHAR 類型的 JSON 數(shù)據(jù)。
一、問題背景
當(dāng) JSON 數(shù)據(jù)存儲(chǔ)在 VARCHAR 列中時(shí)，常見的數(shù)據(jù)格式如下：
[
{"id":"1905555466980773889","hasPermission":true},
{"id":"1905547884060835841","hasPermission":false}
]我們需要查詢這個(gè) JSON 數(shù)組中是否包含特定 ID 的對(duì)象。
二、MySQL JSON 函數(shù)
MySQL 5.7+ 版本提供了豐富的 JSON 處理函數(shù)，即使數(shù)據(jù)類型是 VARCHAR，只要內(nèi)容是有效的 JSON，我們?nèi)匀豢梢允褂眠@些函數(shù)：
2.1 常用 JSON 函數(shù)
JSON_CONTAINS(target, candidate[, path]): 檢查 JSON 文檔是否包含特定值JSON_EXTRACT(json_doc, path): 從 JSON 文檔中提取值JSON_OBJECT(key, val[, key, val]...): 創(chuàng)建 JSON 對(duì)象JSON_ARRAY(val[, val]...): 創(chuàng)建 JSON 數(shù)組JSON_VALID(json_doc): 驗(yàn)證字符串是否為有效的 JSON
三、查詢示例
3.1 基本查詢
查詢 JSON 數(shù)組中包含特定 ID 的記錄：
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));3.2 查詢多個(gè) ID
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND (
JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'))
OR JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905547884060835841'))
);3.3 使用 JSON_OVERLAPS (MySQL 8.0+)
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND JSON_OVERLAPS(
app_ids,
JSON_ARRAY(
JSON_OBJECT('id', '1905555466980773889'),
JSON_OBJECT('id', '1905547884060835841')
)
);3.4 查詢特定權(quán)限的記錄
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND JSON_CONTAINS(
app_ids,
JSON_OBJECT('id', '1905555466980773889', 'hasPermission', true)
);四、避免常見錯(cuò)誤
4.1 空值處理
JSON_CONTAINS 函數(shù)在處理 NULL 或空字符串時(shí)會(huì)報(bào)錯(cuò)，所以需要先排除這些情況：
-- 錯(cuò)誤做法
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));
-- 正確做法
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));4.2 JSON 格式匹配
確保 JSON_CONTAINS 的第二個(gè)參數(shù)結(jié)構(gòu)與目標(biāo) JSON 中的結(jié)構(gòu)匹配：
-- 錯(cuò)誤做法 (直接傳入 ID 字符串)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, '"1905555466980773889"');
-- 正確做法 (創(chuàng)建與數(shù)組元素匹配的對(duì)象)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));4.3 確保 JSON 有效性
添加 JSON_VALID 檢查確保字段內(nèi)容是有效的 JSON：
SELECT * FROM sys_user
WHERE
app_ids IS NOT NULL
AND app_ids != ''
AND JSON_VALID(app_ids) = 1
AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));五、在 MyBatis Plus 中的應(yīng)用
5.1 基本查詢
LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.isNotNull(SysUser::getAppIds)
.ne(SysUser::getAppIds, "")
.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
List<SysUser> userList = sysUserMapper.selectList(queryWrapper);5.2 多條件查詢
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.isNotNull("app_ids")
.ne("app_ids", "")
.apply("JSON_VALID(app_ids) = 1")
.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
// 如果還要根據(jù)權(quán)限過濾
queryWrapper.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}, 'hasPermission', {1}))",
"1905555466980773889", true);5.3 查詢多個(gè) ID
LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.isNotNull(SysUser::getAppIds)
.ne(SysUser::getAppIds, "")
.and(w -> w.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889")
.or()
.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905547884060835841"));
List<SysUser> userList = sysUserMapper.selectList(queryWrapper);六、性能優(yōu)化建議
考慮使用 JSON 類型：如果您的 MySQL 版本是 5.7+，考慮使用原生 JSON 類型代替 VARCHAR，這樣可以獲得更好的性能和功能支持。
添加索引：雖然無法直接為 JSON 內(nèi)容創(chuàng)建索引，但可以使用生成的列和函數(shù)索引：
ALTER TABLE sys_user ADD COLUMN app_id_extracted JSON GENERATED ALWAYS AS (JSON_EXTRACT(app_ids, '$[*].id')) VIRTUAL; ALTER TABLE sys_user ADD INDEX idx_app_id_extracted (app_id_extracted);
定期維護(hù)：對(duì)于大表，定期 OPTIMIZE TABLE 有助于維護(hù)性能。
七、總結(jié)
在 MySQL 中查詢 VARCHAR 類型的 JSON 數(shù)據(jù)時(shí)，關(guān)鍵是：
- 使用 JSON_CONTAINS 函數(shù)并構(gòu)造正確的 JSON 結(jié)構(gòu)進(jìn)行匹配
- 處理好 NULL 和空字符串
- 驗(yàn)證 JSON 有效性
- 在 MyBatis Plus 中使用 apply 方法添加原生 SQL 條件
正確使用這些技術(shù)可以有效地查詢和處理 VARCHAR 中存儲(chǔ)的 JSON 數(shù)據(jù)。
到此這篇關(guān)于MySQL 中查詢 VARCHAR 類型 JSON 數(shù)據(jù)的的文章就介紹到這了,更多相關(guān)mysql 查詢 VARCHAR 類型 JSON 數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- Mysql更新varchar存儲(chǔ)Json數(shù)據(jù)的操作方法
- Mysql中json類型數(shù)據(jù)查詢問題
- MySQL查詢和篩選存儲(chǔ)的JSON數(shù)據(jù)的操作方法
- 關(guān)于Mysql中json數(shù)據(jù)類型的查詢操作指南
- Mysql如何對(duì)json數(shù)據(jù)進(jìn)行查詢及修改
- MySQL對(duì)JSON類型字段數(shù)據(jù)進(jìn)行提取和查詢的實(shí)現(xiàn)
- mysql根據(jù)json字段內(nèi)容作為查詢條件(包括json數(shù)組)檢索數(shù)據(jù)
- Mysql將查詢結(jié)果集轉(zhuǎn)換為JSON數(shù)據(jù)的實(shí)例代碼
相關(guān)文章
修改MySQL密碼的四種方法總結(jié)(適合初學(xué)者!)
在日常使用數(shù)據(jù)庫的過程中,難免會(huì)遇到需要修改賬號(hào)密碼的情景,比如密碼太簡(jiǎn)單需要修改、密碼過期需要修改、忘記密碼需要修改等,下面這篇文章主要給大家介紹了關(guān)于修改MySQL密碼的四種方法,介紹的方法非常適合初學(xué)者,需要的朋友可以參考下2022-08-08
MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決
本文主要介紹了MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-08-08
Mysql最新版本的數(shù)據(jù)庫安裝教程（5.7）
這篇文章主要為大家詳細(xì)介紹了Mysql最新版本的數(shù)據(jù)庫安裝教程，分享了Mysql 5.7安裝配置方法，感興趣的小伙伴們可以參考一下2016-07-07
Windows下修改mysql的data文件夾存放位置的方法
這篇文章主要介紹了在Windows下修改mysql的data文件夾存放位置的方法,需要的朋友可以參考下2014-03-03
MySQL中動(dòng)態(tài)生成SQL語句去掉所有字段的空格的操作方法
在數(shù)據(jù)庫管理過程中,我們常常會(huì)遇到需要對(duì)表中字段進(jìn)行清洗和整理的情況,本文將詳細(xì)介紹如何在MySQL中動(dòng)態(tài)生成SQL語句來去掉所有字段的空格,感興趣的朋友一起看看吧2025-04-04
mysql如何比對(duì)兩個(gè)數(shù)據(jù)庫表結(jié)構(gòu)的方法
這篇文章主要介紹了mysql如何比對(duì)兩個(gè)數(shù)據(jù)庫表結(jié)構(gòu)的方法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
這篇文章主要介紹了Mysql中存儲(chǔ)UUID去除橫線的方法,本文給出了3個(gè)Mysql函數(shù)實(shí)現(xiàn)去除去UUID中的橫線,需要的朋友可以參考下2015-02-02
MySQL備份與恢復(fù)之保證數(shù)據(jù)一致性(5)
這篇文章主要介紹了MySQL備份與恢復(fù)之保證數(shù)據(jù)一致性，感興趣的小伙伴們可以參考一下2015-08-08
這篇文章主要為大家介紹下linux忘記mysql密碼處理方法，需要的朋友可以參考下。2011-08-08
基于mysql+mycat搭建穩(wěn)定高可用集群負(fù)載均衡主備復(fù)制讀寫分離操作
這篇文章主要介紹了基于mysql+mycat搭建穩(wěn)定高可用集群負(fù)載均衡主備復(fù)制讀寫分離操作,需要的朋友可以參考下2018-09-09