I'm facing an issue with a MySQL fulltext index when trying to perform a case-insensitive search. I have a large column in my database where data is stored, and I want to be able to search for text regardless of case. For instance, if my data contains the text "be biopharma," I expect the search for "Be" to return this row, but it's not working as intended.

I attempted to perform a case-insensitive search using a fulltext index in MySQL. I used the following SQL query:

SELECT * FROM product WHERE MATCH(product.name) AGAINST (" be*" IN BOOLEAN MODE);

I expected this query to return rows that contain the text "be biopharma" when I search for "Be." However, the query did not return the expected results. This issue arises because MySQL's fulltext search, by default, is case-sensitive. I'm seeking guidance on how to modify the query or configure the fulltext index to correctly perform a case-insensitive search.

CodePudding user response：

If you want to search for 2-character words using full-text check this documentation. you have to do configuration based on the storage engine you use.

Along with that your search "Be" is a stopword check this documentation.

• Create your own Stopword Table [InnoDb Only] as mentioned in the documentation
• Then You have to rebuild your index.