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?

Flotilla

dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html – Justus

Notice that BINARY is not the same as case sensitive comparison: select 'à' like 'a' // returns true select 'à' like BINARY 'a' // returns false!!! select 'à' like 'a' COLLATE latin1_general_cs // returns true So the suggestion to use BINARY for case sensitive compare is incorrect. – Solanum

@cquezel: So, you're saying that [select 'à' like BINARY 'a'] should return true?? In any case, what has this to do with case sensitive comparisons? – Chuch

@FranciscoZarabozo some people below suggested to use BINARY comparison to do case sensitive comparison. I'm just pointing out that in other languages, this will probably not work as expected as BINARY is not the same as case sensitive. – Solanum

@Solanum I would think that 'à' is a different letter than 'a'. So the comparison between the two should indeed be false whatever the case. – Burger

@StephaneEybert whatever the case? But why do accent insensitive collations exist? again: "select 'à' like 'a' COLLATE latin1_general_cs" returns true. Here is an example: My VISA credit card does not accept accents in my name but my bank does! – Solanum

I chose to have a global configuration and made all the tables case sensitive with: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; – Burger

Note: This can be reproduced without any tables involved: SELECT 'a' = 'A'; # Result: 1. This may depend on a global setting somewhere. – Blackstone

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.

Sensor

any hint on how to do this in phpmyadmin? – Flotilla

@StevenB: Click the column's Edit button, then set the Collation --> i.imgur.com/7SoEw.png – Sensor

@BT To make utf8 column case sensitive you could use bin colation like: SELECT 'email' COLLATE utf8_bin = 'Email' – Quillon

@Sensor How would you declare a column with a case sensitive collation ? – Burger

Here’s an example that works for me: SELECT name FROM ASA WHERE CAST(name AS BINARY) LIKE '%Net%' – Recognizor

@StephaneEybert if you're looking for straight up case sensitivity I have had luck in using varbinary instead of varchar for a field in ut8 table. HTH – Hung

I change the column Collation to latin1_bin but it doesn't work, why? Only put the collation in the query works. – Toffic

@jw_(myself) The reason is MySQL Workbench 6.3 seems to have a bug, when you set the column to binary or set the collumn collation to latin1_bin and Apply, nothing is changed. A solution is rename the column and change the setting at the same time then rename it back. – Toffic

There are very little non binary case sensitive collations in MYSQL. The relatively new utf8mb4 character set seems the mose complete. The ones you are looking for all end in _cs. There are a view in the latin7 charset. I was suprised as comming from a SQLServer environement I was used to have just the same collation in _ci as well as _cs – Somato

Answer 2

Use this to make a case-sensitive query:

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

Condonation

This is exactly what I was looking for. I would it up higher if I could. A question though, what effect does this have on performance? I'm using it on a limited reporting thing, so it's not important in my case, but I am curious. – Mammal

Why is this not the answer? This is exactly what I needed too. – Nikolai

@Mammal If the column was a part of an index, you will suffer a performance hit on queries reliant on that index. To maintain performance, you need to actually alter the table. – Backlog

@David My table needs to be in UTF8 and this was part of an export script so I think the trade off in performance is worth it. But good to know not to use that in frequently run queries. – Mammal

I'd be interested to see the performance difference between this method and the latin1_bin method above. I might have to run some benchmarks when I get chances and post my results. – Chilton

What will this do for UTF-8 strings containing the same character with a different representation, e.g. using a combining character to add an umlaut? These UTF-8 strings could be treated as equal: convert(char(0x65,0xcc,0x88) using utf8) (i.e. e with ¨ added) and convert(char(0xc3,0xab) using utf8) (i.e. ë), but adding BINARY will make them unequal. – Titulary

Also, comparing floating point types gives strange results, where a BINARY comparison will say values are unequal although they seem equal to the naked eye. – Titulary

Tested, this does not work for non-ASCII characters, but it works with the COLLATE latin1_bin solution. – Blueing

@JeanVincent , can you post an example? For me this works SELECT BINARY 'Ñ'='Ñ', BINARY 'Ñ'='ñ' (returns 1 and 0) – Nathanialnathaniel

Could you please show how should it look in 'insert' statement? – Lordling

Could the OP finally come here and accept this answer? We don't come on SO to find copy/pastes of incomprehensive documentation. – Salty

as Jean Vincent stated, this doesn't work with NON-ASCII chars, if you have an entry like "à vendre" it will not find it when you search for BINAY col="à vendre" – Schipperke

This also doesn't work if doing REPLACE INTO based on a unique indexes or similar operations. (As mentioned above it will also kill a server if you have a lot of records.) This answer is appropriate in some circumstances but not all. – Araucania

As a performance example: my query passes from 3,5ms (negligible) to 1.570ms (this is about a second and a half), querying a table with 1.8M rows aprox. – Landfall

I recommend @Nitesh answer because of performance advantage (use BINARY keyword before value not before column to enable indexes)... SELECT * FROM `table` WHERE `column` = BINARY 'value' – Atmometer

This does not seem to be case-sensitive on 10.3.22-MariaDB (using libmysql - 5.6.43) – Saiff

It does seem to be case-sensitive on 10.3.23-MariaDB though – Harveyharvie

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)

Attwood

This does not seem to be case-sensitive on 10.3.22-MariaDB (using libmysql - 5.6.43) – Saiff

I used Craig White's solution for year but after a few page load complaints I took a deeper look, made the change Nitesh recommended and query went from 2.5 seconds to 0.15 seconds. It was not using the index when Binary was before Where. After moving Binary to after Where the index was used. Thank you! – Hid

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

Drugge

This does not seem to be case-sensitive on 10.3.22-MariaDB (using libmysql - 5.6.43) – Saiff

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.

Iodate

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.

Myrlemyrlene

It's worth commenting to say that the above will only help depending on your data - your case insensitive search could potentially return a rather large subset of data. – Incondite

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 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

Chondrite

This does not seem to be case-sensitive on 10.3.22-MariaDB (using libmysql - 5.6.43) – Saiff

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" - ???).

Unfaithful

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.

Hormuz

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;

Hardiness

Need to add declare pSuccess BINARY; at start – Leastwise

Answer 11

For those looking to do case sensitive comparison with a regular expression using RLIKE or REGEXP, you can instead use REGEXP_LIKE() with match type c like this:

SELECT * FROM `table` WHERE REGEXP_LIKE(`column`, 'value', 'c');

Nerveracking

Answer 12

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

Ravenous