重要

このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

文字セットと照合順序

このドキュメントでは、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 はデフォルトでバイナリ照合順序を使用します。これは、デフォルトで大文字と小文字を区別しない照合順序を使用する 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 は次の照合順序をサポートしています。

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_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 |  224 |         | Yes      |       1 |
+--------------------+---------+------+---------+----------+---------+
11 rows in set (0.00 sec)

警告

TiDB は、latin1 を utf8 のサブセットとして誤って扱います。これにより、latin1 エンコーディングと utf8 エンコーディングの間で異なる文字を保存すると、予期しない動作が発生する可能性があります。 utf8mb4 文字セットを強く推奨します。詳細についてはTiDB #18955参照してください。

述語にLIKE 'prefix%'などの文字列接頭辞のLIKEが含まれており、ターゲット列が非バイナリ照合順序(接尾辞が_binで終わらない) に設定されている場合、オプティマイザは現在、この述語を範囲スキャンに変換できません。代わりに、フルスキャンが実行されます。その結果、このような SQL クエリは予期しないリソースの消費につながる可能性があります。

注記：
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)

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

TiDB の`utf8`と`utf8mb4`

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

デフォルトでは、TiDB は文字セットutf8を最大 3 バイトに制限し、TiDB で作成されたデータを MySQL で安全に復元できるようにします。システム変数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)

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

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

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

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

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 は 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 '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)

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