Case Sensitivity in MySQL

Oracle is case sensitive when dealing with equality in select statements. So if we enter names with a capital in the first character position we need to specify the exact case to get a match.

SQL> select * from test_case where name  = 'dave';

no rows selected

SQL> select * from test_case where name  = 'Dave';

NAME
----------
Dave

However MySQL is case insensitive in the same scenario.

mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_case where name  = 'dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.03 sec)

To force MySQL to check for an exact case we can use the binary keyword. This is simply placed before the comparison we want to evaluate.

mysql> select * from test_case where binary name  = 'dave';
Empty set (0.01 sec)

mysql> select * from test_case where binary name  = 'Dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.00 sec)

However you can make a column case senstive by adding the binary keyword to the create table creation statement.

mysql> create table test_case (name varchar(30) binary);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_case where name  = 'dave';
Empty set (0.02 sec)