21

I have an SQL statement:

SELECT * FROM customers WHERE BINARY login='xxx' AND password='yyyy'

There are no blob/binary fields in the table, do I need the BINARY after the WHERE what else does it do?

3 Answers 3

27

BINARY is a built-in keyword that after your WHERE clause that forces a comparison for an exact case-sensitive match.

As a side note, you can also make columns case-sensitive by adding the BINARY keyword when you create your table.

2
  • 2
    This could be clearer. It implies that the BINARY key word after WHERE is a flag that makes the search case-sensitive, which is somewhat misleading. As DVK points out the BINARY key word casts the string following it to a binary string. Case-sensitivity is a side-effect of that cast and it only affects that one word. The BINARY has to be repeated in every AND or OR clauses if you want them to also be case-sensitive. Binary can also be used in SELECT statements.
    – Bob Ray
    Mar 29, 2019 at 21:13
  • 1
    this answer saved my day even 11 years on
    – Dion
    Feb 10, 2022 at 9:29
9

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#operator_binary

The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character. BINARY also causes trailing spaces to be significant.

0
7
SELECT 'user' = 'UsEr' // true
SELECT BINARY 'user' = 'UsEr' // false

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.