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

1、查看表结构

mysql>showcreatetableproduct_test02;|Table|CreateTable|product_test02|CREATETABLE`product_test02`(`product_id`varchar(40)CHARACTERSETutf8COLLATEutf8_binNOTNULL,`artisan_id`varchar(40)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`name`varchar(60)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',`des`varchar(1000)CHARACTERSETutf8mb4COLLATEutf8mb4_binNOTNULLDEFAULT'',`zhima_price`double(11,2)DEFAULTNULL,`market_price`double(11,2)DEFAULTNULL,`cover_pic`varchar(100)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`work_time`int(11)DEFAULTNULL,`comment_count`int(11)DEFAULTNULL,`like_count`int(11)DEFAULTNULL,`produt_consist`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`keep_time`int(11)DEFAULTNULL,`create_at`timestampNULLDEFAULTNULL,`fav_count`int(11)DEFAULTNULL,`width`int(11)DEFAULTNULL,`height`int(11)DEFAULTNULL,`is_publish`int(11)DEFAULTNULL,`is_top`int(11)DEFAULTNULL,`is_delete`int(11)DEFAULTNULL,`sell_amount`int(11)DEFAULT'0',`free_service_time`int(11)DEFAULTNULL,`update_time`timestampNULLDEFAULTNULL,`other_1`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`other_2`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`other_3`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`other_4`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`other_5`varchar(255)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`is_audit`tinyint(1)DEFAULT'0',`audit_time`timestampNULLDEFAULTNULL,`is_undercarriage`tinyint(1)DEFAULT'0',`undercarriage_time`timestampNULLDEFAULTNULL,`category`varchar(30)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',`active_tag_dict_id`bigint(20)DEFAULTNULL,`active_price`double(11,1)DEFAULTNULL,`weight`int(11)unsignedDEFAULT'0',`fit_people`varchar(300)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`matter_attent`varchar(800)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`category_lv2_id`int(11)DEFAULTNULL,`artisan_visit`int(1)DEFAULT'1',`customer_visit`int(1)DEFAULT'0',`customer_zhima_price`double(11,2)DEFAULTNULL,`customer_market_price`double(11,2)DEFAULTNULL,`service_sex`int(1)DEFAULT'0',`service_mode`tinyint(2)DEFAULT'0',`last_update_time`datetimeDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`sell_price`double(11,2)NOTNULLDEFAULT'0.00',`is_new`int(1)NOTNULL,`spu_id`int(11)DEFAULTNULL,`category_id`int(11)DEFAULTNULL,`other_info`varchar(100)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',`product_type`int(2)NOTNULLDEFAULT'0',`product_code`varchar(15)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8;

2、在name字段创建fulltext索引

mysql>altertableproduct_test02addFULLTEXTindexft_indx_name(name)WITHPARSERngram;QueryOK,0rowsaffected,1warning(3min45.93sec)

3、利用fulltext index进行检索

mysql>selectnamefromproduct_test02wherematch(name)against('头部'inbooleanmode)limit1;+---------------------+|name|+---------------------+|头部按*摩+拨筋|+---------------------+1rowinset(0.00sec)

mysql>selectnamefromproduct_test02wherematch(name)against('头'inbooleanmode)limit1;Emptyset(0.00sec)

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

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

mysql>showvariableslike'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>selectnamefromproduct_test02wherematch(name)against('头'inbooleanmode)limit1;Emptyset(0.01sec)

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

6、重建fulltext index并检索

mysql>selectnamefromproduct_test02wherematch(name)against('头部'inbooleanmode)limit1;Emptyset(0.00sec)mysql>selectnamefromproduct_test02wherematch(name)against('3'inbooleanmode)limit1;Emptyset(0.00sec)

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

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

mysql>showvariableslike'ng%';+------------------+-------+|Variable_name|Value|+------------------+-------+|ngram_token_size|1|+------------------+-------+1rowinset(0.01sec)mysql>selectnamefromproduct_test02wherematch(name)against('头部'inbooleanmode)limit1;+---------------------------------------------+|name|+---------------------------------------------+|【头疼必拍】头部舒压+经络疏通|+---------------------------------------------+1rowinset(0.01sec)mysql>selectnamefromproduct_test02wherematch(name)against('头'inbooleanmode)limit1;+--------------------------------------------------+|name|+--------------------------------------------------+|头部拨筋头晕头痛失眠【头部调理】|+--------------------------------------------------+1rowinset(0.01sec)

可以正常检索。

“MySQL fulltext index检索中文有哪些注意事项”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！