MySQL BINARY Operator

MySQL BINARY Operator

The MySQL BINARY operator converts an expression into a binary byte string (VARBINARY) so comparisons check raw byte values instead of character collation rules. Developers traditionally placed BINARY before a column or string literal to force case-sensitive lookups on case-insensitive columns.

Syntax

BINARY expr

• expr: Any string or numeric expression to cast into a binary byte string.

Note that BINARY is a unary operator, not a function with parentheses, even though people often group expressions like BINARY (expr). Deprecation notice: MySQL 8.0.27 deprecated the BINARY expr operator and plans to remove it in a future release. Use CAST(expr AS BINARY) or a _bin / _cs collation instead.

Practical Example

When demo_customers.referral_code uses a default case-insensitive collation (utf8mb4_0900_ai_ci), 'VIP20' matches 'vip20'. Using CAST or BINARY enforces an exact byte match.

SELECT
    customer_id,
    full_name,
    referral_code
FROM demo_customers
WHERE CAST(referral_code AS BINARY) = CAST('VIP20' AS BINARY);

Common Mistakes & NULL Behavior

• NULL handling: Evaluating BINARY NULL returns NULL without throwing an error. Comparing BINARY col = BINARY 'abc' yields NULL (falsy in WHERE) whenever col is NULL.
• Index scan loss: Writing WHERE BINARY referral_code = 'VIP20' prevents MySQL from using a standard B-tree index on referral_code unless you add a functional index or alter the column's collation directly to utf8mb4_bin.
• Trailing spaces and UTF-8 bytes: BINARY compares raw bytes, so trailing spaces and multi-byte accents no longer fold together. Use COLLATE utf8mb4_0900_as_cs if you want character-aware case and accent sensitivity.
• Deprecated syntax: Avoid writing new queries with BINARY col; switch to CAST(col AS BINARY) to prevent upgrade warnings in MySQL 8.0+.

Frequently Asked Questions

Is BINARY() a MySQL function or an operator?

BINARY is a cast operator, not a function. Writing BINARY(col) only works because MySQL treats the parentheses as grouping around the operand col.

Why is the BINARY operator deprecated in MySQL 8.0?

MySQL deprecated BINARY expr in 8.0.27 to standardize type conversion syntax on CAST(expr AS BINARY) and explicit COLLATE clauses.

What is a faster alternative to BINARY in WHERE clauses?

Define the column with a binary or case-sensitive collation (such as utf8mb4_bin) or write col COLLATE utf8mb4_bin = 'VIP20'.