CODEWITHSUNDEEP
mysqlmatchagainst
Deepa
Deepa

Reputation: 1301

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?

Upvotes: 3

Views: 4980

Answers (2)

Tudor Constantin
Tudor Constantin

Reputation: 26861

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.”

So try appending to your query OR 1 = 1 so that the query to return all your records without MATCH operator

Upvotes: -1

Brian Webster
Brian Webster

Reputation: 30865

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

Upvotes: 12

Related Questions