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.