Laravel의 새로운 whereBinary (): MySQL의 간단한 대/소문자 구분 쿼리

If you have worked with Laravel and MySQL for a while, you have probably run into this situation:

User::where('name', 'john')->get();

Enter fullscreen mode Exit fullscreen mode

You expect this to find only:

john

Enter fullscreen mode Exit fullscreen mode

But depending on your MySQL collation, it may also match:

John
JOHN
JoHn

Enter fullscreen mode Exit fullscreen mode

This happens because many MySQL string comparisons are case-insensitive by default.

Laravel 13.x now has a small but useful addition to the Query Builder:

whereBinary()

Enter fullscreen mode Exit fullscreen mode

It gives us a clean way to tell the database:

“Compare this value exactly, using a binary comparison.”

The feature was added in Laravel framework PR #61261. It also adds whereNotBinary(), orWhereBinary(), and orWhereNotBinary().

Let’s see how it works.

The problem: normal string comparisons.

Imagine we have a users table:

id | name
---|------
1  | John
2  | john
3  | JOHN
4  | Alice

Enter fullscreen mode Exit fullscreen mode

Now we run:

$users = DB::table('users')
->where('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

Depending on the column’s collation, MySQL may consider these values equal:

John
john
JOHN

Enter fullscreen mode Exit fullscreen mode

So our query can return more records than we expected.

This isn’t a Laravel problem, it’s how MySQL string comparison works with a case-insensitive collation.

The old Laravel solution

Before whereBinary(), you could write a raw expression:

$users = DB::table('users')
->whereRaw('name = BINARY ?', ['john'])
->get();

Enter fullscreen mode Exit fullscreen mode

This works, but it isn’t particularly nice.

You’re mixing Laravel’s query builder with database-specific SQL.

You might also have seen code like this:

DB::table('users')
->where('name', '=', DB::raw('BINARY "john"'))
->get();

Enter fullscreen mode Exit fullscreen mode

Again, it works, but it’s not very expressive.

Laravel’s new method makes the intention much clearer.

Meet whereBinary()

Now we can simply write:

$users = DB::table('users')
->whereBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

That’s much easier to read.

The meaning is:

Find users where name is exactly john, using a binary comparison.

For MySQL, Laravel generates SQL equivalent to:

select *
from `users`
where `name` = binary ?

Enter fullscreen mode Exit fullscreen mode

with john passed as a bound parameter.

This is exactly what the new Laravel tests verify.

What does “binary” actually mean?

This is the important part.

A normal comparison might treat:

john
John
JOHN

Enter fullscreen mode Exit fullscreen mode

as equal.

A binary comparison cares about the actual characters.

So:

john != John
john != JOHN
John != JOHN

Enter fullscreen mode Exit fullscreen mode

But:

john == john

Enter fullscreen mode Exit fullscreen mode

For example:

DB::table('users')
->whereBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

could match:

john

Enter fullscreen mode Exit fullscreen mode

but not:

John
JOHN
JoHn

Enter fullscreen mode Exit fullscreen mode

This is particularly useful when the exact casing matters.

whereNotBinary()

The PR also adds the opposite operation:

whereNotBinary()

Enter fullscreen mode Exit fullscreen mode

For example:

$users = DB::table('users')
->whereNotBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

This means:

Find rows where name is not exactly john.

Laravel generates:

where `name` != binary ?

Enter fullscreen mode Exit fullscreen mode

So if the database contains:

john
John
JOHN
Alice

Enter fullscreen mode Exit fullscreen mode

whereNotBinary('name', 'john') can match:

John
JOHN
Alice

Enter fullscreen mode Exit fullscreen mode

but not:

john

Enter fullscreen mode Exit fullscreen mode

orWhereBinary()

You can also use the binary comparison with OR.

For example:

$users = DB::table('users')
->where('id', 1)
->orWhereBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

This produces the equivalent of:

select *
from `users`
where `id` = ?
or `name` = binary ?

Enter fullscreen mode Exit fullscreen mode

This is useful when you want to combine a normal condition with a case-sensitive condition.

orWhereNotBinary()

And, of course, there is also:

orWhereNotBinary()

Enter fullscreen mode Exit fullscreen mode

For example:

$users = DB::table('users')
->where('id', 1)
->orWhereNotBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

The generated condition is equivalent to:

where `id` = ?
or `name` != binary ?

Enter fullscreen mode Exit fullscreen mode

So we now have four methods:

Method Meaning whereBinary() Exact binary equality whereNotBinary() Not exactly equal orWhereBinary() Binary equality using OR orWhereNotBinary() Binary inequality using OR

An example with usernames

This is probably one of the easiest real-world examples.

Imagine your application has usernames:

john
John
john123
John123

Enter fullscreen mode Exit fullscreen mode

If your application treats usernames as case-sensitive, you might want:

$user = DB::table('users')
->whereBinary('username', 'John')
->first();

Enter fullscreen mode Exit fullscreen mode

Now you’re explicitly asking the database for the exact value:

John

Enter fullscreen mode Exit fullscreen mode

rather than relying on whatever collation happens to be configured on the column.

An example with API keys

Another possible use case is comparing values where the exact string matters.

For example:

$key = DB::table('api_keys')
->whereBinary('key', $providedKey)
->first();

Enter fullscreen mode Exit fullscreen mode

The comparison is binary rather than a normal case-insensitive string comparison.

However, don’t confuse this with password hashing or password security.

You should still store passwords using a proper password hashing algorithm such as Laravel’s password hashing facilities.

whereBinary() is about database string comparison, not password security.

It also works with Eloquent

Because Eloquent uses Laravel’s query builder underneath, you can use the method in an Eloquent query as well:

$user = User::query()
->whereBinary('name', 'john')
->first();

Enter fullscreen mode Exit fullscreen mode

And:

$users = User::query()
->whereNotBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

This makes it convenient when you’re already working with models.

What happens behind the scenes?

The implementation is actually quite small.

Laravel adds a new Binary where type to the query builder.

Conceptually, this:

->whereBinary('name', 'john')

Enter fullscreen mode Exit fullscreen mode

gets stored as a binary where condition.

The MySQL grammar then turns it into:

`name` = binary ?

Enter fullscreen mode Exit fullscreen mode

For whereNotBinary() it becomes:

`name` != binary ?

Enter fullscreen mode Exit fullscreen mode

The value is still passed as a normal query binding, so you don’t need to manually put the value into a raw SQL string.

This is one of the nice things about the new API: you get the database-specific comparison without having to write the raw SQL yourself.

What about PostgreSQL and SQLite?

This is an important detail.

This new feature is not pretending that every database supports the same binary comparison syntax.

The base Laravel grammar throws an exception if the database grammar doesn’t support binary comparisons.

The tests in the PR explicitly verify this behavior for PostgreSQL, SQLite, and SQL Server.

MySQL and MariaDB, on the other hand, compile the condition using:

= binary

Enter fullscreen mode Exit fullscreen mode

or:

!= binary

Enter fullscreen mode Exit fullscreen mode

So if your application uses MySQL or MariaDB, this feature is directly relevant.

If your application supports multiple database engines, keep this database-specific behavior in mind.

When should you use whereBinary()?

A good rule is:

Use it when the exact characters matter.

For example:

->whereBinary('username', $username)

Enter fullscreen mode Exit fullscreen mode

when usernames are case-sensitive.

Or:

->whereBinary('code', $code)

Enter fullscreen mode Exit fullscreen mode

when a code should match exactly.

Or:

->whereBinary('slug', $slug)

Enter fullscreen mode Exit fullscreen mode

if your application intentionally treats slug casing as significant.

When should you NOT use it?

Don’t automatically replace every where() with whereBinary().

For example, if you want a normal user search:

User::where('name', 'john')->get();

Enter fullscreen mode Exit fullscreen mode

you may actually want:

John
john
JOHN

Enter fullscreen mode Exit fullscreen mode

to be considered the same.

In that situation, a case-insensitive comparison is useful.

The important thing is to decide what your application means by “equal”.

One small API, one useful problem solved

The new API isn’t a huge change.

That’s actually what makes it nice.

Instead of writing:

->whereRaw('name = BINARY ?', [$name])

Enter fullscreen mode Exit fullscreen mode

you can now write:

->whereBinary('name', $name)

Enter fullscreen mode Exit fullscreen mode

And instead of:

->whereRaw('name != BINARY ?', [$name])

Enter fullscreen mode Exit fullscreen mode

you can write:

->whereNotBinary('name', $name)

Enter fullscreen mode Exit fullscreen mode

Your intent becomes much easier to understand when reading the code.

The complete example

Here’s a simple example showing all four methods:

// Exact binary match
User::whereBinary('name', 'john')->get();

// Exact binary non-match
User::whereNotBinary('name', 'john')->get();

// Binary match with OR
User::where('active', true)
->orWhereBinary('name', 'john')
->get();

// Binary non-match with OR
User::where('active', true)
->orWhereNotBinary('name', 'john')
->get();

Enter fullscreen mode Exit fullscreen mode

Summay

whereBinary() is a small addition, but it solves a common source of confusion when working with MySQL and case-sensitive values.

The biggest benefit isn’t that it makes something possible that you couldn’t do before. You could already use BINARY through raw SQL.

The benefit is that Laravel now gives us a clear query-builder API for saying:

whereBinary('name', 'john')

Enter fullscreen mode Exit fullscreen mode

instead of having to drop down into:

whereRaw('name = BINARY ?', ['john'])

Enter fullscreen mode Exit fullscreen mode

If you’re using MySQL or MariaDB and you need an exact, case-sensitive string comparison, whereBinary() is a much cleaner way to express that intent.