文字セットと照合順序

このドキュメントでは、TiDB でサポートされている文字セットと照合順序について説明します。

コンセプト

文字セットは、シンボルとエンコーディングのセットです。TiDB のデフォルトの文字セットは utf8mb4 で、これは MySQL 8.0 以降のデフォルトと一致します。

照合順序は、文字セット内の文字を比較するための一連の規則と、文字の並べ替え順序です。たとえば、バイナリ照合順序では、 Aとa等しいとは見なされません。

SET NAMES utf8mb4 COLLATE utf8mb4_bin;
SELECT 'A' = 'a';
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
SELECT 'A' = 'a';

SELECT 'A' = 'a';

+-----------+
| 'A' = 'a' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 0 rows affected (0.00 sec)

SELECT 'A' = 'a';

+-----------+
| 'A' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

TiDB でサポートされている文字セットと照合順序

現在、TiDB は次の文字セットをサポートしています。

SHOW CHARACTER SET;

+---------+-------------------------------------+-------------------+--------+
| Charset | Description                         | Default collation | Maxlen |
+---------+-------------------------------------+-------------------+--------+
| ascii   | US ASCII                            | ascii_bin         |      1 |
| binary  | binary                              | binary            |      1 |
| gbk     | Chinese Internal Code Specification | gbk_bin           |      2 |
| latin1  | Latin1                              | latin1_bin        |      1 |
| utf8    | UTF-8 Unicode                       | utf8_bin          |      3 |
| utf8mb4 | UTF-8 Unicode                       | utf8mb4_bin       |      4 |
+---------+-------------------------------------+-------------------+--------+
6 rows in set (0.00 sec)

TiDB は次の照合をサポートしています。

SHOW COLLATION;

+--------------------+---------+------+---------+----------+---------+
| Collation          | Charset | Id   | Default | Compiled | Sortlen |
+--------------------+---------+------+---------+----------+---------+
| ascii_bin          | ascii   |   65 | Yes     | Yes      |       1 |
| binary             | binary  |   63 | Yes     | Yes      |       1 |
| gbk_bin            | gbk     |   87 |         | Yes      |       1 |
| gbk_chinese_ci     | gbk     |   28 | Yes     | Yes      |       1 |
| latin1_bin         | latin1  |   47 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |   83 | Yes     | Yes      |       1 |
| utf8_general_ci    | utf8    |   33 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    |  192 |         | Yes      |       1 |
| utf8mb4_0900_ai_ci | utf8mb4 |  255 |         | Yes      |       1 |
| utf8mb4_0900_bin   | utf8mb4 |  309 |         | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 |  224 |         | Yes      |       1 |
+--------------------+---------+------+---------+----------+---------+
13 rows in set (0.00 sec)

注記：

TiDB のデフォルトの照合順序 (サフィックス_binのバイナリ照合順序) は、 MySQLのデフォルトの照合順序 (通常はサフィックス_general_ciまたは_ai_ciの一般的な照合順序) とは異なります。これにより、明示的な文字セットを指定しても暗黙的なデフォルトの照合順序が選択されることに依存する場合に、互換性のない動作が発生する可能性があります。

ただし、TiDB のデフォルトの照合順序は、クライアントの接続照合順序設定によっても影響を受けます。たとえば、MySQL 8.x クライアントでは、 utf8mb4文字セットの接続照合順序はデフォルトでutf8mb4_0900_ai_ciに設定されています。

• TiDB v7.4.0 より前では、クライアントが接続照合順序としてutf8mb4_0900_ai_ci使用すると、TiDB はutf8mb4_0900_ai_ci照合順序をサポートしていないため、TiDB は TiDBサーバーのデフォルトの照合順序utf8mb4_binを使用するようにフォールバックします。
• v7.4.0 以降では、クライアントが接続照合順序としてutf8mb4_0900_ai_ci使用する場合、TiDB はクライアントの構成に従って、デフォルトの照合順序としてutf8mb4_0900_ai_ci使用します。

次のステートメントを使用すると、文字セットに対応する照合順序 ( 照合のための新しいフレームワークの下) を表示できます。

SHOW COLLATION WHERE Charset = 'utf8mb4';

+--------------------+---------+------+---------+----------+---------+
| Collation          | Charset | Id   | Default | Compiled | Sortlen |
+--------------------+---------+------+---------+----------+---------+
| utf8mb4_0900_ai_ci | utf8mb4 |  255 |         | Yes      |       1 |
| utf8mb4_0900_bin   | utf8mb4 |  309 |         | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 |  224 |         | Yes      |       1 |
+--------------------+---------+------+---------+----------+---------+
5 rows in set (0.00 sec)

GBK 文字セットの TiDB サポートの詳細については、 イギリス参照してください。

TiDB のutf8とutf8mb4

MySQL では、文字セットutf8は最大 3 バイトに制限されています。これは、Basic Multilingual Plane (BMP) の文字を保存するには十分ですが、絵文字などの文字を保存するには不十分です。この場合は、代わりに文字セットutf8mb4を使用することをお勧めします。

デフォルトでは、TiDB は、TiDB で作成されたデータが MySQL で安全に復元できることを保証するために、文字セットutf8を最大 3 バイトに制限します。システム変数tidb_check_mb4_value_in_utf8の値をOFFに変更することで、これを無効にすることができます。

以下は、4 バイトの絵文字をテーブルに挿入するときのデフォルトの動作を示しています。 INSERTステートメントはutf8文字セットでは失敗しますが、 utf8mb4では成功します。

CREATE TABLE utf8_test (
     c char(1) NOT NULL
    ) CHARACTER SET utf8;

Query OK, 0 rows affected (0.09 sec)

CREATE TABLE utf8m4_test (
     c char(1) NOT NULL
    ) CHARACTER SET utf8mb4;

Query OK, 0 rows affected (0.09 sec)

INSERT INTO utf8_test VALUES ('😉');

ERROR 1366 (HY000): incorrect utf8 value f09f9889(😉) for column c

INSERT INTO utf8m4_test VALUES ('😉');

Query OK, 1 row affected (0.02 sec)

SELECT char_length(c), length(c), c FROM utf8_test;

Empty set (0.01 sec)

SELECT char_length(c), length(c), c FROM utf8m4_test;

+----------------+-----------+------+
| char_length(c) | length(c) | c    |
+----------------+-----------+------+
|              1 |         4 | 😉     |
+----------------+-----------+------+
1 row in set (0.00 sec)

異なるレイヤーでの文字セットと照合順序

文字セットと照合順序は異なるレイヤーで設定できます。

データベースの文字セットと照合順序

各データベースには文字セットと照合順序があります。次のステートメントを使用して、データベースの文字セットと照合順序を指定できます。

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ここでDATABASE SCHEMAに置き換えることができます。

データベースによって、文字セットと照合順序が異なる場合があります。現在のデータベースの文字セットと照合照合順序を確認するには、 character_set_databaseとcollation_database使用します。

CREATE SCHEMA test1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 0 rows affected (0.09 sec)

USE test1;

Database changed

SELECT @@character_set_database, @@collation_database;

+--------------------------|----------------------+
| @@character_set_database | @@collation_database |
+--------------------------|----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------|----------------------+
1 row in set (0.00 sec)

CREATE SCHEMA test2 CHARACTER SET latin1 COLLATE latin1_bin;

Query OK, 0 rows affected (0.09 sec)

USE test2;

Database changed

SELECT @@character_set_database, @@collation_database;

+--------------------------|----------------------+
| @@character_set_database | @@collation_database |
+--------------------------|----------------------+
| latin1                   | latin1_bin           |
+--------------------------|----------------------+
1 row in set (0.00 sec)

INFORMATION_SCHEMAには 2 つの値も表示されます。

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

表の文字セットと照合順序

次のステートメントを使用して、テーブルの文字セットと照合順序を指定できます。

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

例えば：

CREATE TABLE t1(a int) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 0 rows affected (0.08 sec)

テーブルの文字セットと照合順序が指定されていない場合は、データベースの文字セットと照合順序がデフォルト値として使用されます。照合順序を指定せずに文字セットのみをutf8mb4に指定した場合、照合順序はシステム変数default_collation_for_utf8mb4の値によって決定されます。

カラムの文字セットと照合順序

次のステートメントを使用して、列の文字セットと照合順序を指定できます。

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

列の文字セットと照合順序が指定されていない場合は、テーブルの文字セットと照合順序がデフォルト値として使用されます。照合順序を指定せずに文字セットのみをutf8mb4に指定した場合、照合順序はシステム変数default_collation_for_utf8mb4の値によって決定されます。

文字列の文字セットと照合順序

各文字列は、文字セットと照合順序に対応しています。文字列を使用する場合、このオプションが利用できます。

[_charset_name]'string' [COLLATE collation_name]

例：

SELECT 'string';
SELECT _utf8mb4'string';
SELECT _utf8mb4'string' COLLATE utf8mb4_general_ci;

ルール:

• ルール 1: CHARACTER SET charset_nameとCOLLATE collation_nameを指定すると、 charset_name文字セットとcollation_name照合順序が直接使用されます。
• ルール 2: CHARACTER SET charset_nameを指定してCOLLATE collation_name指定しない場合は、 charset_name文字セットとデフォルトの照合順序charset_nameが使用されます。
• ルール 3: CHARACTER SET charset_nameもCOLLATE collation_name指定しない場合は、システム変数character_set_connectionとcollation_connectionで指定された文字セットと照合順序が使用されます。

クライアント接続の文字セットと照合順序

• サーバーの文字セットと照合順序は、システム変数character_set_serverとcollation_serverの値です。

• デフォルト データベースの文字セットと照合順序は、システム変数character_set_databaseとcollation_databaseの値です。

character_set_connectionとcollation_connectionを使用して、各接続の文字セットと照合順序を指定できます。5 変数character_set_client 、クライアントの文字セットを設定するためのものです。

結果を返す前に、 character_set_resultsシステム変数は、結果のメタデータを含む、サーバーがクライアントにクエリ結果を返す文字セットを示します。

次のステートメントを使用して、クライアントに関連する文字セットと照合順序を設定できます。

• SET NAMES 'charset_name' [COLLATE 'collation_name']

  SET NAMES 、クライアントがサーバーに SQL ステートメントを送信するために使用する文字セットを示します。2 SET NAMES utf8mb4 、クライアントからのすべてのリクエストとサーバーからの結果に utf8mb4 が使用されることを示します。

  SET NAMES 'charset_name'ステートメントは、次のステートメントの組み合わせと同等です。

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET character_set_connection = charset_name;
  

  COLLATEはオプションです。指定しない場合は、デフォルトの照合順序charset_nameを使用してcollation_connection設定されます。

• SET CHARACTER SET 'charset_name'

  SET NAMESと同様に、 SET NAMES 'charset_name'ステートメントは次のステートメントの組み合わせと同等です。

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET charset_connection = @@charset_database;
  SET collation_connection = @@collation_database;
  

文字セットと照合順序の選択優先順位

文字列 >カラム> テーブル > データベース > サーバー

文字セットと照合順序の選択に関する一般的なルール

• ルール 1: CHARACTER SET charset_nameとCOLLATE collation_nameを指定すると、 charset_name文字セットとcollation_name照合順序が直接使用されます。
• ルール 2: CHARACTER SET charset_nameを指定し、 COLLATE collation_name指定しない場合は、 charset_name文字セットとデフォルトの照合順序charset_nameが使用されます。
• ルール 3: CHARACTER SET charset_nameもCOLLATE collation_name指定しない場合は、最適化レベルが高い文字セットと照合順序が使用されます。

文字の有効性チェック

指定された文字セットがutf8またはutf8mb4場合、TiDB は有効なutf8文字のみをサポートします。無効な文字の場合、TiDB はincorrect utf8 valueエラーを報告します。TiDB のこの文字の有効性チェックは、MySQL 8.0 と互換性がありますが、 MySQL 5.7以前のバージョンとは互換性がありません。

このエラー報告を無効にするには、 set @@tidb_skip_utf8_check=1;使用して文字チェックをスキップします。

注記：

文字チェックをスキップすると、TiDB はアプリケーションによって書き込まれた不正な UTF-8 文字を検出できず、 ANALYZE実行時にデコード エラーが発生し、その他の未知のエンコードの問題が発生する可能性があります。アプリケーションが書き込まれた文字列の有効性を保証できない場合は、文字チェックをスキップすることはお勧めしません。

照合サポートフレームワーク

照合順序の構文サポートとセマンティック サポートは、 new_collations_enabled_on_first_bootstrap構成項目によって影響を受けます。構文サポートとセマンティック サポートは異なります。前者は、TiDB が照合を解析して設定できることを示します。後者は、TiDB が文字列を比較するときに照合を正しく使用できることを示します。

v4.0 より前では、 TiDB は照合のための古いフレームワークのみを提供します。このフレームワークでは、 TiDB はほとんどの MySQL 照合順序の構文解析をサポートしますが、意味的にはすべての照合順序をバイナリ照合順序として扱います。

v4.0 以降、TiDB は照合のための新しいフレームワークをサポートしています。このフレームワークでは、TiDB はさまざまな照合順序を意味的に解析し、文字列を比較するときに照合順序に厳密に従います。

照合のための古いフレームワーク

v4.0 より前では、MySQL の照合順序のほとんどを TiDB で指定でき、これらの照合順序はデフォルトの照合順序に従って処理されます。つまり、バイト順序によって文字順序が決まります。MySQL とは異なり、TiDB は文字の末尾のスペースを処理しないため、次のような動作の違いが生じます。

CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);

Query OK, 0 rows affected

INSERT INTO t VALUES ('A');

Query OK, 1 row affected

INSERT INTO t VALUES ('a');

Query OK, 1 row affected

TiDB では、上記のステートメントは正常に実行されます。MySQL では、 utf8mb4_general_ci大文字と小文字を区別しないため、 Duplicate entry 'a'エラーが報告されます。

INSERT INTO t1 VALUES ('a ');

Query OK, 1 row affected

TiDB では、上記のステートメントは正常に実行されます。MySQL では、スペースが埋め込まれた後に比較が行われるため、 Duplicate entry 'a 'エラーが返されます。

照合のための新しいフレームワーク

TiDB v4.0 以降では、照合のための完全なフレームワークが導入されています。

この新しいフレームワークは、照合順序の意味解析をサポートし、クラスターが最初に初期化されるときに新しいフレームワークを有効にするかどうかを決定するnew_collations_enabled_on_first_bootstrap構成項目を導入します。新しいフレームワークを有効にするには、 new_collations_enabled_on_first_bootstrapをtrueに設定します。詳細については、 new_collations_enabled_on_first_bootstrapを参照してください。

すでに初期化されている TiDB クラスターの場合、 mysql.tidbテーブルのnew_collation_enabled変数を通じて新しい照合順序が有効になっているかどうかを確認できます。

注記：

mysql.tidbテーブルのクエリ結果がnew_collations_enabled_on_first_bootstrapの値と異なる場合、 mysql.tidbテーブルの結果が実際の値になります。

SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';

+----------------+
| VARIABLE_VALUE |
+----------------+
| True           |
+----------------+
1 row in set (0.00 sec)

この新しいフレームワークは、照合順序の意味解析をサポートします。TiDB は、クラスターが最初に初期化されるときに、デフォルトで新しいフレームワークを有効にします。

新しいフレームワークでは、TiDB は MySQL と互換性のあるutf8_general_ci 、 utf8mb4_general_ci 、 utf8_unicode_ci 、 utf8mb4_unicode_ci 、 utf8mb4_0900_bin 、 utf8mb4_0900_ai_ci 、 gbk_chinese_ci 、およびgbk_bin照合順序をサポートします。

utf8_general_ci 、 utf8mb4_general_ci 、 utf8_unicode_ci 、 utf8mb4_unicode_ci 、 utf8mb4_0900_ai_ci 、 gbk_chinese_ciのいずれかを使用すると、文字列の比較では大文字と小文字が区別されず、アクセントも区別されません。同時に、TiDB は照合順序PADDINGの動作も修正します。

CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);

Query OK, 0 rows affected (0.00 sec)

INSERT INTO t VALUES ('A');

Query OK, 1 row affected (0.00 sec)

INSERT INTO t VALUES ('a');

ERROR 1062 (23000): Duplicate entry 'a' for key 't.PRIMARY' -- TiDB is compatible with the case-insensitive collation of MySQL.

INSERT INTO t VALUES ('a ');

ERROR 1062 (23000): Duplicate entry 'a ' for key 't.PRIMARY' -- TiDB modifies the `PADDING` behavior to be compatible with MySQL.

注記：

TiDB のパディングの実装は、MySQL とは異なります。MySQL では、パディングはスペースを埋めることによって実装されます。TiDB では、パディングは末尾のスペースを切り取ることによって実装されます。ほとんどの場合、2 つのアプローチは同じです。唯一の例外は、文字列の末尾にスペース (0x20) 未満の文字が含まれている場合です。たとえば、TiDB での'a' < 'a\t'の結果は1ですが、MySQL では'a' < 'a\t'は'a ' < 'a\t'に相当し、結果は0になります。

式内の照合順序の強制値

式に異なる照合順序の複数の句が含まれる場合は、計算で使用される照合順序を推測する必要があります。ルールは次のとおりです。

• 明示的なCOLLATE節の強制可能性値は0です。
• 2 つの文字列の照合順序に互換性がない場合、異なる照合順序を持つ 2 つの文字列の連結の強制可能性値は1なります。
• 列の照合順序CAST() 、 CONVERT() 、またはBINARY()の強制値は2です。
• システム定数 ( USER ()またはVERSION ()によって返される文字列) の強制値は3です。
• 定数の強制値は4です。
• 数値または中間変数の強制値は5です。
• NULLまたはNULLから派生した式の強制値は6です。

照合を推論する場合、TiDB は、より低い強制値を持つ式の照合順序を優先的に使用します。2 つの句の強制値が同じ場合、照合順序は次の優先順位に従って決定されます。

バイナリ > utf8mb4_bin > (utf8mb4_general_ci = utf8mb4_unicode_ci) > utf8_bin > (utf8_general_ci = utf8_unicode_ci) > latin1_bin > ascii_bin

次の状況では、TiDB は照合順序を推測できず、エラーを報告します。

• 2 つの句の照合順序が異なり、両方の句の強制可能性値が0場合。
• 2 つの句の照合順序に互換性がなく、返される式の型がStringの場合。

COLLATE句

TiDB は、式の照合順序を指定するためにCOLLATE句の使用をサポートしています。この式の強制値は0で、これが最も優先度が高いです。次の例を参照してください。

SELECT 'a' = _utf8mb4 'A' collate utf8mb4_general_ci;

+-----------------------------------------------+
| 'a' = _utf8mb4 'A' collate utf8mb4_general_ci |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

詳細については接続文字セットと照合順序参照してください。