whereBinary(): регістрозалежні запити MySQL у Laravel

Проблема стандартного порівняння рядків у MySQL

Порівняння рядків у MySQL відбувається не безпосередньо, а через collation, який визначає, які відмінності враховуються. Стандартне для Laravel collation utf8mb4_unicode_ci ігнорує регістр символів, акценти та кінцеві пробіли. Це означає, що такий запит:

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

знайде збіги для A7f3B9, a7f3b9, A7F3B9 та A7f3B9 (з пробілами в кінці). Для відображуваних імен це саме те, що потрібно. Але для токенів, slug-ів або будь-яких інших даних, де байти визначають ідентичність, це потенційна помилка, яка чекає на правильний input.

Рішення до Laravel 13.27

Раніше для точного побайтового порівняння доводилося виходити за межі query builder:

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

Нові методи у Laravel 13.27

Laravel 13.27 додає whereBinary() разом із orWhereBinary(), whereNotBinary() та orWhereNotBinary().

Приклади використання

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 ?

Значення все ще прив'язується як параметр, тому нічого не змінюється порівняно з версією whereRaw() щодо параметризації. Але тепер ви зберігаете переваги builder: умова композується з when(), query scopes та іншими умовами запиту, і читається як умова, а не як рядок SQL.

Методи працюють однаково з Eloquent builders:

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

Що насправді змінює "Binary"

BINARY у MySQL приводить операнд до бінарного рядка, що змушує порівняння відбуватися побайтово. Починають мати значення чотири типи відмінностей:

• Регістр. Ada більше не дорівнює ada.
• Акценти. utf8mb4_unicode_ci ігнорує акценти, тому resume співпадає з résumé при звичайному where(). При whereBinary() - ні.
• Кінцеві пробіли. utf8mb4_unicode_ci є PAD SPACE collation, тому 'ada' і 'ada ' вважаються рівними. Бінарне порівняння бачить різну довжину байтів і каже "ні".
• Unicode нормалізація. é написана як один code point і як e плюс комбінований акцент - це різні послідовності байтів. _ci collation може вважати їх рівними; бінарне порівняння - ніколи.

Останні два типи здивовують розробників, оскільки проявляються як успішний пошук, коли він мав би провалитися, а не навпаки.

Підтримка різних БД

MySQL та MariaDB підтримують цю функцію; MariaDB успадковує граматику MySQL, тому не знадобилося нічого специфічного для драйвера. Всі інші БД викидають виняток:

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

Це навмисне рішення, а не прогалина. Postgres та SQLite за замовчуванням порівнюють рядки з врахуванням регістру, тому whereBinary() на цих БД був би або no-op, або заявою, яку драйвер не може виконати. Викидання винятку - це той самий вибір, який робить whereLike() для регістрозалежного пошуку на БД, що не підтримують його.

Це означає, що запит, написаний для MySQL, викине виняток на тестовій базі SQLite. Якщо ваш тестовий набір працює на SQLite, а продакшн база - MySQL, ця умова потребує тесту на MySQL.

Застереження щодо індексів

Індекс на token будується в collation стовпця. Порівняння з бінарним операндом виконується в binary collation, що є іншим, тому MySQL зазвичай не може використати цей індекс для задоволення умови і повертається до сканування рядків.

На малій таблиці це неважливо. На великій звичайний патерн - дозволити індексу звузити вибірку, а бінарному порівнянню - відфільтрувати результат:

DB::table('invites')
    ->where('token', $request->token)        // використовує індекс, без урахування регістру
    ->whereBinary('token', $request->token)  // фільтрує кілька повернутих рядків
    ->first();

Перша умова повертає всі варіанти токена з різним регістром, що майже завжди один рядок, а друга відкидає все, що не є побайтово ідентичним. Ви отримуєте пошук по індексу та точне порівняння.

Краще рішення для постійної точності

Якщо стовпець завжди повинен порівнюватися побайтово, краще рішення - задати йому binary або регістрозалежне collation у міграції, щоб індекс і порівняння узгоджувалися, і жоден запит не мусив про це пам'ятати:

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

Це також виправляє те, чого whereBinary() не може. Унікальний індекс на стовпці з _ci відхиляє Ada, коли ada вже існує, незалежно від того, як ви робите запит. whereBinary() - це інструмент для читання; унікальність визначається collation стовпця.

Зв'язок з регістрозалежним LIKE

whereLike() вже деякий час приймає аргумент caseSensitive, і на MySQL він компілюється в like binary:

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

Обидва методи покривають різні потреби. whereLike() - для пошуку за шаблоном з wildcards, whereBinary() - для рівності. Використовуйте версію з рівністю, коли ви не шукаєте за шаблоном, оскільки = - це порівняння, для якого планувальник запитів має більше опцій.

Автор внеску

Функціонал додано @xiCO2k у PR #61261.