快捷導(dǎo)航

MySQL 如何查詢 JSON 數(shù)組是否包含特定的值

更新時(shí)間：2023年11月01日 11:43:44 作者：knan-阿樂(lè)

本文給大家介紹MySQL 如何查詢 JSON 數(shù)組是否包含特定的值,假設(shè)定義了一張表 School,其中字段 stages 為 JSON 類型,本文通過(guò)實(shí)例代碼給大家詳細(xì)講解,感興趣的朋友一起看看吧

MySQL 查詢 JSON 數(shù)組是否包含特定的值

假設(shè)定義了一張表 School，其中字段 stages 為 JSON 類型

CREATE TABLE `schools` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `stages` json DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

插入測(cè)試數(shù)據(jù)

INSERT INTO `schools` ( `stages` ) VALUES ( '[2]' );

查詢表中字段 stages 中包含數(shù)值 2 的所有記錄

JSON_EXTRACT & LIKE

使用 JSON_EXTRACT 函數(shù)，可以從 JSON 中提取出指定位置的值。但是它只能提取某個(gè)特定位置的值，不能查詢是否包含某個(gè)值。所以如果需要查詢 JSON 數(shù)組是否包含某個(gè)值，還需補(bǔ)充使用 LIKE。

-- 使用JSON_EXTRACT函數(shù)將數(shù)組的第一個(gè)元素提取出來(lái), 然后使用LIKE聲明匹配是否包含字符串"2"
-- 注意, 這種方式只能用于已知數(shù)組中元素位置的情況, 它無(wú)法用于查找一個(gè)值是否在任何位置上都存在
SELECT
	* 
FROM
	`schools` AS `SchoolModel` 
WHERE
	JSON_EXTRACT( stages, '$[0]' ) LIKE '%2%'

JSON_CONTAINS

MySQL 5.7 版本引入了 JSON_CONTAINS 函數(shù)，用于判斷一個(gè) JSON 數(shù)組是否包含某個(gè)元素。

-- 第一個(gè)參數(shù)是查詢的JSON對(duì)象, 第二個(gè)參數(shù)是需要查找的JSON對(duì)象(在這里是包含一個(gè)字符串"2"的JSON數(shù)組), 第三個(gè)參數(shù)是$(表示查詢應(yīng)該從整個(gè)JSON對(duì)象開(kāi)始)
-- 注意, 如果想搜索一個(gè)不是獨(dú)立字符串的值, 例如數(shù)字或布爾值, 需要將值轉(zhuǎn)換成JSON格
-- 例如, 如果想搜索數(shù)字2, 在JSON格式中, 需要寫成'2'而不是原始數(shù)字2
SELECT
	* 
FROM
	`schools` AS `SchoolModel` 
WHERE
	JSON_CONTAINS(
		stages,
		'2', -- '[2]'
	'$' 
	)

JSON_CONTAINS 函數(shù)同樣可以用于搜索嵌套在一個(gè)對(duì)象或數(shù)組中的元素。

-- 這個(gè)查詢將返回空結(jié)果, 因?yàn)镴SON數(shù)組中沒(méi)有{"name":"小學(xué)"}
SELECT
	* 
FROM
	`schools` 
WHERE
	JSON_CONTAINS( `stages`, '{"name":"小學(xué)"}', "$" );