MYSQL における COLLATE の役割とさまざまな COLLATE の違いの詳細な説明

MYSQL の COLLATE とは何ですか?

mysql で show create table コマンドを実行すると、テーブルのテーブル作成ステートメントが表示されます。例は次のとおりです。ほとんどのフィールドは理解できますが、今日説明したいのは COLLATE キーワードについてです。この値の後ろにある対応する utf8_unicode_ci は何を意味しますか? DBA 試験を受ける際の面接でこの質問を使用すると、ほとんどの人がつまづいてしまうはずです。

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

phpmyadmin を使用した開発は、中国語のヘッダーですでに答えが示されているため、非常によく知られているように見えるかもしれません:

phpmyadmin スクリーンショット

いわゆる utf8_unicode_ci は、実際には並べ替えに使用されるルールです。 VARCHAR、CHAR、TEXT タイプのカラムなど、mysql の文字タイプのカラムの場合、カラムのソートと比較の方法を 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 エンコードのデフォルト値は utf8mb4_general_ci です。

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

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

mysql で showcollat​​ion コマンドを使用して、mysql でサポートされているすべての COLLATE を表示します。 utf8mb4 を例に挙げると、このエンコーディングでサポートされるすべての COLLATE は次の図に示すとおりです。

mysql の utf8mb4 に関連するすべての COLLATEs

図には、多くの国の言語の照合規則が示されています。中国で一般的に使用される 3 つは、utf8mb4_general_ci (デフォルト)、utf8mb4_unicode_ci、および utf8mb4_bin です。これら 3 つの違いを見てみましょう。

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

実際には、中国語と英語の utf8mb4_unicode_ci と utf8mb4_general_ci の間に違いはありません。弊社が国内向けに開発したシステムですので、どれでもお選びいただけます。一部の西欧諸国の文字では、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 を使用しているシステムの場合、時間をかけて変更する必要はありません。

もう 1 つ注意すべき点は、mysql 8.0 以降、mysql のデフォルトの CHARSET が Latin1 ではなく utf8mb4 (参照リンク) に変更され、デフォルトの COLLATE も utf8mb4_0900_ai_ci に変更されたことです。 utf8mb4_0900_ai_ci は通常、unicode をさらに細分したものです。0900 は Unicode 比較アルゴリズム (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使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

以上がMYSQL における COLLATE の役割とさまざまな COLLATE の違いの詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。