Tuesday, 28 August 2018

MySQL match against in boolean mode does not return anything on the word middle

I've got a problem using Match Against in my MySQL database, and I'm hoping someone can help.

This is the examples of the data in my database:
id   name
1    really bitter chocolate
2    soft cheese

When I run this query:
SELECT * FROM food WHERE (name) LIKE "%bitter%"

This bring back the first result:
1    really bitter chocolate

However its part of a much larger query, and when I run the Match Against code, I don't get anything returned from either of these queries:
SELECT * FROM food WHERE MATCH (name) AGAINST ("bitter")

SELECT * FROM food WHERE MATCH (name) AGAINST ("bitter", IN BOOLEAN MODE)

I have full text searches turned on, and it works when I search the start of the name:
SELECT * FROM food WHERE MATCH (name) AGAINST ("really")

SELECT * FROM food WHERE MATCH (name) AGAINST ("really", IN BOOLEAN MODE)

Both of which returns:
1    really bitter chocolate

I've read through this for solutions: http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html
And I've looked here: mysql WHERE MATCH AGAINST
Can someone please see where I am going wrong or point me in the right direction?
Thanks!
EDIT
Ok as per Woot4Moo great answer below I've changed my code to remove the comma which shouldn't have been there. I've also added in the + and putting it in single quotes but still no luck.
My current query now looks like this:
SELECT * FROM food WHERE MATCH (name) AGAINST ('+bitter' IN BOOLEAN MODE)

But it's returning no results in query browser, and not returning any errors or warnings.

SELECT * FROM food WHERE MATCH (name) AGAINST ("bitter", IN BOOLEAN MODE)

looks like it should be:
SELECT * FROM food WHERE MATCH (name) AGAINST ('+bitter' IN BOOLEAN MODE)

notice how mine has the plus sign + and NO comma ,
Basing it off of the example here
MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier. With this modifier, certain characters have special meaning at the beginning or end of words in the search string. In the following query, the + and - operators indicate that a word is required to be present or absent, respectively, for a match to occur. Thus, the query retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL”:
mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

0 comments:

Post a Comment