More than 5 years have passed since last update.

[MySQL] LaravelでのDBクエリ結果の並び順と、適当なDBクライアントアプリでのDBクエリ結果の並び順が、異なっていて悩んだ後なんとかした話

Last updated at 2018-04-04Posted at 2018-04-04

はじめに

LaravelのクエリビルダーでDBアクセス楽チンライフを享受する日々。

あるとき、単にDBからテーブルの一覧情報を取得して表示する画面にて、
並び順が想定通りでないことがありました。
（「ひらがな、カタカナ、漢字...」が含まれるフィールドの５０音順表示）

常にログに出力するようにしていた「実行SQLのログ」をコピーして、
適当なDBクライアントで「まったく同じSQL」を貼り付けて実行してみたところ、
こちらは想定通りの並び順となっている結果が得られました。

SQL適当サンプル

SELECT * FROM some_table order by CAST(hoge as CHAR) asc;

結果の並び順例

なぜ異なるです！？

MySQLでは、OrderBy句の並び替えなどで利用する際の Collation(照合順序) という概念が存在する。
- 文字コードと同じレベルで設定される。
- テーブル毎の設定もある。
- カラム毎の設定もある。
  - 基本的に、SQL実行時はOrderBy対象カラムに設定されているCollationが使われる。

↓

Laravelは、config/database.php に collation の設定があり、Laravelからマイグレーションを実行した場合に、テーブルやカラムのデフォルトCollationとして設定される。（多分）
テーブルにもカラムにもCollationの設定がない（≒OrderBy対象が計算結果だったり、CASTだったり）場合、それぞれのSQL実行環境上のデフォルト値が使われる？
- Laravelでは、config/database.php の collationの設定がデフォルトになる。（なってた）
- DBクライアントアプリなどは、何も指定しなければ基本的にMySQLに設定されているデフォルトのCOLLATE(collation_connection,collation_serverなど)が利用される？（多分）

↓

よって、同じSQLを実行しても、暗黙的に設定されている設定内容によって結果が変わってしまっていた。
- 今回でいうと、Laravelでは COLLATE=utf8mb4_unicode_ci で、
  DBクライアントアプリでは COLLATE=utf8mb4_general_ci になっていた。

COLLATEの設定をどうするかは、utf8mb4 では以下のどれかを使うことになるかと思いますが、
「どうしたいか」は要件によりけりだと思いますので、それぞれの特性を知って設定してください。

案1) Laravel上の config/database.phpのcollationを変更する。
- Laravel上のシステム全体に影響するので慎重に。
- 既存のテーブルやカラム全体をなんとかしたい場合は、再マイグレーションが必要。
- ここを変更したからといって、既存のSQLが全部その設定になるわけではない。
  - 既にカラムにCollationの設定がされている場合は、カラムの設定が優先される。

案2) MySQLのデフォルトcollation設定を、なんらか統一を図るように検討して調整する。
- データベースを見てるところ全体に影響するので慎重に。
- テーブルやカラムは、Laravelのマイグレーション設定と異なると混乱するので慎重に。

今回は手っ取り早く一部で対応することにしました。
Laravelでは、以下の感じにすると、個別のSQLでorderBy句を調整できます。

SomeTable::where(〜〜)
//  ->orderBy('hoge', 'asc')
    ->orderByRaw('CAST(hoge as CHAR) COLLATE utf8mb4_general_ci asc')
    ->get();

おわり