whereBinary(): Case-Sensitive MySQL Queries in Laravel

A MySQL string comparison does not compare strings. It compares them through a collation, and the collation decides which differences count. Laravel's default utf8mb4_unicode_ci treats case as noise, treats accents as noise, and ignores trailing spaces. So this query:

DB::table('invites')->where('token', $request->token)->first();

matches on A7f3B9, a7f3b9, A7F3B9, and A7f3B9 . For a display name that is exactly what you want. For a token, a slug, or anything else where the bytes are the identity, it is a bug waiting for the right input.

Getting a byte-exact comparison used to mean leaving the query builder:

DB::table('invites')->whereRaw('token = BINARY ?', [$request->token])->first();

Laravel 13.27 adds whereBinary(), along with orWhereBinary(), whereNotBinary(), and orWhereNotBinary().

The Methods

DB::table('invites')->whereBinary('token', $request->token)->first();
// select * from `invites` where `token` = binary ?
 
DB::table('users')->whereNotBinary('username', $username)->get();
// select * from `users` where `username` != binary ?
 
DB::table('users')
    ->where('id', $id)
    ->orWhereBinary('username', $username)
    ->get();
// select * from `users` where `id` = ? or `username` = binary ?

The value is still bound, so nothing about parameterization changes compared to the whereRaw() version. What changes is that you keep the builder: the clause composes with when(), with query scopes, and with the rest of the conditions on the query, and it reads as a condition rather than as a string of SQL.

They work on Eloquent builders the same way:

$invite = Invite::query()
    ->whereBinary('token', $request->token)
    ->where('expires_at', '>', now())
    ->firstOrFail();

What "Binary" Actually Changes

BINARY in MySQL casts the operand to a binary string, which forces the comparison to be byte-for-byte. Four kinds of difference start mattering:

Case. Ada no longer equals ada.
Accents. utf8mb4_unicode_ci is accent insensitive, so resume matches résumé under a plain where(). Under whereBinary() it does not.
Trailing whitespace. utf8mb4_unicode_ci is a PAD SPACE collation, so 'ada' and 'ada ' compare equal. A binary comparison sees different byte lengths and says no.
Unicode normalization. é written as one code point and as e plus a combining accent are different byte sequences. A _ci collation may treat them as equal; a binary comparison never will.

The last two are the ones that surprise people, because they show up as a lookup that succeeds when it should have failed rather than the other way around.

Engine Support

MySQL and MariaDB support it; MariaDB inherits the MySQL grammar, so nothing driver-specific was needed there. Every other engine throws:

RuntimeException: This database engine does not support binary comparison operations.

That is deliberate rather than a gap. Postgres and SQLite already compare strings case sensitively by default, so a whereBinary() on those engines would be either a no-op or a claim the driver cannot honor. Throwing is the same choice whereLike() makes for case-sensitive lookups on engines that do not support them.

It does mean a query written against MySQL will throw on a SQLite test database. If your test suite runs on SQLite and your production database is MySQL, that clause needs a MySQL-backed test.

The Index Caveat

An index on token is built in the column's collation. A comparison against a binary operand is evaluated in the binary collation, which is a different one, so MySQL generally cannot use that index to satisfy the condition and falls back to scanning the rows it has.

On a small table that is irrelevant. On a large one, the usual pattern is to let the index do the narrowing and let the binary comparison do the filtering:

DB::table('invites')
    ->where('token', $request->token)        // uses the index, case insensitively
    ->whereBinary('token', $request->token)  // filters the handful of rows it returned
    ->first();

The first condition returns every case variant of the token, which is nearly always one row, and the second discards anything that is not byte-identical. You get the index lookup and the exact comparison.

If a column should always be compared byte-exactly, the better fix is to give it a binary or case-sensitive collation in the migration, so the index and the comparison agree and no query has to remember.

$table->string('token')->collation('utf8mb4_bin')->unique();

That also fixes something whereBinary() cannot. A unique index on a _ci column rejects Ada when ada already exists, no matter how you query it. whereBinary() is a read-side tool; uniqueness is decided by the column's collation.

Related: Case-Sensitive LIKE

whereLike() has taken a caseSensitive argument for a while, and on MySQL it compiles to like binary:

DB::table('users')->whereLike('username', 'ada%', caseSensitive: true);
// select * from `users` where `username` like binary ?

The two cover different needs. whereLike() is for pattern matching with wildcards, whereBinary() is for equality. Reach for the equality version when you are not matching a pattern, since = is the comparison the query planner has more options for.

Contributed by @xiCO2k in #61261.