Playful IT
EngineeringDesignStartupEducationEvent

MySQLの照合順序（collation）をイチから勉強する

2018.09.11

「照合順序（collation）」とは

「Railsでconfig/database.ymlに設定するutf8_general_ciとかのこと」 という理解しかしていなかった。

照合順序とは、データベース内のデータの文字同士を比較するときの比較関数のこと。 aとAを同じものとして見るか？のような概念。

照合順序とは、文字セット内の文字を比較するためのルールを集めたもの （MySQL 5.6 リファレンスマニュアル 10.1.1 一般の文字セットおよび照合順序より）

照合順序の命名規則

照合順序はutf8_general_ciのような形で表される。 意味は文字セット_言語名_比較方法となる。

照合順序名には、関連する文字セットの名前で始まる、通常は言語名を含む、ci (大文字と小文字を区別しない)、cs (大文字と小文字を区別する)、_bin (バイナリ) のいずれかで終わる、という規則が適用されます。 （MySQL 5.6 リファレンスマニュアル 10.1.1 一般の文字セットおよび照合順序より）

比較順序のciの部分は、以下のようになっている。

比較順序の表記 意味 解説
_ci 大文字小文字を区別しない Case Insensitiveの略
_cs 大文字小文字を区別する Case Sensitiveの略
_bin バイナリ比較 binaryの略

利用できる照合順序を調べる

utf8で始まる照合順序に絞って抽出。全体では200件を超えた。



$ mysql
mysql> SHOW COLLATION LIKE 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
| utf8mb4_general_ci       | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin              | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci       | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci     | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci       | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci      | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci     | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci        | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci      | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci       | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci       | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci       | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci         | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci        | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci    | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci        | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci      | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci         | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci       | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci     | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci     | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci       | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci       | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci      | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci   | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci    | utf8mb4 | 247 |         | Yes      |       8 |
+--------------------------+---------+-----+---------+----------+---------+
53 rows in set (0.00 sec)

照合順序の選び方

照合順序は文字セットと一体であり、文字セットは文字リテラルごとに決められるので、照合順序はそれに合わせる必要がある。

MySQLサーバー全体の文字セットと照合順序を統一できるなら、その決め方は利用シーンに応じて決めるべきだろう。

「曖昧な検索を行う機会が多ければ、_unicode_ciを選択する」といった選び方で良さそう。

  • 全角文字・半角文字を区別したいか？（ＡとA、１と1など）

    • したい → _general_
    • したくない → _unicode_

  • アルファベットの大文字・小文字を区別したいか？（Aとaなど）

    • したい → _cs
    • したくない → _ci

いつもutf8_general_ciにしている。

照合順序の確認方法

MySQL WorkBenchを使う。まずはVagrantで管理しているCentOS7上のMySQLサーバーに接続できるように設定する。SSH越しでデータベースに接続するため、Connection MethodはStandard TCP/IP over SSHを選択。

MySQLの照合順序（collation）をイチから勉強する

MySQLの照合順序（collation）をイチから勉強する

照合順序の変更方法

MySQLの照合順序（collation）をイチから勉強する

MySQLRDBMSインフラデータベース照合順序