MySQL Warning 1287: BINARY Operator Is Deprecated

MySQL Warning 1287 reports use of the expression operator BINARY expr, which converts a string expression to a binary string. MySQL deprecated this operator in 8.0.27 and recommends CAST(expr AS BINARY) instead. The operator is still present in MySQL 9.7, but the manual says to expect its removal in a future version. See the current MySQL CAST functions and operators reference.

This warning is about the BINARY expr operator. It does not mean that the BINARY(n) and VARBINARY data types or the CAST(... AS BINARY) expression are deprecated.

Replace BINARY expr with CAST

These expressions produce the same binary-string conversion:

-- Deprecated operator
SELECT BINARY 'a' = 'A';

-- Recommended replacement
SELECT CAST('a' AS BINARY) = CAST('A' AS BINARY);

Both comparisons return 0: a binary string comparison uses byte values, so lowercase a and uppercase A are different. The binary-string comparison also treats trailing spaces as significant.

Use the same replacement in a predicate:

-- Before
SELECT *
FROM users
WHERE BINARY username = 'Admin';

-- After
SELECT *
FROM users
WHERE CAST(username AS BINARY) = CAST('Admin' AS BINARY);

The same conversion works in LIKE, ORDER BY, and join conditions:

SELECT *
FROM files
WHERE CAST(filename AS BINARY) LIKE CAST('%.PDF' AS BINARY);

MySQL documents CAST(expr AS BINARY) as equivalent to BINARY expr for converting an expression to a binary string.

When a _bin collation is different

A binary string comparison is byte-based. A _bin collation on a nonbinary character string compares character code values and follows that collation’s padding rules. These approaches can differ for multibyte characters and trailing spaces; _bin collations can also have different PAD SPACE or NO PAD behavior. See MySQL’s comparison of the binary collation and _bin collations.

If you want case-sensitive character comparison for a utf8mb4 column rather than a byte-string comparison, you can choose an appropriate collation, for example:

SELECT *
FROM users
WHERE username COLLATE utf8mb4_0900_bin = 'Admin';

Use this only when its character and trailing-space comparison rules match your requirement; it is not a byte-for-byte replacement for CAST(... AS BINARY).

Check the result before migrating

Before replacing deprecated expressions in application queries or stored programs:

1. Find uses of the BINARY expr operator. Do not change BINARY(n) column definitions just because they contain the same word.
2. Replace the operator with CAST(expr AS BINARY) where bytewise comparison is intended.
3. Test case differences, multibyte text, and trailing spaces against representative data.
4. Use a _bin collation only when character-based collation behavior is what the application needs.

This keeps the comparison behavior explicit while removing the deprecated expression operator.