本篇内容介绍了“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检索中文有哪些注意事项”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。