How to Fix MySQL Warning 1287 "'BINARY expr' is deprecated and will be removed in a future release"

The Changing Landscape of MySQL String Comparison

If you’ve recently upgraded your MySQL database or applications, you might have encountered Warning 1287 - a gentle nudge from MySQL that your current approach to string comparisons is living on borrowed time. This warning appears when you use the BINARY operator directly before an expression, a syntax that MySQL has marked for deprecation.

This change reflects MySQL’s ongoing effort to standardize its syntax while maintaining backward compatibility. The warning doesn’t break your application today, but it’s a clear signal to update your code before future versions remove support entirely. Let’s explore why this change is happening and how to adapt your queries.

Understanding the Deprecation Warning

What Does BINARY Do?

The BINARY operator forces a binary comparison of strings, making them case-sensitive and accent-sensitive:

-- Case-sensitive comparison
SELECT * FROM users WHERE BINARY username = 'Admin';  -- Will match 'Admin' but not 'admin'

Why the Change?

MySQL is standardizing its syntax to use CAST(... AS BINARY) instead of the standalone BINARY operator. This change:

Aligns with SQL standards
Provides clearer intent in code
Offers more consistent behavior with other type conversions

Modern Alternatives to BINARY Operator

Using CAST AS BINARY

The recommended replacement is explicit casting:

-- Old way (deprecated)
SELECT * FROM products WHERE BINARY product_code = 'A1b2';

-- New standard
SELECT * FROM products WHERE CAST(product_code AS BINARY) = 'A1b2';

Collation-Based Solutions

For more control, specify a binary collation directly:

-- Using collation for binary comparison
SELECT * FROM documents
WHERE filename = 'Report.pdf' COLLATE utf8mb4_bin;

Changing Column Definition

For permanent case-sensitive behavior, alter the column:

ALTER TABLE users MODIFY username VARCHAR(50) COLLATE utf8mb4_bin;

Practical Conversion Examples

In WHERE Clauses

-- Deprecated
SELECT * FROM logs WHERE BINARY error_code = 'ERR404';

-- Updated
SELECT * FROM logs WHERE CAST(error_code AS BINARY) = 'ERR404';

In ORDER BY Operations

-- Old approach
SELECT * FROM cities ORDER BY BINARY name;

-- New approach
SELECT * FROM cities ORDER BY CAST(name AS BINARY);

With LIKE Operators

-- Case-sensitive LIKE (deprecated)
SELECT * FROM files WHERE BINARY filename LIKE '%.PDF';

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

Handling Complex Expressions

Multiple Column Comparisons

-- Old syntax
SELECT * FROM accounts
WHERE BINARY username = 'Admin' OR BINARY email = '[email protected]';

-- New syntax
SELECT * FROM accounts
WHERE CAST(username AS BINARY) = 'Admin'
   OR CAST(email AS BINARY) = '[email protected]';

In JOIN Conditions

-- Deprecated JOIN
SELECT a.*, b.*
FROM table_a a JOIN table_b b ON BINARY a.code = b.code;

-- Updated JOIN
SELECT a.*, b.*
FROM table_a a JOIN table_b b ON CAST(a.code AS BINARY) = CAST(b.code AS BINARY);

Migration Strategies

Finding All Instances

Locate deprecated usage in your codebase:

-- Search stored procedures
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%BINARY %';

-- Search application code (example grep command)
grep -r "BINARY [a-zA-Z]" /path/to/code/

Gradual Conversion Approach

Identify all occurrences of the warning in logs
Prioritize frequently executed queries first
Test each change in a development environment
Document changes for team awareness

Performance Considerations

Index Usage Implications

Be aware that casting may affect index usage:

-- May prevent index usage
SELECT * FROM products WHERE CAST(sku AS BINARY) = 'ABC123';

-- Better alternative if you control the schema
ALTER TABLE products MODIFY sku VARCHAR(50) COLLATE utf8mb4_bin;
SELECT * FROM products WHERE sku = 'ABC123';  -- Can use index

Temporary vs Permanent Solutions

Evaluate whether to:

Apply one-time CAST operations (temporary fix)
Change column collations (permanent solution)
Use application-layer case conversion (alternative approach)

Conclusion

MySQL’s Warning 1287 serves as an important heads-up about upcoming syntax changes. While the deprecated BINARY operator still works today, proactively updating your code ensures compatibility with future MySQL versions and follows current SQL standards.

The migration path is straightforward—replace standalone BINARY operators with explicit CAST(... AS BINARY) expressions. For permanent case-sensitive behavior, consider altering your column collations instead. Remember to test changes thoroughly, especially for queries where binary comparison behavior is critical to your application logic.

By addressing these warnings now, you’ll future-proof your database code and potentially discover opportunities to optimize your string comparison operations in the process. The small effort required to update these expressions today will prevent disruptive changes when future MySQL releases finally remove the deprecated syntax.