MySql模糊查询json关键字检索方案示例

MySql模糊查询json关键字检索方案示例 目录

• 前言
• 方案一：
• 方案二：
• 方案三：
• 方案四（最终采用方案）：
• 总结

前言

​最近在项目中遇到这样一个需求：需要在数据表中检索包含指定内容的结果集，该字段的数据类型为text，存储的内容是json格式，具体表结构如下：

CREATE TABLE `product` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产品名称' COLLATE 'utf8mb4_general_ci',
	`price` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT '产品价格',
	`suit` TEXT NOT NULL COMMENT '适用门店 json格式保存门店id' COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '状态 1-正常 0-删除 2-下架',
	`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
	`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='产品表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

表数据如下：

 

现需求：查找 suit->hotel 中包含10001的数据。

通过谷歌百度查找，大致找到以下几种方案：

方案一：

select * from product where suit like '%"10001"%';
#like方式不能使用索引，性能不佳，且准确性不足

方案二：

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

方案三：

select * from product where suit != '' and json_contains('suit'->'$.hotel', '"10001"');
#以MySQL内置json函数查找，需要MySQL5.7以上版本才能支持，准确性较高，不能使用全文索引

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

select * from product where MATCH(suit) AGAINST('+"10001"' IN BOOLEAN MODE);
#可使用全文索引，MySQL关键字默认限制最少4个字符，可在mysql.ini中修改 ft_min_word_len=2，重启后生效

MATCH() AGAINST() 更多使用方法可查看MySQL参考手册：

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

总结

到此这篇关于MySql模糊查询json关键字检索方案示例的文章就介绍到这了,更多相关MySql json关键字检索内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！