MySQL: is a SELECT statement case sensitive?

Question

They are case insensitive, unless you do a binary comparison.

You can lowercase the value and the passed parameter :

SELECT * FROM `table` WHERE LOWER(`Value`) = LOWER("IAreSavage")

Another (better) way would be to use the COLLATE operator as said in the documentation

Comparisons are case insensitive when the column uses a collation which ends with _ci (such as the default latin1_general_ci collation) and they are case sensitive when the column uses a collation which ends with _cs or _bin (such as the utf8_unicode_cs and utf8_bin collations).

Check collation

You can check your server, database and connection collations using:

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

and you can check your table collation using:

mysql> SELECT table_schema, table_name, table_collation 
       FROM information_schema.tables WHERE table_name = `mytable`;
+----------------------+------------+-------------------+
| table_schema         | table_name | table_collation   |
+----------------------+------------+-------------------+
| myschema             | mytable    | latin1_swedish_ci |

Change collation

You can change your database, table, or column collation to something case sensitive as follows:

-- Change database collation
ALTER DATABASE `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- or change table collation
ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- or change column collation
ALTER TABLE `table` CHANGE `Value` 
    `Value` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin;

Your comparisons should now be case-sensitive.

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

String comparison in WHERE phrase is not case sensitive. You could try to compare using

WHERE `colname` = 'keyword'

or

WHERE `colname` = 'KeyWord'

and you will get the same result. That is default behavior of MySQL.

If you want the comparison to be case sensitive, you could add COLLATE just like this:

WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'

That SQL would give different result with this one: WHERE colname COLLATE latin1_general_cs = 'keyword'

latin1_general_cs is common or default collation in most database.

The collation you pick sets whether you are case sensitive or not.

The default is case insensitive, but the next most important thing you should take a look at is how the table was created in the first place, because you can specify case sensitivity when you create the table.

The script below creates a table. Notice down at the bottom it says "COLLATE latin1_general_cs". That cs at the end means case sensitive. If you wanted your table to be case insensitive you would either leave that part out or use "COLLATE latin1_general_ci".

   CREATE Table PEOPLE (

       USER_ID  INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

       FIRST_NAME  VARCHAR(50) NOT NULL,
       LAST_NAME  VARCHAR(50) NOT NULL,

       PRIMARY KEY (USER_ID)

   )

   ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;

If your project is such that you can create your own table, then it makes sense to specify your case sensitivity preference when you create the table.