問題背景

在MySQL數(shù)據(jù)庫中，當某個字段存儲的是JSON數(shù)組（如["噴繪","2.6m噴繪","M噴繪","直噴","雙透","氣模"]），需要查詢數(shù)組中包含特定字符串（如’氣模’）的記錄時，傳統(tǒng)的LIKE語句無法直接使用。本文介紹兩種高效的解決方案。

解決方案對比

1. 精確匹配方案（推薦）

當需要完全匹配數(shù)組中的元素時（如精確查找"氣模"）：

SELECT *
FROM process_unit_prices
WHERE 
  process_name = '制圖' 
  AND JSON_CONTAINS(craft_name, JSON_QUOTE('氣模'))
LIMIT 1;

1.核心函數(shù)：

• JSON_QUOTE(): 將字符串轉為JSON格式（如"氣模"）
• JSON_CONTAINS(): 檢查JSON數(shù)組中是否包含指定值

2.優(yōu)點：效率高，精確匹配數(shù)組元素

3.注意：必須元素完全相等才會匹配（如"氣模車"不會匹配）

2. 模糊匹配方案

當需要部分匹配元素內(nèi)容時（如查找包含"氣模"子串的元素）：

SELECT *
FROM process_unit_prices
WHERE 
  process_name = '制圖' 
  AND JSON_SEARCH(craft_name, 'one', '%氣模%') IS NOT NULL
LIMIT 1;

1.核心函數(shù)：

• JSON_SEARCH(): 搜索JSON中匹配模式的路徑
• 'one': 找到第一個匹配即停止
• '%氣模%': SQL的LIKE通配符模式

2.優(yōu)點：支持模糊匹配

3.注意：效率低于精確匹配，大數(shù)據(jù)量時需謹慎

參數(shù)化查詢示例

在MyBatis等ORM框架中使用（如Spring Boot項目）：

<!-- 精確匹配 -->
<select id="findProcess" resultType="ProcessPrice">
  SELECT * FROM process_unit_prices
  WHERE process_name = #{processType}
    AND JSON_CONTAINS(craft_name, JSON_QUOTE(#{craft}))
  LIMIT 1
</select>

<!-- 模糊匹配 -->
<select id="findProcessFuzzy" resultType="ProcessPrice">
  SELECT * FROM process_unit_prices
  WHERE process_name = #{processType}
    AND JSON_SEARCH(craft_name, 'one', CONCAT('%', #{craft}, '%')) IS NOT NULL
  LIMIT 1
</select>

使用場景建議

性能優(yōu)化貼士

索引優(yōu)化：MySQL 8.0+可為JSON字段創(chuàng)建函數(shù)索引

CREATE INDEX idx_craft ON process_unit_prices((CAST(craft_name AS CHAR(255))));

數(shù)據(jù)規(guī)范：若常需查詢，建議拆分為關系表

避免全模糊：%氣模%無法使用索引，盡量用氣模%

經(jīng)驗總結：JSON字段查詢雖靈活，但需謹慎使用。精確匹配首選JSON_CONTAINS，模糊需求再用JSON_SEARCH，并注意性能影響。在數(shù)據(jù)設計階段評估是否真需JSON結構，關系表通常更高效。

知識擴展

下面小編為大家整理了MYSQL 匹配查詢JSON字段的相關方法，希望對大家有所幫助

在 MySQL 中，如果你存儲的是 JSON 數(shù)據(jù)，并且需要檢查某個 JSON 字段是否包含一個特定的鍵值對（例如 {user1: 1}），你可以使用 MySQL 提供的 JSON 函數(shù)來實現(xiàn)。

1. 使用 JSON_CONTAINS() 函數(shù)

JSON_CONTAINS() 函數(shù)可以檢查一個 JSON 字段是否包含另一個 JSON 值。該函數(shù)返回一個布爾值，如果第一個參數(shù)（JSON 數(shù)據(jù)）包含第二個參數(shù)（查詢條件），則返回 1，否則返回 0。

示例查詢：

假設你的表結構如下：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_data JSON
);

你可以通過 JSON_CONTAINS() 函數(shù)來檢查 JSON 字段是否包含 {user1: 1}。

SELECT * 
FROM users 
WHERE JSON_CONTAINS(user_data, '{"user1": 1}');

這條 SQL 語句會返回所有 user_data 字段包含 {user1: 1} 鍵值對的記錄。

2. JSON_CONTAINS() 函數(shù)的使用細節(jié)

• 第一個參數(shù)是你要查詢的 JSON 字段。
• 第二個參數(shù)是你要查找的 JSON 數(shù)據(jù)，必須是一個有效的 JSON 格式字符串。
• 該函數(shù)會檢查 JSON 字段中是否存在第二個參數(shù)所表示的鍵值對。

3. 查詢部分匹配

如果你只想檢查 JSON 字段中是否存在某個鍵（例如 user1）并且該鍵的值是 1，而不關心 JSON 字段中的其他內(nèi)容，你仍然可以使用 JSON_CONTAINS()，但是需要確保第二個參數(shù)符合你想要的結構。

SELECT * 
FROM users 
WHERE JSON_CONTAINS(user_data, '{"user1": 1}');

4. 檢查 JSON 字段中是否包含某個鍵

如果你不關心鍵值對，只想檢查 JSON 字段中是否包含某個鍵（比如 user1），可以使用 JSON_EXTRACT() 或 JSON_UNQUOTE() 與 IS NOT NULL 來實現(xiàn)。

SELECT * 
FROM users 
WHERE JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.user1')) = '1';

• JSON_EXTRACT(user_data, '$.user1') 提取 user_data 字段中的 user1 鍵的值。
• JSON_UNQUOTE() 去除引號，使其返回一個普通值。
• 比較提取的值是否等于 '1'。

小結

• JSON_CONTAINS()：用于檢查 JSON 字段是否包含某個指定的鍵值對。
• JSON_EXTRACT() + JSON_UNQUOTE()：可以用來提取并比較 JSON 字段中的值。

使用 JSON_CONTAINS() 是檢查 JSON 數(shù)據(jù)是否包含某個完整鍵值對的簡便方法。如果你需要更細粒度的查詢（如只關心鍵是否存在且其值為特定值），可以結合 JSON_EXTRACT() 和 JSON_UNQUOTE() 進行操作。

到此這篇關于MySQL查詢JSON數(shù)組字段包含特定字符串的方法的文章就介紹到這了,更多相關MySQL查詢JSON字段內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論