MySql fuzzy query json keyword retrieval solution example

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
;

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

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

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

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.