What are the points for attention in MySQL fulltext index retrieval of Chinese

This article introduces the relevant knowledge of "MySQL fulltext index retrieval Chinese what precautions there are". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. View table structure

mysql> show create table product_test02;| Table | Create Table | product_test02 | CREATE TABLE `product_test02` ( `product_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `artisan_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `des` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', `zhima_price` double(11,2) DEFAULT NULL, `market_price` double(11,2) DEFAULT NULL, `cover_pic` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `work_time` int(11) DEFAULT NULL, `comment_count` int(11) DEFAULT NULL, `like_count` int(11) DEFAULT NULL, `produt_consist` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `keep_time` int(11) DEFAULT NULL, `create_at` timestamp NULL DEFAULT NULL, `fav_count` int(11) DEFAULT NULL, `width` int(11) DEFAULT NULL, `height` int(11) DEFAULT NULL, `is_publish` int(11) DEFAULT NULL, `is_top` int(11) DEFAULT NULL, `is_delete` int(11) DEFAULT NULL, `sell_amount` int(11) DEFAULT '0', `free_service_time` int(11) DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL, `other_1` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `other_2` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `other_3` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `other_4` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `other_5` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `is_audit` tinyint(1) DEFAULT '0', `audit_time` timestamp NULL DEFAULT NULL, `is_undercarriage` tinyint(1) DEFAULT '0', `undercarriage_time` timestamp NULL DEFAULT NULL, `category` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `active_tag_dict_id` bigint(20) DEFAULT NULL, `active_price` double(11,1) DEFAULT NULL, `weight` int(11) unsigned DEFAULT '0', `fit_people` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `matter_attent` varchar(800) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `category_lv2_id` int(11) DEFAULT NULL, `artisan_visit` int(1) DEFAULT '1', `customer_visit` int(1) DEFAULT '0', `customer_zhima_price` double(11,2) DEFAULT NULL, `customer_market_price` double(11,2) DEFAULT NULL, `service_sex` int(1) DEFAULT '0', `service_mode` tinyint(2) DEFAULT '0' , `last_update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sell_price` double(11,2) NOT NULL DEFAULT '0.00', `is_new` int(1) NOT NULL, `spu_id` int(11) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `other_info` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `product_type` int(2) NOT NULL DEFAULT '0' , `product_code` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a fulltext index in the name field

mysql> alter table product_test02 add FULLTEXT index ft_indx_name (name) WITH PARSER ngram;Query OK, 0 rows affected, 1 warning (3 min 45.93 sec)

3. Use fulltext index to retrieve

mysql> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;+-------------------+| name |+---------------------+| Head Press * Rubbing + Stiffening |+----------------------+1 row in set (0.00 sec)mysql> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;Empty set (0.00 sec)

Query: This is related to the ft_min_word_len parameter of the database. The default is 4. At least 4 characters are retrieved. If the length of the retrieved string is less than 4 characters, it will not be retrieved.

4. Change parameter ft_min_word_len = 1 and restart instance

mysql> show variables like 'ft%';+--------------------------+----------------+| Variable_name | Value |+--------------------------+----------------+| ft_boolean_syntax | + -> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;Empty set (0.01 sec)

The query still fails because all fulltext indexes must be rebuilt after ft_min_word_len parameter is changed.

6. Reconstruct fulltext index and retrieve

mysql> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;Empty set (0.00 sec)mysql> select name from product_test02 where match(name) against ('3' in boolean mode) limit 1;Empty set (0.00 sec)

After query, ngram_token_size=2 #Before retrieving the word segmentation plug-in ngram in Chinese, you must first set its word segmentation size in MySQL configuration file

7. Change parameter ngram_token_size=1 and restart instance

mysql> show variables like 'ng%';+------------------+-------+| Variable_name | Value |+------------------+-------+| ngram_token_size | 1 |+-------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;+-----------------------------------| name |+---------------------------------------------+|[Headache must beat] Head pressure relief + meridian dredging |+---------------------------------------------+1 row in set (0.01 sec)mysql> select name from product_test02 where match(name) against ('head ' in boolean mode) limit 1;+-------------------------------------------------------+| name |+--------------------------------------------------+| Headache, headache, insomnia [head conditioning] |+--------------------------------------------------+1 row in set (0.01 sec)

It can be searched normally.

"MySQL fulltext index retrieval Chinese what precautions" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!