CODEWITHSUNDEEP
mysqlsearchfull-text-search
Ali
Ali

Reputation: 1678

FullText search query in MySQL

I've the following Table Structure:

id           bigint(20)
loc          text   
lastmod      datetime
changefreq   varchar(15)
priority     float
isdownloaded tinyint(1)
mainrepoid   bigint(20)

FullText is enabled on loc having links, I want to extract all the links having both 'name' and 'details' words but should not include 'character' word. I am using the following query:

SELECT *
FROM links
WHERE MATCH (
   loc
)
AGAINST (
   'name+details-character'
)

But the returned results also have 'character' word in them. In short I actually want all the urls with this structure example.com/name/id/details from the loc field.

Upvotes: 1

Views: 1279

Answers (2)

ravnur
ravnur

Reputation: 2852

Also you should check system vars ft_max_word_len and ft_min_word_len because fulltext search may skip some records depending on word's length.

sys vars

Upvotes: 0

matthiasmullie
matthiasmullie

Reputation: 2083

Like Jauzsika said in the comment, you should put spaces between it.

If "name" must be present, you should also add a plus in front of it.

Furthermore; these operators only work in boolean mode. You should add IN BOOLEAN MODE to your AGAINST-statement, like this:

SELECT *
FROM links
WHERE MATCH (
   loc
)
AGAINST (
   '+name +details -character' IN BOOLEAN MODE
)

Upvotes: 1

Related Questions