MySQL fulltext index检索中文有哪些注意事宜

发布时间：2023-08-28 12:32:31 所属栏目：MySql教程来源：

导读：本篇内容介绍了“MySQL fulltext index检索中文有哪些注意事项”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，

本篇内容介绍了“MySQL fulltext index检索中文有哪些注意事项”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、查看表结构

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;

2、在name字段创建fulltext索引

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、利用fulltext index进行检索

mysql> select name from product_test02 where match(name) against ('头部' in boolean mode) limit 1;

+---------------------+

| name |

+---------------------+

| 头部按*摩+拨筋 |

+---------------------+

1 row in set (0.00 sec)

mysql> select name from product_test02 where match(name) against ('头' in boolean mode) limit 1;

Empty set (0.00 sec)

经查询：这个是数据库ft_min_word_len参数有关，默认为4，至少检索4个字符，被检索字符串长度小于4个字符将检索不到。

4、改参数ft_min_word_len = 1并重启实例

mysql> show variables like 'ft%';

+--------------------------+----------------+

| Variable_name | Value |

+--------------------------+----------------+

| ft_boolean_syntax | + -><()~*:""&| |

| ft_max_word_len | 84 |

| ft_min_word_len | 1 |

| ft_query_expansion_limit | 20 |

| ft_stopword_file | (built-in) |

+--------------------------+----------------+

5、再次查询

mysql> select name from product_test02 where match(name) against ('头' in boolean mode) limit 1;

Empty set (0.01 sec)

依然查询不到，原因是ft_min_word_len 参数改完之后，必须重建所有fulltext index

6、重建fulltext index并检索

mysql> select name from product_test02 where match(name) against ('头部' 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)

经查询，ngram_token_size=2 #用中文检索分词插件ngram之前，先得在MySQL配置文件里面设置他的分词大小

7、更改参数ngram_token_size=1，并重启实例

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 ('头部' in boolean mode) limit 1;

+---------------------------------------------+

| name |

+---------------------------------------------+

| 【头疼必拍】头部舒压+经络疏通 |

+---------------------------------------------+

1 row in set (0.01 sec)

mysql> select name from product_test02 where match(name) against ('头' in boolean mode) limit 1;

+--------------------------------------------------+

| name |

+--------------------------------------------------+

| 头部拨筋头晕头痛失眠【头部调理】 |

+--------------------------------------------------+

1 row in set (0.01 sec)

可以正常检索。

“MySQL fulltext index检索中文有哪些注意事项”的内容就介绍到这里了，感谢大家的阅读。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!