かきノート

【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 を使おう

対応策1:database.php を修正(要リフレッシュ)

「database.php」の内容を修正。
その後、リフレッシュ。
(この設定を変えただけだと、以降に作成されたテーブルのみが設定変更後の対象となる)
全テーブルを対象にできる。

  'collation' => 'utf8mb4_unicode_ci','collation' => 'utf8mb4_bin',

「php artisan migrate:fresh」等のコマンドが必要になるので、データの退避が必要。

対応策2:MySQL の ALTER TABLE コマンドを使用

ALTER TABLE コマンドを使用する。
全てを一括で変更する事は出来ないので、テーブル個別に設定。

ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

対応策3: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・Laravel】意図されないインデックスが使用され、update時にロックがかかってしまった時の対応

December 19, 2021

【MySQL】null を許可したカラムをユニークキーに含めると、意図しない重複レコードが許可されてしまう(deleted_at)

December 11, 2021

福岡の物流エンジニアが、七転び八起きしたあと九回転び、寝っ転がったまま何かやってる事を垂れ流しているブログ

RotateLinkImg-iconRotateLinkImg-iconRotateLinkImg-iconRotateLinkImg-icon