More and more the websites I develop need to support multiple languages. Until these languages descend from Latin alphabet, like English and Italian, the MySQL Latin1 collation does a pretty good job.
However, when I need to have a website translated into Eastern languages, like Chinese or Japanese, I need to change MySQL collation to
Currently, MySQL supports only the case insensitive version of the UFT8 collation,
utf8_general_ci. As the
utf8_general_cs collation has not been implemented yet, the use of the
*_ci collation will produce case insensitive matches.
One possible solution could be to change the collation to
utf8_bin, which is the binary version; however, MySQL implementation is buggy, so that collation has some problems with the
ORDER BY statement. What I mean is that using
ORDER BY clause will return capital letters separated from their small counterparts, and this is not good.
Therefore, the best solution is the simplest one. What we need to do is changing the collation directly in the
WHERE statement: as a result the query will use the
utf8_bin collation in order to create a case sensitive match leaving data with
SELECT * FROM tb WHERE field COLLATE utf8_bin = "Word";