mysqlmatchcase-sensitiveagainst

MySQL Match() Against() Case-Sensitive

Currently, my database is charset Latin1 meaning 

SELECT * FROM TABLE MATCH(column1) AGAINST('"words here"' IN BOOLEAN MODE)

will only return in-sensitive searches. The problem though is that my database will be searched with in-sensitive and case-sensitive searches. Is there a way to solve this so that I can use the same table to do the searches? Is that even possible or will I be forced to make a table with latin1_bin charset to query if the user's search is case sensitive.

Solution

  • A naïve approach comes to my mind: store your text twice in two distinct columns (with two distinct indexes). The one with latin1_bin (for case-sensitive searches), the other with latin1_*_ci (for case-insensitive searches).

    Side notes:

    1. this is a matter of collation, not character encoding (a.k.a. charset)
    2. using a binary collation (such as latin1_bin) has other side effects, such as treating accented characters as different to their non-accented counterpart (e.g. "é" <> "e").