How to execute mysql fulltext search query having single quote in where clause?

Question

I am using full text search the query is having single quote in where clause it returning values but not accurate.

SELECT `id`, `itemid`, `title`, 
MATCH( `itemid`, `title`, `product_id`,`p1_name`, `p2_name`, `p3_name`, `p4_name` ) 
AGAINST ( "men\'s" IN BOOLEAN MODE ) AS score FROM `deals` 
WHERE `active_flag`='1' AND 
MATCH( `itemid`, `title`, `product_id`,`p1_name`, `p2_name`, `p3_name`, `p4_name`) 
AGAINST ( "men\'s" IN BOOLEAN MODE ) !=0 
ORDER BY score DESC

it should return the case where key word is exactly "Men's" (dont include " double quotes). but it is returning the cases where men and s exist?

can anybody help me out?

thanks.

i can only see on param with single quotes and it's a number so you don't even need quotes :) hope i got you right... otherwise i have to re-read your post ^^ its late and im at work :D — Dwza, Dec 19 '13 at 14:57
Did you set your ft_min_word_length to something other than the default? Its default is `4`, so `men` would never get indexed. — Marc B, Dec 19 '13 at 14:58
@Dwza see the line AGAINST ( "men\'s" IN BOOLEAN MODE ). i want results which has men's keyword i am taking param from user and user has entered men's. — Manish Malviya, Dec 19 '13 at 15:00
Try using paratheses `AGAINST ( "(men\'s)" IN BOOLEAN MODE ) AS score FROM deals` and in the `AGAINST ( "(men\'s)" IN BOOLEAN MODE ) !=0` — Mad Dog Tannen, Dec 19 '13 at 15:03
http://stackoverflow.com/questions/12316209/full-text-query-with-a-single-quote — Mad Dog Tannen, Dec 19 '13 at 15:05
What MySQL version are you running? Is it innodb or MyISAM fulltext? — Mad Dog Tannen, Dec 19 '13 at 15:21
For me its working. What if you put a `*` instead of a `\`. AGAINST ( "men*'s" IN BOOLEAN MODE ) — Mad Dog Tannen, Dec 19 '13 at 15:24
OK im running innodb fulltext and it works. I will keep trying with a MYISAM... — Mad Dog Tannen, Dec 19 '13 at 15:30
Managed to get it to work. It was the built in stopword list. — Mad Dog Tannen, Dec 19 '13 at 15:55

Mad Dog Tannen · Answer 1 · 2013-12-19T16:07:17.570

Its most likley because of the internal stopword list for MyISAM fulltext.

According to this documentation

The variable value should be the path name of the file containing the stopword list, or the empty string to disable stopword filtering. The server looks for the file in the data directory unless an absolute path name is given to specify a different directory. After changing the value of this variable or the contents of the stopword file, restart the server and rebuild your FULLTEXT indexes.

I changed in my ini file this

+ft_stopword_file = C:\ProgramData\MySQL\MySQL Server 5.6/mysql_ft_stopword_file.txt

Then restart the server, it will start even if the file doesnt exists so you dont have to create one. You will have to rebuild the table after the restart.

I now managed to get results using this query

SELECT * FROM test.fttest 
WHERE MATCH(txt) AGAINST("men's" IN BOOLEAN MODE);

and this also

SELECT * FROM test.fttest 
WHERE MATCH(txt) AGAINST('men\'s' IN BOOLEAN MODE);

It may be a good idea to later build your own stopword list if you have alot of data since no words are considered as stopwords so the indexes will get bigger.

score -1 · Answer 2 · answered Dec 19 '13 at 14:57

-1

Escape a ' with two of them ''

  where name = 'men''s'

answered Dec 19 '13 at 14:57

DavidC

1,842
1
18
32

4

Yes, but that's mysql being compatible with other systems. Normal escaping is a backslash, and changing `men\'s` to `men''s` will NOT fix the problem. it's just reordering the deckchairs on the titanic. – Marc B Dec 19 '13 at 15:06

How to execute mysql fulltext search query having single quote in where clause?

2 Answers2