在數據庫設計中，有時我們會將 JSON 數據存儲在 VARCHAR 或 TEXT 類型字段中。這種方式雖然靈活，但在查詢時需要特別注意。本文將詳細介紹如何在 MySQL 中有效查詢存儲為 VARCHAR 類型的 JSON 數據。

一、問題背景

當 JSON 數據存儲在 VARCHAR 列中時，常見的數據格式如下：

[{"id":"1905555466980773889","hasPermission":true},{"id":"1905547884060835841","hasPermission":false}
]

我們需要查詢這個 JSON 數組中是否包含特定 ID 的對象。

二、MySQL JSON 函數

MySQL 5.7+ 版本提供了豐富的 JSON 處理函數，即使數據類型是 VARCHAR，只要內容是有效的 JSON，我們仍然可以使用這些函數：

2.1 常用 JSON 函數

• JSON_CONTAINS(target, candidate[, path]): 檢查 JSON 文檔是否包含特定值
• JSON_EXTRACT(json_doc, path): 從 JSON 文檔中提取值
• JSON_OBJECT(key, val[, key, val]...): 創建 JSON 對象
• JSON_ARRAY(val[, val]...): 創建 JSON 數組
• JSON_VALID(json_doc): 驗證字符串是否為有效的 JSON

三、查詢示例

3.1 基本查詢

查詢 JSON 數組中包含特定 ID 的記錄：

SELECT * FROM sys_user
WHERE app_ids IS NOT NULLAND app_ids != ''AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

3.2 查詢多個 ID

SELECT * FROM sys_user
WHERE app_ids IS NOT NULLAND 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 NULLAND app_ids != ''AND JSON_OVERLAPS(app_ids, JSON_ARRAY(JSON_OBJECT('id', '1905555466980773889'),JSON_OBJECT('id', '1905547884060835841')));

3.4 查詢特定權限的記錄

SELECT * FROM sys_user
WHERE app_ids IS NOT NULLAND app_ids != ''AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889', 'hasPermission', true));

四、避免常見錯誤

4.1 空值處理

JSON_CONTAINS 函數在處理 NULL 或空字符串時會報錯，所以需要先排除這些情況：

-- 錯誤做法
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 的第二個參數結構與目標 JSON 中的結構匹配：

-- 錯誤做法 (直接傳入 ID 字符串)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, '"1905555466980773889"');-- 正確做法 (創建與數組元素匹配的對象)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

4.3 確保 JSON 有效性

添加 JSON_VALID 檢查確保字段內容是有效的 JSON：

SELECT * FROM sys_user
WHERE app_ids IS NOT NULLAND app_ids != ''AND JSON_VALID(app_ids) = 1AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

五、在 MyBatis Plus 中的應用

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");// 如果還要根據權限過濾
queryWrapper.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}, 'hasPermission', {1}))", "1905555466980773889", true);

5.3 查詢多個 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);

六、性能優化建議

1. 考慮使用 JSON 類型：如果您的 MySQL 版本是 5.7+，考慮使用原生 JSON 類型代替 VARCHAR，這樣可以獲得更好的性能和功能支持。

2. 添加索引：雖然無法直接為 JSON 內容創建索引，但可以使用生成的列和函數索引：

   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);
   

3. 定期維護：對于大表，定期 OPTIMIZE TABLE 有助于維護性能。

七、總結

在 MySQL 中查詢 VARCHAR 類型的 JSON 數據時，關鍵是：

1. 使用 JSON_CONTAINS 函數并構造正確的 JSON 結構進行匹配
2. 處理好 NULL 和空字符串
3. 驗證 JSON 有效性
4. 在 MyBatis Plus 中使用 apply 方法添加原生 SQL 條件

正確使用這些技術可以有效地查詢和處理 VARCHAR 中存儲的 JSON 數據。