How can I use a case-sensitive LIKE operator in MySQL?

Diving into BINARY

The BINARY operator in MySQL forces case-sensitive comparisons in LIKE operations, differentiating between uppercase and lowercase characters. Use it when case sensitivity is critical, such as in password verifications or unique identifiers.

SELECT 'abc' LIKE BINARY 'ABC'; -- Returns 0 (false) or as developers say: "Not today abc, not today."


As the example depicts, LIKE BINARY enforces an exact case match.

Making COLLATE work for you

MySQL uses collations to determine how characters are compared. For case-sensitive operations, utf8_bin is your go-to collation—it treats characters in a binary format, making 'A' and 'a' distinct.

SELECT * FROM table WHERE column COLLATE utf8_bin LIKE 'value%'; -- Your ticket to case-sensitive search


You can also use COLLATE to differentiate between 'a%' and 'A%'—precision at your service!

SELECT * FROM table WHERE column COLLATE utf8_general_ci LIKE 'a%' AND column COLLATE utf8_bin LIKE 'A%';


The light and dark sides of case-sensitive search

While BINARY and COLLATE offer precise control, be wary of potential issues:

• Performance: Binary comparisons or specific collations can affect performance—it's a trade-off worth considering.
• Consistency: Ensure your data has the expected format, as binary comparisons might reveal case mismatches.
• Bounds: Make sure patterns are defined correctly; wildcard searches can yield different results depending on case sensitivity.

Extra: Deep dive into case sensitivity

Pattern matching with 'LIKE BINARY'

Using BINARY doesn't only enforce string equality—it carries over to pattern matching:

SELECT * FROM users WHERE BINARY username LIKE 'john%'; -- 'john' and 'john123' will match, 'John' and 'JOHNdoe' won't


Creating case-sensitive views

If consistent case-sensitive data retrieval is what you're after, then MySQL views might be your best friend:

CREATE VIEW case_sensitive_users AS
SELECT * FROM users WHERE column COLLATE utf8_bin LIKE 'pattern%'; -- Setting up the base for case-sensitive operations


Full-text search, case-sensitive style

Full-text indices are generally case-insensitive. But with BINARY in the mix, you can mimic case sensitivity:

SELECT * FROM posts WHERE MATCH(title, body) AGAINST('keyword' IN NATURAL LANGUAGE MODE) 
AND title LIKE BINARY '%keyword%'; -- Full-text search just got a little bit cooler...
