MySql fuzzy query json keyword retrieval solution example

Preface
Option 1:
Option 2:
Option 3:
Option 4 (finally adopted option):
Summarize

Preface

Recently, I encountered such a requirement in the project: I need to retrieve a result set containing specified content in the data table. The data type of this field is text, and the stored content is in json format. The specific table structure is as follows:

CREATE TABLE `product` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Product name' COLLATE 'utf8mb4_general_ci',
	`price` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT 'Product price',
	`suit` TEXT NOT NULL COMMENT 'Applicable store json format to save the store ID' COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(3) NOT NULL DEFAULT '0' COMMENT 'Status 1-Normal 0-Deleted 2-Delisted',
	`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Release time',
	`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='Product Table'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

The table data is as follows:

Current requirement: Find the data containing 10001 in suit->hotel.

Through Google and Baidu search, we can find the following solutions:

Option 1:

select * from product where suit like '%"10001"%';
#Like method cannot use indexes, has poor performance, and lacks accuracy

Option 2:

select * from product where suit LOCATE('"10001"', 'suit') > 0;
# The LOCATE method has the same problem as the LIKE method

Option 3:

select * from product where suit != '' and json_contains('suit'->'$.hotel', '"10001"');
#Search with MySQL built-in json function, which requires MySQL 5.7 or above to support, with high accuracy, and cannot use full-text index

Option 4 (finally adopted option):

select * from product where MATCH(suit) AGAINST('+"10001"' IN BOOLEAN MODE);
#You can use full-text indexing. The default limit for MySQL keywords is at least 4 characters. You can modify ft_min_word_len=2 in mysql.ini to take effect after restarting.

For more usage of MATCH() AGAINST(), see the MySQL Reference Manual:

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

Summarize

This is the end of this article about MySql fuzzy query json keyword retrieval solution example. For more relevant MySql json keyword retrieval content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Example code for converting Mysql query result set into JSON data
Detailed explanation of querying JSON format fields in MySQL
MySQL json format data query operation
Summary of related functions for Mysql query JSON results
Which is faster among MySQL full-text index, joint index, like query, and json query?
Two query methods when the MySQL query field type is json
Python query mysql, return json instance
How to query json in the database in mysql5.6 and below
Mysql directly queries the data in the stored Json string

<<: HTML image img tag_Powernode Java Academy

>>: JavaScript immediate execution function usage analysis