かきノート
【MySQL・Laravel】検索時、濁点や大文字・小文字が識別されない問題の対応
December 18, 2021 • ☕️ 5 min read
【 環境 】
Laravel のバージョン： 8.16.1
PHP のバージョン： 7.4.7
MySQL のバージョン： 5.7
MySQL を使っていると、以下のような現象に遭遇する事がある。
SQL
select
id
,name
,name_kana
from
customers
where 1=1
and name_kana like '%たざき%'出力結果
| id | name | name_kana |
|---|---|---|
| 1 | 田崎 浩平 | たざき こうへい |
| 2 | 田崎 耕平 | たさき こうへい |
こんな感じで、「たざき」と検索すると、「たさき」も対象となってしまう。
他にも、大文字・小文字が区別されなかったりと、やたらと検索結果が広くなってしまったりする。
ちなみに、Laravel ソースと、migration 時に生成される MySQL 定義情報は、こんな感じ。
Laravel ソース
public function up()
{
Schema::create('customers', function (Blueprint $table) {
$table->id();
$table->string('name', 80);
$table->string('name_kana', 80);
$table->timestamps();
$table->softDeletes();
});
}MySQL 定義
CREATE TABLE `customers` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`name_kana` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci原因
「collation」という、文字列を比較するためのルール（COLLATE）があり、デフォルトでは「utf8_unicode_ci」が設定されるのが原因らしい。
| collation | 説明 |
|---|---|
| utf8mb4_unicode_ci | アルファベットの大文字小文字を区別せず、全角半角も混同する。ひらがな・かたかなの大文字小文字も区別しない。 |
| utf8mb4_general_ci | アルファベットの大文字小文字を区別しなくなる。それ以外は区別される。 |
| utf8mb4_bin | 完全に文字の一致を照合する。 |
参考サイト
MySQLの文字コード事情
laravel使うならmysqlのcollation指定はutf8mb4_binにしておくべき
MySQLの文字コードとCollation
MySQL の utf8mb4 の文字照合順序まとめ
結論
utf8mb4 を使おう
対応策１：database.php を修正（要リフレッシュ）
「database.php」の内容を修正。
その後、リフレッシュ。
（この設定を変えただけだと、以降に作成されたテーブルのみが設定変更後の対象となる）
全テーブルを対象にできる。
'collation' => 'utf8mb4_unicode_ci',
↓
'collation' => 'utf8mb4_bin',「php artisan migrate:fresh」等のコマンドが必要になるので、データの退避が必要。
対応策２：MySQL の ALTER TABLE コマンドを使用
ALTER TABLE コマンドを使用する。
全てを一括で変更する事は出来ないので、テーブル個別に設定。
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin対応策３：Laravel で個別に設定
テーブルごとに個別に定義する事ができるみたい。
https://readouble.com/laravel/8.x/ja/migrations.html
とはいえ、そんな場面が必要なのだろうか。
database.php でまとめてやってしまった方がいい気がする。
変更後
最終的に、テーブル定義にて「COLLATE=utf8mb4_bin」となっていることが確認できればOKです。
CREATE TABLE `customers` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(80) COLLATE utf8mb4_bin NOT NULL,
`name_kana` varchar(80) COLLATE utf8mb4_bin NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin場合によっては、varchar や text のカラムは、「utf8mb4_bin」と変更され無いケースがあり、その場合は個別のカラムに対して alter table を書ける必要があるみたいです。
ALTER TABLE customers MODIFY name_kana VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin構文
https://dev.mysql.com/doc/refman/5.6/ja/charset-column.html
ALTER TABLE t1 MODIFY
col1 VARCHAR(5)
CHARACTER SET latin1
COLLATE latin1_swedish_ci;Relative Posts:
【MySQL】null を許可したカラムをユニークキーに含めると、意図しない重複レコードが許可されてしまう（deleted_at）
December 11, 2021
福岡の物流エンジニアが、七転び八起きしたあと九回転び、寝っ転がったまま何かやってる事を垂れ流しているブログ
