文字セットと照合

このドキュメントでは、TiDBでサポートされている文字セットと照合を紹介します。

コンセプト

文字セットは、記号とエンコーディングのセットです。 TiDBのデフォルトの文字セットはutf8mb4で、MySQL8.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で作成されたデータをMySQLで安全に復元できるように、TiDBはutf8に同じ3バイトの制限を提供します。これは、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と互換性がありますが、MySQL5.7以前のバージョンとは互換性がありません。

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

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

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

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

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

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

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

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 4.0では、照合のための完全なフレームワークが導入されています。この新しいフレームワークは、セマンティック解析の照合をサポートし、クラスタが最初に初期化されるときに新しいフレームワークを有効にするかどうかを決定する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)

新しいフレームワークでは、 utf8mb4_general_ciはMySQLと互換性のあるutf8_general_ci 、およびutf8mb4_unicode_ciのgbk_binをgbk_chinese_ciしutf8_unicode_ci 。

utf8_general_ci 、およびutf8mb4_general_ciのutf8mb4_unicode_ci gbk_chinese_ciが使用されている場合、文字列の比較では大文字と小文字が区別されず、アクセントも区別されutf8_unicode_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)

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