FULLTEXT2 短语与布尔检索¶
SET experimental_fulltext2_index = 1;
CREATE DATABASE cookbook_text;
CREATE TABLE cookbook_text.docs(id BIGINT PRIMARY KEY, body TEXT, category VARCHAR(20));
INSERT INTO cookbook_text.docs VALUES
(1,'quick brown fox','animal'),(2,'brown quick fox','animal'),
(3,'quick brown dog','animal'),(4,'sleepy owl','bird'),
(5,'quick brown fox','ops');
CREATE FULLTEXT2 INDEX ft ON cookbook_text.docs(body) INCLUDE(category);
SELECT id FROM cookbook_text.docs
WHERE MATCH(body) AGAINST('quick brown fox') ORDER BY id;
SELECT id FROM cookbook_text.docs
WHERE MATCH(body) AGAINST('+quick +fox' IN BOOLEAN MODE)
AND category='animal' ORDER BY id;
DROP DATABASE cookbook_text;
短语查询应返回 1,5;叠加 category='animal' 的布尔查询应返回 1,2。INCLUDE 列可用于附加过滤,但不改变匹配词。对中文分词要选择并验证适合数据的 parser;参见 CREATE FULLTEXT2 INDEX 和源码 短语测试。