FULLTEXT2 phrase and Boolean search¶
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;
The phrase query should return 1,5; the Boolean query with category='animal' should return 1,2. An INCLUDE column can support an additional filter but does not change the search terms. For Chinese text, choose and verify a parser appropriate for your data. See CREATE FULLTEXT2 INDEX and the source phrase test.