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

User · Accepted Answer

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

User · Answer

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

User · Answer

mysql is not case sensitive by default  try changing the language collation to latin1 general cs

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  EXPLAIN SELECT   FROM temp1 WHERE BINARY col1    quot ABC quot  AND col2    quot DEF quot     ---- ------------- -------- ------ --------------- ------ --------- ------ -------- -------------    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 gt  EXPLAIN SELECT   FROM temp1 WHERE col1   BINARY  quot ABC quot  AND col2    quot DEF quot     ---- ------------- ------- ------- --------------- --------------- --------- ------ ------ ------------------------------------    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

User · Answer

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  -

User · Answer

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

User · Answer

No need to changes anything on DB level  just you have to changes in SQL Query it will work   Example -   SELECT   FROM  lt TABLE gt  where userId        iv userId      AND password   BINARY      iv password         Binary keyword will make case sensitive

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

Examples related to mysql

Examples related to sql

Examples related to interop

Examples related to case-sensitive

Examples related to string-comparison