how to search special characters in fulltext index mysql

Question

I have created a fulltext index in mysql.

I configured the index by song name, but the song title consisting only of special characters cannot be searched.

music[table] - title[column] is indexed, settings innodb_ft_min_token_size = 1, stopword is disabled.

If the song name is '$$$'.

select * from music where match(title) against('$$$' in boolean mode);
select * from music where match(title) against('"$$$"' in boolean mode);
select * from music where match(title) against('+$$$*' in boolean mode);

None of the above codes work, and even just one $ gives the same result. (numbers, English have been confirmed to operate normally.)

I have a question because I don't know which setting to change after this..!

What is your client's language? (I am checking for whether `$` is being messed with it.) — Rick James, Feb 21 '23 at 00:56
@RickJames Most languages are in English. For $, it is to find 'dollar($$$)'. — Mamochi, Feb 21 '23 at 10:44

comodoro · Accepted Answer · 2023-02-23T08:48:12.290

0

As $ is not considered by MySQL a word character, it is not being added to the index and therefore cannot be used for fulltext search. In this case you do not have the option to search without symbols, which worked for my data. I can see these other possibilities:

Revert to LIKE when searching entirely or mostly for symbols.

select * from music where title LIKE '%$$$%'

Add $ to word characters as detailed in this answer

edited Feb 23 '23 at 08:48

answered Feb 22 '23 at 19:58

comodoro

1,489
1
19
30

This is an impossible in mysql. Thanks for the reply. – Mamochi Feb 23 '23 at 01:54

how to search special characters in fulltext index mysql

1 Answers1