How can I make SQL case sensitive string comparison on MySQL?

Question

I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case.

How can I make MySQL string queries case sensitive?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

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

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

Answer 2

The good news is that if you need to make a case-sensitive query, it is very easy to do:

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

Answer 3

The answer posted by Craig White has a big performance penalty

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

because it doesn't use indexes. So, either you need to change the table collation like mention here https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html.

OR

Easiest fix, you should 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 in set (0.00 sec)

Answer 4

Instead of using the = operator, you may want to use LIKE or LIKE BINARY

// 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 take 'a' and not 'A' in its condition

Answer 5

The most correct way to perform a case sensitive string comparison without changing the collation of the column being queried is to explicitly specify a character set and collation for the value that the column is being compared to.

select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;

Why not use binary?

Using the binary operator is inadvisable because it compares the actual bytes of the encoded strings. If you compare the actual bytes of two strings encoded using the different character sets two strings that should be considered the same they may not be equal. For example if you have a column that uses the latin1 character set, and your server/session character set is utf8mb4, then when you compare the column with a string containing an accent such as 'café' it will not match rows containing that same string! This is because in latin1 é is encoded as the byte 0xE9 but in utf8 it is two bytes: 0xC3A9.

Why use convert as well as collate?

Collations must match the character set. So if your server or session is set to use the latin1 character set you must use collate latin1_bin but if your character set is utf8mb4 you must use collate utf8mb4_bin. Therefore the most robust solution is to always convert the value into the most flexible character set, and use the binary collation for that character set.

Why apply the convert and collate to the value and not the column?

When you apply any transforming function to a column before making a comparison it prevents the query engine from using an index if one exists for the column, which could dramatically slow down your query. Therefore it is always better to transform the value instead where possible. When a comparison is performed between two string values and one of them has an explicitly specified collation, the query engine will use the explicit collation, regardless of which value it is applied to.

Accent Sensitivity

It is important to note that MySql is not only case insensitive for columns using an _ci collation (which is typically the default), but also accent insensitive. This means that 'é' = 'e'. Using a binary collation (or the binary operator) will make string comparisons accent sensitive as well as case sensitive.

What is utf8mb4?

The utf8 character set in MySql is an alias for utf8mb3 which has been deprecated in recent versions because it does not support 4 byte characters (which is important for encoding strings like 🐈). If you wish to use the UTF8 character encoding with MySql then you should be using the utf8mb4 charset.

Answer 6

To make use of an index before using the BINARY, you could do something like this if you have large tables.

SELECT
   *
FROM
   (SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
   BINARY `column` = 'value'

The subquery would result in a really small case-insensitive subset of which you then select the only case-sensitive match.

Answer 7

You can use BINARY to case sensitive like this

select * from tb_app where BINARY android_package='com.Mtime';

unfortunately this sql can't use index, you will suffer a performance hit on queries reliant on that index

mysql> explain select * from tb_app where BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_app | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1590351 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

Fortunately, I have a few tricks to solve this problem

mysql> explain select * from tb_app where android_package='com.Mtime' and BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_app | NULL       | ref  | idx_android_pkg           | idx_android_pkg           | 771     | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

Answer 8

Following is for MySQL versions equal to or higher than 5.5.

Add to /etc/mysql/my.cnf

  [mysqld]
  ...
  character-set-server=utf8
  collation-server=utf8_bin
  ...

All other collations I tried seemed to be case-insensitive, only "utf8_bin" worked.

Do not forget to restart mysql after this:

   sudo service mysql restart

According to http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html there is also a "latin1_bin".

The "utf8_general_cs" was not accepted by mysql startup. (I read "_cs" as "case-sensitive" - ???).

Answer 9

No need to changes anything on DB level, just you have to changes in SQL Query it will work.

Example -

"SELECT * FROM <TABLE> where userId = '" + iv_userId + "' AND password = BINARY '" + iv_password + "'";

Binary keyword will make case sensitive.

Answer 10

Excellent!

I share with you, code from a function that compares passwords:

SET pSignal =
(SELECT DECODE(r.usignal,'YOURSTRINGKEY') FROM rsw_uds r WHERE r.uname =
in_usdname AND r.uvige = 1);

SET pSuccess =(SELECT in_usdsignal LIKE BINARY pSignal);

IF pSuccess = 1 THEN
      /*Your code if match*/
ELSE
      /*Your code if don't match*/

END IF;

Answer 11

mysql is not case sensitive by default, try changing the language collation to latin1_general_cs