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，重启后生效