MySQL: is a SELECT statement case sensitive?

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


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


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.

Tags:

Mysql

Select