Laravel でLike検索時にCOLLATEを指定したい

Question

前提・実現したいこと

Laravelで一部機能のみ、Like検索時に全角半角・大文字小文字を区別せずに検索したい。
現在SQLにCOLLATE utf8mb4_unicode_ciを含めて検索させる方法をとっているのですが、良いコードの書き方がないか知りたいです。
対象カラムのDB上の COLLATEは utf8mb4_binになっています。

発生している問題・エラーメッセージ

1. whereRawメソッドの第2引数にバインド値を入れた場合

$search_word = "tes't";
User::whereRaw("name LIKE %?% COLLATE utf8mb4_unicode_ci", [$search_word])->get();

$search_word = "tes't";
User::whereRaw("name LIKE ? COLLATE utf8mb4_unicode_ci", ['%' . $search_word . '%'])->get();

select * from `users` where name LIKE %tes't% COLLATE utf8mb4_unicode_ci

どちらも上記のSQLが生成され、シンタックスエラーとなってしまう。

2. 自前でエスケープした場合

$search_word = "tes't";

$search_word = str_replace(['\', "'", '_', '%'], ['\\', "\'", '\_', '\%'], $search_word);
User::whereRaw("name LIKE '%{$search_word}%' COLLATE utf8mb4_unicode_ci")->get();

select * from `users` where name LIKE '%tes\'t%' COLLATE utf8mb4_unicode_ci

この場合SQL自体は想定通りとなってはくれるのですが、書き方がスマートでないです。出来ればエスケープなどはフレームワークに任せたいです。

試したこと

「Laravel whereRaw like」等のキーワードでTeratailや他サイトなど検索してみましたが、有用な回答がみつかりませんでした。

補足情報（FW/ツールのバージョンなど）

Laravel 6.17.0
MariaDB 10.4.12

補足など必要であれば指摘いただければと思います。

Answer 1

だいぶ前の質問ですが一応回答させていただきます。

PDOStatement のエミュレーションが OFF の場合に失敗することに関しては既に回答がある通りですが，「一時的に有効にする」という処理を

• 安全に （途中で例外が起こってもかならず元に戻す）
• 効率的に （PDOインスタンスがまだ解決されていないときには処理を遅延させる）

行うにはやや手間がかかるため，その処理を隠蔽するためのライブラリを作成しました。

• mpyw/laravel-pdo-emulation-control: Temporarily enable/disable PDO prepared statement emulation

これを使うと DB::emulated(function () { ... }) で括ったところだけ効率的な方法で自動的にエミュレーションを ON にしてくれます。

Comments

[Eggpan]

回答ありがとうございます。
ライブラリ化すると読みやすくなりそうですね。参考になります。

Answer 2

なぜwhereRawでうまくいかなかったのか?

User::whereRaw("name LIKE ? COLLATE utf8mb4_unicode_ci", ['%' . $search_word . '%'])->get();

は書き方的には正しくて一見動きそうなのですが、何故かこの場合はうまくいかず謎のエラーが出ます。

これについて調べたところ、Laravelはプレースホルダの処理についてサーバ側で行うように指定(`PDO:ATTR_EMULATE_PREPARE'が'false')していますが、mysqlのなにかの制限により上記のSQL文はプリペアドステートメントとしてうまく扱えないようです。

実際、phpには関係なくcliのmysqlコマンドから以下のようにしてもエラーになります。

mysql> prepare hoge from 'select * from users where name like ? collate utf8mb4_unicode_ci';
ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'

対策

エミュレーションに切り替える

プレースホルダの処理をエミュレーションにするとこの問題はないのであらかじめ

DB::getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

を実行しておくか、config/database.phpのmysqlのところでoptionで指定します。

ただ、エミュレーションにはセキュリティ上の問題があるといえばあるので採用するかどうかは個別の判断になるでしょう。

PDO::ATTR_EMULATE_PREPARES => falseは必要か？に対するockeghemさんの回答が参考になると思います。

個人的にはセキュリティの要求がよほど高くなければ今ならエミュレーションを使ってもいいのではないかと思いますが(そもそもPDO_mysqlはデフォルトがこちら)、判断は分かれるところでしょうね。

設定で全部エミュレーションに切り替えることはせず、これのときだけ個別に変更するというのもありかもしれません。

プレースホルダは使わない

がんばってSQL文を組み立てる方法です。ただこれ実質的にはエミュレーションに切り替えたのと似たようなものであり、エスケープ処理を自分でやっている分さらに危険といえるでしょう。あまりおすすめはできません。

ストアドファンクションを使う

うまく扱えないのは構文の問題なので、問題のある部分をストアドファンクションにしてやれば回避することができます。

例えば

CREATE FUNCTION like_ci(x TEXT,y TEXT) RETURNS BOOLEAN DETERMINISTIC
    RETURN x LIKE y COLLATE utf8mb4_unicode_ci;

としてlike_ciという関数を用意してこれを使います。

User::whereRaw("like_ci(name, ?)", ["%tes't%"])->get()

これでやるのならストアドファンクションの定義はマイグレーションで行うのがいいでしょう。

Comments

[Eggpan]

回答ありがとうございます。
該当SQLの部分のみATTR_EMULATE_PREPARESを変更する対応でエラーも起きず、想定通りのレコードを抽出できました。

ストアドファンクションについてはDBにコードを埋め込みたくない、との事で見送りましたが、今まで利用したことがなかったのでこちらも調べてみたいと思います。ありがとうございました。

Answer 3

以下の記事の回答の方法を利用すれば、もうちょっとlaravelよりの書き方にできるのではないでしょうか。

参考
how to add collate to laravel query

Comments

[Eggpan]

回答ありがとうございます。database.phpに設定を追加し、別コネクションを張る。というやり方みたいなのですが、このやり方ですとコネクションレベルのcollationが変更される設定の様です。

カラムに設定されているCOLLATEが変わるわけでは無いためか、結果としては大文字や全角文字のレコードが拾えず、検索結果は変わりませんでした。

[hayato7]

なるほど、そこの設定だと保存時に設定するということだったんですね。

調べてみましたが、sqlで書くしか見つからず、お力になれずすみません。