MYSQL の COLLATE とは何ですか?

序文

MySQL で show create table <tablename> コマンドを実行して、テーブルのテーブル作成ステートメントを表示します。例は次のとおりです。

テーブル `table1` を作成します (
    `id` bigint(20) 符号なし NOT NULL AUTO_INCREMENT,
    `field1` テキスト COLLATE utf8_unicode_ci NOT NULL COMMENT 'フィールド 1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'フィールド2',
    主キー (`id`)
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8_unicode_ci;

ほとんどのフィールドは理解できますが、今日説明するのは COLLATE キーワードです。この値に対応する utf8_unicode_ci はどういう意味ですか?面接中にこの質問を使用して DBA をテストすると、ほとんどの人が困惑するでしょう。

COLLATE は何に使用されますか?

phpmyadmin を使用した開発は、中国語のヘッダーに答えが示されているため、非常に馴染みがあるように見えるかもしれません。

いわゆる utf8_unicode_ci は、実際にはソートに使用されるルールです。 MySQL の文字型列 (VARCHAR、CHAR、TEXT 型列など) の場合、列の並べ替え方法と比較方法を MySQL に指示するには COLLATE 型が必要です。つまり、 COLLATE は ORDER BY ステートメントの順序に影響し、WHERE 条件の大なり記号または小なり記号によってフィルタリングされた結果に影響し、DISTINCT、GROUP BY、および HAVING ステートメントのクエリ結果に影響します。また、MySQL がインデックスを作成するときに、インデックス列が文字型の場合、インデックス作成にも影響しますが、この影響は認識できません。つまり、文字タイプの比較やソートが関係する場合は常に、 COLLATE に関連します。

さまざまなCOLLATEの違い

COLLATE は通常、データエンコーディング (CHARSET) に関連しています。一般的に、各 CHARSET は複数の COLLATE をサポートしており、各 CHARSET はデフォルト値として COLLATE を指定します。たとえば、Latin1 エンコーディングのデフォルトの COLLATE は latin1_swedish_ci、GBK エンコーディングのデフォルトの COLLATE は gbk_chinese_ci、utf8mb4 エンコーディングのデフォルトの COLLATE は utf8mb4_general_ci です。

ちなみに、MySQL には utf8 と utf8mb4 の 2 つのエンコーディングがあります。MySQL では、**utf8** を忘れて、常に **utf8mb4** を使用してください。これは MySQL のレガシー問題です。MySQL の utf8 は、最大長 3 バイトの文字エンコーディングしかサポートできません。4 バイトを占める必要があるテキストの場合、MySQL の utf8 はそれをサポートしていないため、utf8mb4 を使用する必要があります。

多くの COLLATE には _ci という単語があります。これは Case Insensitive の略語で、並べ替えや比較の際に「A」と「a」が同等に扱われることを意味します。 field1="a" の table1 からの選択 * では、field1 の値が "A" であるものも選択できます。同時に、_cs サフィックスを持つ COLLATE では、大文字と小文字が区別されます。

mysql でサポートされているすべての COLLATE を表示するには、mysql の show collation コマンドを使用します。 utf8mb4 を例にとると、このエンコーディングでサポートされているすべての COLLATE が下の図に示されています。

imgmysql の utf8mb4 に関連するすべての COLLATE

写真では、多くの国の言語の並べ替えルールがわかります。国内で最も一般的に使用されている 3 つは、utf8mb4_general_ci (デフォルト)、utf8mb4_unicode_ci、utf8mb4_bin です。これら 3 つの違いを見てみましょう。

まず、utf8mb4_bin の比較方法は、実際にはすべての文字をバイナリ文字列として直接扱い、最上位ビットから最下位ビットまで比較することです。したがって、明らかに大文字と小文字は区別されます。

実際には、中国語と英語の utf8mb4_unicode_ci と utf8mb4_general_ci の間に違いはありません。当社が国内向けに開発するシステムであれば、いずれか 1 つで十分です。ただ、一部の西洋諸国の文字については、utf8mb4_general_ci よりも utf8mb4_unicode_ci の方が言語習慣に合っているというだけです。General は MySQL の古い標準です。たとえば、ドイツ語の文字「ß」は、utf8mb4_unicode_ci では 2 つの文字「ss」に相当します (これはドイツの慣習に準拠しています) が、utf8mb4_general_ci では文字「s」に相当します。しかし、通常の発達においては、2 つのエンコーディング間の微妙な違いを認識することは困難です。テキストフィールドを直接並べ替えに使用することはほとんどありません。1 文字または 2 文字が誤って並べ替えられたとしても、システムに壊滅的な結果をもたらすことは本当にあるのでしょうか?インターネット上のさまざまな投稿や議論から、utf8mb4_unicode_ci の使用を推奨する人が増えていますが、デフォルト値を使用するシステムにあまり反対しておらず、大きな問題はないと考えています。結論: utf8mb4_unicode_ci を使用することをお勧めします。utf8mb4_general_ci をすでに使用しているシステムの場合は、変更に時間を費やす必要はありません。

もう一つ注目すべき点は、MySQL 8.0 以降、MySQL のデフォルトの CHARSET が Latin1 ではなく utf8mb4 (参照リンク) に変更され、デフォルトの COLLATE も utf8mb4_0900_ai_ci に変更されたことです。 utf8mb4_0900_ai_ci は基本的に Unicode のさらなる細分化であり、0900 は Unicode 照合アルゴリズムのバージョンを指し、ai はアクセントを区別しない (発音を区別しない) ことを意味します。たとえば、e、è、é、ê、ë は同じに扱われます。関連参考リンク 1、関連参考リンク 2

COLLATE 設定レベルとその優先順位

COLLATE 設定は、インスタンスレベル、ライブラリレベル、テーブルレベル、列レベル、SQL レベルで指定できます。インスタンスレベルの COLLATE 設定は、MySQL 構成ファイルまたは起動コマンドの collation_connection システム変数です。

ライブラリレベルで COLLATE を設定するステートメントは次のとおりです。

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CHARSET と COLLATE がライブラリレベルで設定されていない場合、ライブラリレベルのデフォルトの CHARSET と COLLATE はインスタンスレベルの設定を使用します。 MySQL バージョン 8.0 未満では、何も変更しない場合、デフォルトの CHARSET は Latin1 で、デフォルトの COLLATE は latin1_swedish_ci になります。 MySQL 8.0 以降では、デフォルトの CHARSET が utf8mb4 に変更され、デフォルトの COLLATE が utf8mb4_0900_ai_ci に変更されました。

テーブルレベルの COLLATE 設定は、テーブルを作成するときに関連する設定ステートメントを追加します。次に例を示します。

テーブルの作成 (

…

) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_unicode_ci;

CHARSET と COLLATE がテーブルレベルで設定されていない場合、テーブルレベルはデータベースレベルの CHARSET と COLLATE を継承します。

列レベルの設定は、CREATE TABLEで列を宣言するときに指定します。たとえば、

テーブルの作成 (

`field1` VARCHAR(64) 文字セット utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',

…

）……

CHARSET と COLATE が列レベルで設定されていない場合、列レベルはテーブルレベルの CHARSET と COLLATE を継承します。

最後に、SQL クエリを記述するときに COLLATE を明示的に宣言して、データベーステーブル列の COLLATE 設定を上書きすることもできます。これは一般的には使用されませんが、理解できます。

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;

テーブル1からフィールド1、フィールド2を選択し、フィールド1でORDER BYし、utf8mb4_unicode_ciでCOLLATEします。

すべての設定が表示された場合、優先順位は SQL ステートメント > 列レベルの設定 > テーブルレベルの設定 > ライブラリレベルの設定 > インスタンスレベルの設定となります。つまり、列に指定された COLLATE はテーブルに指定された COLLATE をオーバーライドでき、テーブルに指定された COLLATE はライブラリレベルの COLLATE をオーバーライドできます。指定しない場合は、設定は次のレベルから継承されます。つまり、列に COLLATE が指定されていない場合、列の COLLATE はテーブルに設定されているものと同じになります。

以上がMySQLのCOLLATEについての知識です。ただし、システム設計では、システムが中国語フィールドのソート結果に大きく依存しないようにする必要があります。また、MySQL クエリでクエリ条件として中国語を使用することも避ける必要があります。