mysqlmatchagainst

negation with match against

I want search those rows that do not contain "rajasthan" in the state field using match against in MySql.

My query is this:

SELECT * from member where MATCH(state) AGAINST('-rajasthan' IN BOOLEAN MODE)

However, it returns an empty set of result.

What is the problem with this?

Solution

  • I believe this will do the trick:

    SELECT 
  *
FROM 
  Member
WHERE 
  NOT MATCH (state) AGAINST ('+rajasthan' IN BOOLEAN MODE)

    Source: "Show all except" in MySQL Boolean Full-Text Searches

    Beware - I believe this will do a full table scan and this query will not benefit from the FullText index as you might hope.

    As to your empty result:

    Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

    Source