MySql match against工具详细用法

create table articles (
    id int primary key auto_increment,
    title varchar(255) not null,
    content text not null,
    fulltext (title, content)  -- 在title和content列上创建全文索引
);

select * from table_name 
where match(column1, column2, ...) against('search_term');

-- 插入数据
insert into articles (title, content) values
('mysql tutorial', 'this is a tutorial about mysql.'),
('advanced mysql', 'learn advanced techniques in mysql.'),
('postgresql vs mysql', 'a comparison between postgresql and mysql.');
​
-- 自然语言搜索
select * from articles 
where match(title, content) against('mysql');

select id, title, match(title, content) against('mysql') as score 
from articles 
where match(title, content) against('mysql');

select * from table_name 
where match(column1, column2, ...) against('search_term' in boolean mode);

-- 必须包含mysql，且不包含postgresql
select * from articles 
where match(title, content) against('+mysql -postgresql' in boolean mode);
​
-- 包含mysql或postgresql
select * from articles 
where match(title, content) against('mysql postgresql' in boolean mode);
​
-- 包含以my开头的词
select * from articles 
where match(title, content) against('my*' in boolean mode);
​
-- 包含完整短语"mysql tutorial"
select * from articles 
where match(title, content) against('"mysql tutorial"' in boolean mode);

select id, title, match(title, content) against('mysql') as score 
from articles 
where match(title, content) against('mysql') 
order by score desc;

-- 查看当前配置
show variables like 'innodb_ft_min_token_size';
​
-- 修改配置（需要重启mysql）
set global innodb_ft_min_token_size = 2;

-- 创建表时指定ngram分词器
create table articles (
    id int primary key auto_increment,
    title varchar(255) not null,
    content text not null,
    fulltext (title, content) with parser ngram
);
​
-- 查询时使用ngram分词器
select * from articles 
where match(title, content) against('关键词' in boolean mode);

alter table table_name drop index index_name;

alter table articles drop index title;