Friday, March 6, 2009

MySql CaseSensitive Select Trick

By the default, mysql compares the strings case insensitively

INSERT INTO users (email) VALUES ('some@email.com');
INSERT INTO users (email) VALUES ('SoMe@EmAiL.cOm');

--- The following two selects both will return two records

SELECT * FROM users WHERE email = 'some@email.com';
SELECT * FROM users WHERE email = 'SoMe@EmAiL.cOm';

--- Even LIKE select will return the same two records

SELECT * FROM users WHERE email LIKE 'SoMe@EmAiL.cOm';


But there's a trick how you can run a case sensitive select on mysql, you should use the binary comparison for the strings like this.


SELECT * FROM users WHERE email LIKE BINARY 'some@email.com';
SELECT * FROM users WHERE email LIKE BINARY 'SoMe@EmAiL.cOm';


The first select will return the first record only, the second will return the second one.