MySQL case sensitivity
No Comments »
Consider a table tbl_01 containing a column Name and consider the following query :
SELECT * FROM tbl_01 WHERE name = ‘paul’
In Oracle this query would return only the rows containing ‘paul’ in the column name, because Oracle is case sensitive.
MySQL is not, so the same query would return rows containing ‘Paul’, ‘paul’, ‘pAul’, etc. In order to force MySQL to check for the exact case, the keyword binary has to be used :
SELECT * FROM tbl_01 WHERE binary name = “paul”
There is also an alternative to this – making a column case sensitive on table creation:
CREATE TABLE tbl_01 (name varchar(100) binary);
Now the select on name cares for the way the name is written.
