MySQL: is a SELECT statement case sensitive?

user_409958 0
Viewed 345148

Can anyone tell me if a MySQL SELECT query is case sensitive or case insensitive by default? And if not, what query would I have to send so that I can do something like:

SELECT * FROM `table` WHERE `Value` = "iaresavage"

Where in actuality, the real value of Value is IAreSavage.

select mysql
14 Answers

USE BINARY

This is a simple select

SELECT * FROM myTable WHERE 'something' = 'Something'

= 1

This is a select with binary

SELECT * FROM myTable WHERE BINARY 'something' = 'Something'

or

SELECT * FROM myTable WHERE 'something' = BINARY 'Something'

= 0

user_12838060

Marc B's answer is mostly correct.

If you are using a nonbinary string (CHAR, VARCHAR, TEXT), comparisons are case-insensitive, per the default collation.

If you are using a binary string (BINARY, VARBINARY, BLOB), comparisons are case-sensitive, so you'll need to use LOWER as described in other answers.

If you are not using the default collation and you are using a nonbinary string, case sensitivity is decided by the chosen collation.

Source: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html. Read closely. Some others have mistaken it to say that comparisons are necessarily case-sensitive or insensitive. This is not the case.

user_65485010

You can try it. hope it will be useful.

SELECT * FROM `table` WHERE `Value` COLLATE latin1_general_cs = "IAreSavage"
user_88453870

String fields with the binary flag set will always be case sensitive. Should you need a case sensitive search for a non binary text field use this: SELECT 'test' REGEXP BINARY 'TEST' AS RESULT;

user_22885800

In my case neither BINARY nor COLLATE nor CHARACTER SET works with my UTF8 table.

I have usernames in my table like henry, Henry, susan, Susan or suSan and find the respective users by comparing the byte sequences of the names.

The following function creates the byte sequences:

function makeByteString($string){
    $tmp = "";
    for($i=0;$i<strlen($string);$i++){
        $sign = substr($string,$i,1);
        $tmp.=ord($sign);
    }
    return $tmp;
}

The SQL query finds the correct id:

$sql = "SELECT id, username FROM users WHERE `username` = ? ";
$stmt = $conn->prepare($sql);
$stmt->execute([$strUsername]); //e.g. susan, Susan or suSan
$rows = $stmt->rowCount();
if($stmt && $rows>0){
  while ($row = $stmt->fetch()) {
    if(makeByteString($strUsername) == 
                   makeByteString(trim($row["username"]))){
      $id = $row['id'];
    }
  }
}
user_60070770
Related