MySQL – COLLATE~照合順序

概要

  • COLLATE文は文字の照合順序を指定する
  • 照合順序は文字の大小比較をする際の基準
  • 検索時のWHEREや並べ替えORDER BYなどで適用される

書き方

WHERE

WHERE colname COLLATE collateion = 'expression';

WHERE colname COLLATE collateion LIKE 'expression';

ORDER BY

ORDER BY colname COLLATE collateion;

Collation

  • utf8mb4_unicode_ci
  • utf8mb4_general_ci
  • utf8mb4_bin
  • utf8mb4_0900-ai_ci(デフォルト)

デフォルトの照合順序

UTF-8のデフォルトはutf8_general_ciだが、utf8mb4の場合utf8mb4_0900-ai_ciと照合順序が異なる。

確認

以下のテーブルを使う。

区別

utf8mb4_0900_ai_ci

  • 大文字と小文字は区別される
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

utf8mb4_general_ci

  • 大文字と小文字は区別されない
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

utf8mb4_unicode_ci

  • 大文字と小文字は区別されない
  • 全角と半角は区別されない
  • 平仮名と片仮名は区別されない
  • 濁点の有無は区別されない

utf8mb4_bin

  • 大文字と小文字は区別される
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

順序

utf8mb4_0900_ai_ci

utf8mb4_unicode_ciと同じ。

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

utf8mb4_general_ci

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名はユニコード文字順

utf8mb4_unicode_ci

Unicodeの文字順と異なる。

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

utf8mb4_bin

  • アルファベット・仮名ともUnicode文字順

 

2件のコメント

  1. こんにちは、とても良い感じでまとめていただいたブログを拝見したのでコメントします

    例えば utf8mb4_unicode_ci で

    * 全角と半角は区別されない
    * 濁点の有無は区別されない

    と並列で書かれておりますが

    半角濁点と全角濁点は区別することになると思います。

    SELECT text FROM testtable WHERE text COLLATE utf8mb4_unicode_ci IN(‘a’, ‘パパ’);

    と検索したら 半角の「パパ」は引っかからないと思います。

    1. コメントをいただいてありがとうございます。
      私の方の環境で今一度、お示しいただいたSELECT文を試してみました。

      utf8mb4_unicode_ciの場合:
      INの中がはは,ハハ,ババ,パパの全角,半角いずれか
      -> いずれの場合も、はは,ハハ,パパ,ババの全角と半角が全てヒット

      utf8mb4_general_ciの場合:
      INの中がはは,ハハ,ババ,パパの全角,半角いずれか
      -> INで指定したものだけヒット

      環境あるいは設定で変わってくるのでしょうか。
      おかしな点があったらご指摘ください。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です