MySQL 8.4のCollation(照合順序)を知ろう! 〜摩訶不思議なPAD属性と末尾スペースの扱い〜

目次

はじめに

前回のブログでは、MySQL 8.4のCollation(照合順序)について、文字の比較やソート順の違いをご紹介しました。

今回は、その続編として「PAD属性と末尾スペース」の扱いについて深掘りしていきます!
* 「なぜか検索結果が想定と違う…」
* 「PADやNO_PAD属性って何?」
* 「データ型によって末尾スペースの挙動は違うの?」

そんな疑問をお持ちの方に向けて、CollationとPAD属性の関係性、そしてデータ型による末尾スペースの挙動について解説します!

PAD属性とは?

PAD属性とは、文字列比較時に末尾のスペースをどう扱うかを定めるルールです。
MySQL 8.0以降のCollationには、以下の2種類が存在します!

PAD属性 説明 比較時の挙動
PAD SPACE 末尾のスペースを無視する ‘ABC’ = ‘ABC ‘ → TRUE
NO PAD 末尾のスペースも厳密に比較する ‘ABC’ = ‘ABC ‘ → FALSE

参考:MySQL公式ドキュメント

どのCollationがどのPAD属性を持つのか?

以下のようにWHERE句でCollationの名前を指定してクエリを実行することで、
簡単にPAD属性を見分けることができます!

例えば、MySQL 8.0および8.4のCollationのデフォルトはutf8mb4_0900_ai_ciですが、
クエリの実行結果からPAD_ATTRIBUTE=NO PADであることがわかります。

データ準備

本検証は、MySQL 8.4.4で検証を行っています。
Collationはデフォルトのutf8mb4_0900_ai_ciを使用して、検証用に1つのテーブルを作成し、
CHAR型、VARCHAR型のカラムに末尾スペースの数を0~3個追加した文字データを投入します。

VARCHAR型は挿入したままの状態で保存・取得されているので、問題ありません。
一方、CHAR型は挿入時にスペースを追加してデータを保存しますが、表示時には末尾スペースが削除されています! これはどういうことなのでしょうか?

参考までに以下の表は、CHAR(4) カラムと VARCHAR(4) カラムにさまざまな文字列値を格納した結果です。
この表からもわかる通り、CHAR は値が格納されると、指定された長さになるように右側がスペースで埋められるというのが一般的です。

登録値 CHAR(4) VARCHAR(4)
'' ' ' ''
'ab' 'ab ' 'ab'
'abcd' 'abcd' 'abcd'
'abcdefgh' 'abcd' 'abcd'

それでは、いくつかの検証結果と共に説明をしていきます。

検証1:NO PAD Collationでの比較

デフォルトのutf8mb4_0900_ai_ci(NO PAD)で検証します。

VARCHAR型での比較

こちらはNO PADのため、厳密に一致したレコードのみが返されます。これは正しい挙動ですね!

CHAR型での比較

取得結果を見ると、
NO PADにも関わらず全件マッチして、表示時に末尾スペースが削除されていますね…

まずCHAR(10)'ABC' を入れると、実際には 'ABC '(末尾スペース7つ)として格納されます。
ただし、この末尾スペースはSQLモードの設定によって扱いが変わります。

そのキーフラグがこちら:PAD_CHAR_TO_FULL_LENGTH(デフォルトOFF)

SQLモード CHAR型の挙動
PAD_CHAR_TO_FULL_LENGTH = OFF(デフォルト) SELECT時に末尾スペースが削除される
PAD_CHAR_TO_FULL_LENGTH = ON 格納時・表示時ともにスペースを維持する

CHAR型の末尾スペースが取得時に自動削除されるため、全て'ABC'として扱われてしまいます。

PAD_CHAR_TO_FULL_LENGTHを有効化

では有効化した場合の動作を見てみましょう!

全て10文字になりました!
CHAR(10)で定義したカラムが、末尾スペース付きで取得されるようになります。
改めて確認してみましょう!!

このように、PAD_CHAR_TO_FULL_LENGTH有効時は、スペース込みで比較する必要があることがわかります!

🧪検証1まとめ

  • NO PAD: VARCHARは厳密比較、CHARはモードに依存
  • PAD_CHAR_TO_FULL_LENGTH=ONで文字長が固定化され、スペース込みの比較となる

検証2:PAD SPACE Collationでの比較

次に、PAD SPACE属性を持つCollationで比較してみます。

今回使用するのは、古くから存在する utf8mb4_general_ci です。
このCollationは PAD SPACE属性 を持つため、末尾スペースを無視して比較します。

PAD_CHAR_TO_FULL_LENGTHが無効

まずはデフォルト状態(PAD_CHAR_TO_FULL_LENGTH = OFF)で確認します。

結果を確認するとVARCHAR、CHAR型共に全レコードがヒットしています。

ただしCHAR型の表示結果は、
[ABC] となっており、末尾スペースが表示されていません。

これは前述したように、
PAD_CHAR_TO_FULL_LENGTH = OFF の状態では 取得時に末尾スペースが削除されるためです。

PAD_CHAR_TO_FULL_LENGTHを有効化

次に PAD_CHAR_TO_FULL_LENGTH を有効化します。

この状態では、CHAR型の値は 末尾スペースを含めた状態で取得されますが、
PAD SPACE Collationでは、比較時には依然として末尾スペースが無視されます。

そのため、

のどちらの条件でも 全レコードが一致します。

つまり、
* 表示 → スペースあり
* 比較 → スペース無視
という挙動になります。

🧪検証2まとめ

  • utf8mb4_general_ciなどPAD SPACE属性を持つCollationでは、比較時は常にスペース無視。

検証3:LIKE句とスペース

次に LIKE句を使用した場合の挙動を確認します。
普通は「= の挙動まで押さえておけば、想像つくので検証する必要ないよな」と思いますが…

PAD_CHAR_TO_FULL_LENGTHが無効

まず PAD_CHAR_TO_FULL_LENGTH = OFF の状態です。

なんと、Collationに付随しているPAD属性を無視して、
LIKE句の場合は、常にNO PADで評価されていることがわかります…

PAD_CHAR_TO_FULL_LENGTHを有効化

次にPAD_CHAR_TO_FULL_LENGTH を有効化します。

すると CHAR型は

として取得されるようになりますが、

やはりCollationに付随しているPAD属性を無視して、
LIKE句の場合は、常にNO PADで評価しているようです。

その結果、

では 一致するレコードが存在しないという結果になりました

🧪検証3まとめ

  • LIKE句はPAD SPACE比較を行わないため、末尾スペースは比較対象として扱われる。(要注意!)

検証4:IN句とスペース

IN句の挙動はどうなのか…
混乱しやすい部分なので、動作を明確にしておきたいと思います!

PAD_CHAR_TO_FULL_LENGTHが無効

まず PAD_CHAR_TO_FULL_LENGTH = OFF の状態です。

結果は以下のようになりました。これは= の挙動と同じですね!

VARCHAR型では:
* NO PAD (utf8mb4_0900_ai_ci) → 厳密一致のみ
* PAD SPACE (utf8mb4_general_ci) → 末尾スペース無視で全件ヒット

CHAR型では:
* どちらのCollationでも全件ヒット

PAD_CHAR_TO_FULL_LENGTHを有効化

次にPAD_CHAR_TO_FULL_LENGTH を有効化します。

結果を確認すると、IN句は等号(=)演算子と同じCollationルールであることがわかります。
そのためCHAR型 + PAD_CHAR_TO_FULL_LENGTH の組み合わせには注意する必要があります!!

🧪検証4まとめ

  • IN()は=と同じPADルール。
  • CHAR列+SQLモードON時は動作が直感とズレる可能性がある。

挙動まとめ表

これまでの検証結果を一覧表にまとめました!

等号(=)での比較

データ型 PAD属性 PAD_CHAR_TO_FULL_LENGTH 比較条件 結果
VARCHAR NO PAD OFF = 'ABC' 厳密一致のみ (1件)
VARCHAR NO PAD ON = 'ABC' 厳密一致のみ (1件)
VARCHAR PAD SPACE OFF = 'ABC' 末尾スペース無視 (全件)
VARCHAR PAD SPACE ON = 'ABC' 末尾スペース無視 (全件)
CHAR NO PAD OFF = 'ABC' 全件 (※1)
CHAR NO PAD ON = 'ABC' 0件 (※2)
CHAR PAD SPACE OFF = 'ABC' 全件 (※3)
CHAR PAD SPACE ON = 'ABC' 全件 (※4)

注釈:
* ※1: 取得時に末尾スペース削除 → 全て 'ABC' として比較
* ※2: 'ABC ' (10文字) vs 'ABC' (3文字) で不一致
* ※3: 取得時は末尾スペース削除、比較時も末尾スペース無視
* ※4: 取得時はスペース付き、比較時は末尾スペース無視

LIKE句での比較

データ型 PAD属性 PAD_CHAR_TO_FULL_LENGTH 比較条件 結果
VARCHAR 任意 任意 LIKE 'ABC' 厳密一致のみ (1件)
CHAR 任意 OFF LIKE 'ABC' 全件 (※5)
CHAR 任意 ON LIKE 'ABC' 0件 (※6)

注釈:
* ※5: 取得時に末尾スペース削除 → 全て 'ABC'
* ※6: 'ABC ' (10文字) vs 'ABC' (3文字) で不一致

まとめ

皆さんは「間(ま)」の重要性をご存じでしょうか?
落語や漫才では、絶妙な「間」が笑いを生み出します。 一方、余計な「間(スペース)」があると、オチが台無しになることも…

それでは、最後になぞかけです…
MySQL 8.4のPAD属性」とかけまして、
お笑い芸人の『間』の取り方」と解きます。
そのこころは…

どちらも “スペース(空白・間)” の扱いで、結果が大きく変わります。
コードの見た目は完璧…でも検索結果が1件足りない!? その犯人は、「末尾スペース」かもしれません!
本ブログが、皆さんのデバッグの「間」を縮める一助となれば幸いです!!

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次