Problem
I have a function that returns five mixed-case characters. If I run a query on this string, the value will be returned regardless of case.
What is the best way to make MySQL string queries case-sensitive?
Asked by StevenB
Solution #1
The good news is that creating a case-sensitive query is simple:
SELECT * FROM `table` WHERE BINARY `column` = 'value'
Answered by Craig White
Solution #2
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin
Answered by drudge
Solution #3
Craig White’s answer includes a significant performance penalty.
SELECT * FROM `table` WHERE BINARY `column` = 'value'
It doesn’t use indexes because it doesn’t use them. So, either you need to alter the table collation as described here: https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html, or you need to modify the table collation as described here: https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html.
OR
The simplest solution is to use a BINARY of value.
SELECT * FROM `table` WHERE `column` = BINARY 'value'
E.g.
mysql> EXPLAIN SELECT * FROM temp1 WHERE BINARY col1 = "ABC" AND col2 = "DEF" ;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | temp1 | ALL | NULL | NULL | NULL | NULL | 190543 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
VS
mysql> EXPLAIN SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | temp1 | range | col1_2e9e898e | col1_2e9e898e | 93 | NULL | 2 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
enter code here
1 row is included in the collection (0.00 sec)
Answered by Nitesh
Solution #4
You might wish to use LIKE or LIKE BINARY instead of the = operator.
// this returns 1 (true)
select 'A' like 'a'
// this returns 0 (false)
select 'A' like binary 'a'
select * from user where username like binary 'a'
It will accept ‘a’ rather than ‘A’ in its conditions.
Answered by insoftservice
Solution #5
The most accurate way to execute a case-sensitive string comparison without modifying the collation of the query column is to specifically define a character set and collation for the value being compared to.
select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;
The binary operator is not recommended because it compares the encoded strings’ real bytes. When the actual bytes of two strings encoded using different character sets are compared, they may not be equivalent. For example, if your column uses the latin1 character set and your server/session character set is utf8mb4, when you compare the column to a string having an accent like ‘café,’ it will not match rows containing that same string! This is due to the fact that in latin1, é is represented by the byte 0xE9, whereas in utf8, it is represented by two bytes: 0xC3A9.
The character set must be compatible with the collations. If your server or session is set to use the latin1 character set, you must use collate latin1 bin; if it is set to use the utf8mb4 character set, you must use collate utf8mb4 bin. As a result, the most reliable option is to translate the value to the most flexible character set possible and then utilize the binary collation for that character set.
When you apply a converting function to a column before comparing it, the query engine is prevented from using an index for that column if one exists, which can significantly slow down your query. As a result, wherever possible, it is always preferable to change the value. When a comparison is made between two string values and one of them has an explicitly defined collation, the query engine will apply the explicit collation to both values.
It’s worth noting that for columns with a _ci collation (which is usually the default), MySql is not only case insensitive, but also accent insensitive. This means that ‘é’ is the same as ‘e’. String comparisons are accent and case sensitive when using a binary collation (or the binary operator).
In MySql, utf8 is an alias for utf8mb3, which has been deprecated in recent versions because it does not support 4 byte characters (which is necessary for encoding strings like ). The utf8mb4 charset should be used with MySql if you want to use UTF8 character encoding.
Answered by Paul Wheeler
Post is based on https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql