文字セットと照合順序

このドキュメントでは、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';

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

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

TiDB はデフォルトでバイナリ照合順序を使用します。これは、デフォルトで大文字と小文字を区別しない照合順序を使用する MySQL とは異なります。

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 は次の照合順序をサポートしています。

mysql> show collation;
+-------------+---------+------+---------+----------+---------+
| Collation   | Charset | Id   | Default | Compiled | Sortlen |
+-------------+---------+------+---------+----------+---------+
| utf8mb4_bin | utf8mb4 |   46 | Yes     | Yes      |       1 |
| latin1_bin  | latin1  |   47 | Yes     | Yes      |       1 |
| binary      | binary  |   63 | Yes     | Yes      |       1 |
| ascii_bin   | ascii   |   65 | Yes     | Yes      |       1 |
| utf8_bin    | utf8    |   83 | Yes     | Yes      |       1 |
| gbk_bin     | gbk     |   87 | Yes     | Yes      |       1 |
+-------------+---------+------+---------+----------+---------+
6 rows in set (0.00 sec)

ノート：

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

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

SHOW COLLATION WHERE Charset = 'utf8mb4';

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

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

utf8mb4のutf8と utf8mb4

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

デフォルトでは、TiDB はutf8に同じ 3 バイト制限を提供し、TiDB で作成されたデータを MySQL で安全に復元できるようにします。これは、TiDB 構成ファイルの値をcheck-mb4-value-in-utf8からFALSEに変更することで無効にすることができます。

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

mysql> CREATE TABLE utf8_test (
    ->  c char(1) NOT NULL
    -> ) CHARACTER SET utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE utf8m4_test (
    ->  c char(1) NOT NULL
    -> ) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO utf8_test VALUES ('😉');
ERROR 1366 (HY000): incorrect utf8 value f09f9889(😉) for column c
mysql> INSERT INTO utf8m4_test VALUES ('😉');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT char_length(c), length(c), c FROM utf8_test;
Empty set (0.01 sec)

mysql> 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)

テーブルの文字セットと照合順序が指定されていない場合、データベースの文字セットと照合順序がデフォルト値として使用されます。

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

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

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]

列の文字セットと照合順序が指定されていない場合、テーブルの文字セットと照合順序がデフォルト値として使用されます。

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

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

[_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を使用して、各接続の文字セットと照合順序を指定できます。 character_set_client変数は、クライアントの文字セットを設定するためのものです。

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

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

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

  SET NAMESは、クライアントが SQL ステートメントをサーバーに送信するために使用する文字セットを示します。 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 より前では、TiDB でほとんどの MySQL 照合を指定でき、これらの照合はデフォルトの照合に従って処理されます。つまり、バイト順によって文字の順序が決定されます。 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 # In TiDB, it is successfully executed. In MySQL, because utf8mb4_general_ci is case-insensitive, the `Duplicate entry 'a'` error is reported.
INSERT INTO t1 VALUES ('a ');
Query OK, 1 row affected # In TiDB, it is successfully executed. In MySQL, because comparison is performed after the spaces are filled in, the `Duplicate entry 'a '` error is returned.

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

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

この新しいフレームワークは、照合の意味解析をサポートし、クラスターが最初に初期化されるときに新しいフレームワークを有効にするかどうかを決定するnew_collations_enabled_on_first_bootstrapの構成項目を導入します。新しいフレームワークを有効にするには、 new_collations_enabled_on_first_bootstrapをtrueに設定します。詳細については、 new_collations_enabled_on_first_bootstrapを参照してください。構成アイテムを有効にした後にクラスターを初期化すると、新しい照合順序が有効になっているかどうかをmysqlのnew_collation_enabled変数で確認できます。 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 、 gbk_chinese_ci 、およびgbk_bin照合をサポートします。

utf8_general_ci 、 utf8mb4_general_ci 、 utf8_unicode_ci 、 utf8mb4_unicode_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 'PRIMARY' # TiDB is compatible with the case-insensitive collation of MySQL.
INSERT INTO t VALUES ('a ');
ERROR 1062 (23000): Duplicate entry 'a ' for key '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)

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