CODEWITHSUNDEEP
mysqlfull-text-search
Jens Törnell
Jens Törnell

Reputation: 24768

MySQL boolean match phrase does not work

I use fulltext boolean match https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

Works

Matches bear, skip the rest:

+bear

Works

Matches bear OR teddy, skip the rest:

+(bear teddy)

Doesn't work

The expected behavior to me is bear OR teddy OR me and my, skip the rest. It does not work that way as I don't get the results.

+(bear teddy '"me and my"')

How can I get the expected results?

Upvotes: 1

Views: 205

Answers (1)

Nick
Nick

Reputation: 147166

I think you're misinterpreting the documentation. me and my just needs to be enclosed in double quotes, not single and double quotes as you have done. So your query should look something like

SELECT * FROM table WHERE MATCH(column) AGAINST('+(bear teddy "me and my")' IN BOOLEAN MODE)

Consider this example:

CREATE TABLE table1 (txt VARCHAR(512));
INSERT INTO table1 VALUES('here is a bear'), ('this is a teddy'),
('nothing to see here'), ('only me'), ('me and someone else'), ('oh my'),
('me and my children'), ('a teddy bear'), ('me and my teddy bear'),
('love me'), ('what about me and my life');
ALTER TABLE table1 ADD FULLTEXT idx(txt);
SELECT * FROM table1 WHERE MATCH(txt) AGAINST('+(bear teddy "me and my")' IN BOOLEAN MODE)

Output:

txt
here is a bear
this is a teddy
me and my children
a teddy bear
me and my bear
what about me and my life

If I change the query to

SELECT * FROM table1 WHERE MATCH(txt) AGAINST('+(bear teddy "me and all my")' IN BOOLEAN MODE)

I lose the "me and my children" value, but keep "me and my teddy bear" as it contains "teddy" and "bear".

txt
a teddy bear
me and my teddy bear
here is a bear
this is a teddy

In none of the cases do values which only include one of the words "me", "and" or "my" get selected.

SQLFIDDLE

Upvotes: 3

Related Questions