发布时间:2023-03-03 17:00
use test;
show variables like 'innodb_ft_min_token_size';
## innodb_ft_min_token_size 2
show variables like 'ft%';
## ft_min_word_len 1
REPAIR TABLE product;
show create table product;
CREATE TABLE `product` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL DEFAULT '' COMMENT '商品名称',
PRIMARY KEY (`id`),
FULLTEXT KEY `full_title` (`title`) with parser ngram
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
## 注意这里一定要用到 with parser ngram
alter table product add FULLTEXT KEY `full_title` (`title`) with parser ngram;
insert into product(title)values('青岛啤酒'),('雪花啤酒'),('哈尔滨啤酒'),('牛栏山二锅头'),('红星二锅头'),('are you ok?'),('how are you'),('how do you do'),('what are you doing now?'),('hello');
select * from product;
select * from product where match(title) against('you' In natural language mode);
select * from product where match(title) against('哈尔滨');
select * from product where match(title) against('"青岛"' IN BOOLEAN MODE);
## 重建索引
REPAIR TABLE product QUICK;
ALTER TABLE product ENGINE=INNODB;
OPTIMIZE TABLE product;