Mysql Match against query with special charters search

2
votes

I have following match against query which searches records from database table based on search phrase. 

    SELECT * FROM My_Table WHERE MATCH (catchall) AGAINST ('"horse"' IN BOOLEAN MODE)

This query works properly. When search phrase contains special characters like '(' etc It just skips such special characters. If i search for "(horse)" it gives me same result as it gives for "horse".

    SELECT * FROM My_Table WHERE MATCH (catchall) AGAINST ('"(horse)"' IN BOOLEAN MODE)

Does it mean match against query doesn't work with special characters or am i missing something. Please suggest. Thanks. I tried by removing IN BOOLEAN MODE from the query but it didn't work.

1
phpmysql

1 Answers

2
votes

from the documentation:

Parentheses group words into subexpressions. Parenthesized groups can be nested.

if you want to treat prenthes as "word chars", there are two possibilitys:

If you want to change the set of characters that are considered word characters, you can do so in two ways. Suppose that you want to treat the hyphen character ('-') as a word character. Use either of these methods:

Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.

Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish letters and numbers from other characters. . You can edit the contents in one of the character set XML files to specify that '-' is a “letter.” Then use the given character set for your FULLTEXT indexes.

After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes.

a third way would be to not use MATCH ... AGAINST at all and use LIKE instead - but this might get complicated (if you want to use the other operators of ful-text-searches such as +/-) and slow down your query.