質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Laravel

LaravelとはTaylor Otwellによって開発された、オープンソースなPHPフレームワークです。Laravelはシンプルで表現的なシンタックスを持ち合わせており、ウェブアプリケーション開発の手助けをしてくれます。

Q&A

解決済

3回答

8088閲覧

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

Eggpan

総合スコア2665

Laravel

LaravelとはTaylor Otwellによって開発された、オープンソースなPHPフレームワークです。Laravelはシンプルで表現的なシンタックスを持ち合わせており、ウェブアプリケーション開発の手助けをしてくれます。

0グッド

0クリップ

投稿2020/02/27 13:06

編集2020/02/29 20:56

前提・実現したいこと

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

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

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

PHP

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

SQL

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

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

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

PHP

1$search_word = "tes't"; 2 3$search_word = str_replace(['\', "'", '_', '%'], ['\\', "\'", '\_', '\%'], $search_word); 4User::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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

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

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

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

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

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

投稿2020/04/22 22:42

編集2020/04/22 22:53
mpyw

総合スコア5223

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Eggpan

2020/04/24 04:48

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

0

ベストアンサー

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

php

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

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

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

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

console

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

対策

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

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

php

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

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

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

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

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

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

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

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

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

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

例えば

sql

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

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

php

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

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

投稿2020/02/28 06:46

編集2020/02/28 06:51
crhg

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Eggpan

2020/02/28 09:41

回答ありがとうございます。 該当SQLの部分のみATTR_EMULATE_PREPARESを変更する対応でエラーも起きず、想定通りのレコードを抽出できました。 ストアドファンクションについてはDBにコードを埋め込みたくない、との事で見送りましたが、今まで利用したことがなかったのでこちらも調べてみたいと思います。ありがとうございました。
guest

0

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

参考
how to add collate to laravel query

投稿2020/02/27 14:26

hayato7

総合スコア1135

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Eggpan

2020/02/27 17:50

回答ありがとうございます。database.phpに設定を追加し、別コネクションを張る。というやり方みたいなのですが、このやり方ですとコネクションレベルのcollationが変更される設定の様です。 カラムに設定されているCOLLATEが変わるわけでは無いためか、結果としては大文字や全角文字のレコードが拾えず、検索結果は変わりませんでした。
hayato7

2020/02/28 03:19

なるほど、そこの設定だと保存時に設定するということだったんですね。 調べてみましたが、sqlで書くしか見つからず、お力になれずすみません。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問