MySql模糊查詢json關(guān)鍵字檢索方案示例

更新時間：2021年12月01日 15:32:01 作者：PHPer自談

大家好，本篇文章主要講的是MySql模糊查詢json關(guān)鍵字檢索方案示例，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下哦，方便下次瀏覽

前言

?最近在項目中遇到這樣一個需求：需要在數(shù)據(jù)表中檢索包含指定內(nèi)容的結(jié)果集，該字段的數(shù)據(jù)類型為text，存儲的內(nèi)容是json格式，具體表結(jié)構(gòu)如下：

CREATE TABLE `product` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '產(chǎn)品名稱' COLLATE 'utf8mb4_general_ci',
	`price` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT '產(chǎn)品價格',
	`suit` TEXT NOT NULL COMMENT '適用門店 json格式保存門店id' COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '狀態(tài) 1-正常 0-刪除 2-下架',
	`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '發(fā)布時間',
	`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='產(chǎn)品表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

表數(shù)據(jù)如下：

現(xiàn)需求：查找 suit->hotel 中包含10001的數(shù)據(jù)。

通過谷歌百度查找，大致找到以下幾種方案：

方案一：

select * from product where suit like '%"10001"%';
#like方式不能使用索引，性能不佳，且準(zhǔn)確性不足

方案二：

select * from product where suit LOCATE('"10001"', 'suit') > 0;
# LOCATE方式和like存在相同問題

方案三：

select * from product where suit != '' and json_contains('suit'->'$.hotel', '"10001"');
#以MySQL內(nèi)置json函數(shù)查找，需要MySQL5.7以上版本才能支持，準(zhǔn)確性較高，不能使用全文索引

方案四（最終采用方案）：

select * from product where MATCH(suit) AGAINST('+"10001"' IN BOOLEAN MODE);
#可使用全文索引，MySQL關(guān)鍵字默認(rèn)限制最少4個字符，可在mysql.ini中修改 ft_min_word_len=2，重啟后生效

MATCH() AGAINST()?更多使用方法可查看MySQL參考手冊：

https://dev.mysql.com/doc/refman/5.6/ja/fulltext-boolean.html