MySQL/MariaDB で完全な Unicode をサポートする方法

現在のコードを表示
MySQL/Mariadb 設定ファイルを変更し、utf8 エンコーディングを utf8mb4 に変更します。
MySQL/MariaDBを再起動する
文字セットと照合順序を再度確認してください
character_set_filesystem と character_set_system の説明
文字セット設定に関するその他の参考資料

既存のデータベースの文字セットを utf8mb4 に切り替える完全なプロセス

参照する

MySQL では utf8 を使用せず、常に utf8mb4 を使用してください。

utf8mb4 の紹介

MySQL/MariaDB では、utf8 文字セットは Unicode の真の実装ではありません。つまり、最大 3 バイトの文字をサポートするため、真の UTF-8 エンコーディングではありません。
可変長文字エンコーディングである UTF-8 は、最大 4 バイトをサポートします。絵文字、中国語、日本語、韓国語の繁体字などの一部の文字を完全に保存するには 4 バイトが必要です。つまり、すべての Unicode エンコーディングをサポートしたい場合、MySQL/MariaDB の utf8 だけでは不十分です。

UTF-8 エンコーディングは、U+000000 から U+10FFFF までの範囲の Unicode 文字セット内のすべての記号を表すことができます。つまり、1,114,112 個の記号が考えられます。(これらの Unicode コードポイントのすべてに文字が割り当てられているわけではありませんが、UTF-8 で文字をエンコードすることは可能です。Unicode は実際には文字を合計 1,112,064 個のコードポイントにマッピングします。) UTF-8 は可変幅エンコーディングであり、各シンボルをエンコードするために 1 ～ 4 (8 ビット) バイトを使用します。これにより、ローコード値を保存するためのスペースが節約されます。

そして、すべての文字を完全に表現することができます。

UTF-8 は、おそらく Unicode の最も人気のあるエンコード実装です。

MySQL/MariaDB に実装されている実際の UTF-8 エンコーディングは utf8mb4 であり、最大 4 バイトのストレージをサポートします。

使用できないコードポイントは合計 1,048,576 個あります。 MySQL の utf8 では、すべての可能な Unicode コードポイント ((0x00FFFF + 1) / (0x10FFFF + 1)) の 5.88% のみを保存できます。正しい UTF-8 は、すべての Unicode コードポイントを 100% エンコードできます。

UTF8 バイト数超過エラー

utf8 を使用すると、絵文字などの 4 バイト文字を保存するときに、通常、エラー Error: 1366 が報告されます。

[エラー] 1366 - 行 xx の列 'xxx' の文字列値 '\xF0\x9F\x98\x93' が正しくありません
[エラー] 1366 - 行 xx の列 'xxx' の文字列値 '\x...' が正しくありません

utf8mb4 サポート

MySQL/MariaDB の utf8mb4 は、3 バイトのサイズの文字のみを保存できるオリジナルの utf8 を補足するものであり、真の UTF-8 エンコーディングです。

MySQL 5.5.3 以降でサポートされています。

MySQL 8.0 以降では、デフォルトの文字セットが utf8mb4 に変更されました。つまり、MySQL 8.0 以降を使用する場合、バイト長の問題を心配する必要はありません。
MariaDB 10.x のデフォルトの文字セットは引き続き latin1 です。したがって、通常は utf8mb4 に変更する必要があります。

utf8mb4 に対応する一般的に使用されるソート規則は、utf8mb4_general_ci と utf8mb4_unicode_ci です。

デフォルトの文字エンコーディングを utf8mb4 に設定し、対応するソート規則を設定します。

現在のコードを表示

MySQL/MariaDB にログインした後、SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; を使用して、文字セットと照合順序を表示します。

文字セットを表示します:

MariaDB [(なし)]> 'character%' のような変数を表示します。
+--------------------------+------------------------------------------------+
| 変数名 | 値 |
+--------------------------+------------------------------------------------+
| 文字セットクライアント | gbk |
| 文字セット接続 | gbk |
| 文字セットデータベース | utf8 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | gbk |
| 文字セットサーバー | utf8 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | C:\Program Files\MariaDB 10.3n\share\charsets\ |
+--------------------------+------------------------------------------------+
セット内の 8 行 (0.043 秒)

中国語版Windowsにインストールされるため、gbkが含まれています。

MySQL/Mariadb 設定ファイルを変更し、utf8 エンコーディングを utf8mb4 に変更します。

Linux では、/etc/my.cnf または /etc/my.cnf.d/server.cnf ファイルを変更し、[mysql]、[mysqld] などの下に次の内容を追加します。
Windows では、MySQL/Mariadb インストールディレクトリの data/my.ini ファイルを変更し、[mysql]、[mysqld] などの下にコンテンツを追加します。

Linux 上の my.cnf:

> vim /etc/my.cnf
#
# このグループはクライアントとサーバーの両方で読み取られます
# すべてに影響するオプションに使用します
#
[クライアント-サーバー]

[mysqld]
文字セットサーバー = utf8mb4
init_connect = '名前をutf8mb4に設定'
照合サーバー=utf8mb4_unicode_ci
文字セットクライアントハンドシェイク=FALSE

[クライアント]
デフォルトの文字セット=utf8mb4

[mysql]
デフォルトの文字セット=utf8mb4
#
# configディレクトリからすべてのファイルをインクルードする
#
!includedir /etc/my.cnf.d

Windows の my.ini:

[mysqld]
データディレクトリ=C:/Program Files/MariaDB 10.3n/data
ポート=3306
文字セットサーバー=utf8mb4
# 一意のオプション プレフィックス 'character_set_client' を使用するとエラーが発生しやすくなり、将来的に機能しなくなる可能性があります。代わりに完全な名前 'character-set-client-handshake' を使用してください。
文字セットクライアントハンドシェイク=utf8mb4  
# 文字セットクライアント=utf8mb4
照合サーバー=utf8mb4_unicode_ci
init_connect = '名前をutf8mb4に設定'
文字セットクライアントハンドシェイクをスキップ=false
文字セットファイルシステム = バイナリ
小文字テーブル名=2 

[mysqlダンプ]
ルーズ文字セットクライアント=utf8mb4

[mysql]
デフォルトの文字セット=utf8mb4

[クライアント]
ポート=3306
プラグインディレクトリ=C:/Program Files/MariaDB 10.3n/lib/plugin
デフォルトの文字セット=utf8mb4

いくつかの設定項目について説明します。

character-set-client-handshake=FALSE は、utf8mb4_general_ci ではなく utf8mb4_unicode_ci への collation_connection の結果に影響を与える可能性があります。もちろん、SET collation_connection = utf8mb4_unicode_ci または collation_connection = utf8mb4_unicode_ci を使用することもできます。
SET NAMES は、クライアント接続で使用される文字セット、つまり、SQL ステートメントをサーバーに送信するために使用される文字セットを示します。 character-set-server サーバーの文字セットを設定します。 utf8mb4 を正しく使用するには、クライアント、サーバー、接続がすべて utf8mb4 に設定されていることを確認する必要があります。
init_connect などの文字セットに関連するその他のすべてのデフォルト設定は、character-set-server から継承されるため、init-connect、character_set_client、character_set_results、character_set_connection などを個別に指定する必要はありません。そのため、上記設定のinit_connectは省略可能です。
接続のエンコーディング値により、MySQL は入力を正しくデコードし、結果をエンコードできるようになります。それ以外の場合は、内部で再エンコードされます。

utf8mb4 文字セットとソート規則の純粋かつ簡潔な設定は次の通りで、必要な設定は 5 つだけです。

[クライアント]
デフォルトの文字セット = utf8mb4

[mysql]
デフォルトの文字セット = utf8mb4

[mysqld]
文字セットクライアントハンドシェイク = FALSE
文字セットサーバー = utf8mb4
照合サーバー = utf8mb4_unicode_ci

MySQL/MariaDBを再起動する

Linux では、systemctl restart mariadb または systemctl restart mysqld を使用します。再起動（リロードではない）を使用する必要があります。

以前のバージョンの MariaDB によって開始または再起動されたサービスは、mysqld (mysqld.service) という名前です。 systemctl で mysqld を再起動します。

Windows で「サービス」を開き、サービス内で MariaDB または MySQL の対応するサービスを見つけて右クリックし、再起動します。

文字セットと照合順序を再度確認してください

再起動後に上記の変更が有効になった後、再度確認すると、以下のように utf8mb4 と utf8mb4_unicode_ci になっています。

MariaDB [(なし)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| 変数名 | 値 |
+--------------------------+--------------------+
| 文字セットクライアント | utf8mb4 |
| 文字セット接続 | utf8mb4 |
| 文字セットデータベース | utf8mb4 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | utf8mb4 |
| 文字セットサーバー | utf8mb4 |
| 文字セットシステム | utf8 |
| 照合接続 | utf8mb4_unicode_ci |
| 照合データベース | utf8mb4_unicode_ci |
| 照合サーバー | utf8mb4_unicode_ci |
+--------------------------+--------------------+
セット内の行数は 10 行 (0.008 秒)

character_set_filesystem と character_set_system の説明

上記の文字セットとソート規則から、2 つの項目が utf8mb4 ではないことがわかります。

ファイル名にマルチバイト UTF-8 エンコード文字をサポートするファイルシステムを使用している場合を除き、character_set_filesystem はバイナリにする必要があります。
character_set_system は常に utf8 であり、上書きすることはできません。

文字セット設定に関するその他の参考資料

上記の設定は、utf8mb4 を変更するための正しい設定です。以下に、参考のために、関連する設定をいくつか示します (ただし、めったに使用されません)。

[クライアント]
デフォルトの文字セット = utf8mb4

[mysql]
デフォルトの文字セット = utf8mb4

[mysqld]
explicit_defaults_for_timestamp = 1 # タイムスタンプ メッセージを無効にするヒントとしてここに投稿します。誰かの役に立つかもしれません :)
文字セットクライアントハンドシェイク = 0 # FALSE
init_connect = '文字セットシステムをutf8mb4に設定'
init_connect = '文字セット接続を utf8mb4 に設定'
init_connect = '文字セットデータベースをutf8mb4に設定'
init_connect = '文字セット結果 = utf8mb4 を設定します'
init_connect = '照合データベースを utf8mb4_unicode_ci に設定'
init_connect = '照合接続を utf8mb4_unicode_ci に設定'
init_connect = '名前をutf8mb4に設定'
文字セットサーバー = utf8mb4
#character_set_client = utf8mb4
照合サーバー = utf8mb4_unicode_ci
照合接続 = utf8mb4_unicode_ci
照合データベース = utf8mb4_unicode_ci

複数の init_connect を次のように設定することもできます: init_connect = 'SET collation_connection = utf8mb4_unicode_ci,NAMES utf8mb4'。未確認

既存のデータベースの文字セットを utf8mb4 に切り替える完全なプロセス

データベースをutf8mb4で作成すれば、このようなトラブルは発生しません！！！

手順 1: バックアップを作成するアップグレードするサーバー上のすべてのデータベースのバックアップを作成します。安全第一！

ステップ 2: MySQL サーバーをアップグレードする MySQL サーバーを v5.5.3 以降にアップグレードします。 MySQL を 8.0 にアップグレードする場合、手順 5 で MySQL サーバーの文字セットを変更する必要はありません。デフォルトは utf8mb4 です。 MariaDB でも文字セットを変更する必要があります。

手順 3: データベース、テーブル、および列を変更する utf8mb4 を使用するように、データベース、テーブル、および列の文字セットと照合プロパティを変更します。

# データベースごとに:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 各テーブルについて:
ALTER TABLE table_name を CHARACTER SET utf8mb4 に変換し、 COLLATE utf8mb4_unicode_ci を実行します。
# 各列について:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (盲目的にコピーして貼り付けないでください。正確なステートメントは、列の種類、最大長、およびその他のプロパティによって異なります。上記の行は、`VARCHAR` 列の例にすぎません。)
# または、modify ステートメント ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ステップ4: 列とインデックスキーの最大長を確認する

これはおそらく、アップグレードプロセス全体の中で最も面倒な部分です。

utf8 から utf8mb4 に変換する場合、列またはインデックスキーの最大長 (バイト単位) は変更されません。したがって、文字の最大長が 3 バイトではなく 4 バイトになったため、文字数としては小さくなります。

たとえば、TINYTEXT 列には最大 255 バイトを保持できます。これは、3 バイト文字では 85 文字、4 バイト文字では 63 文字に相当します。 utf8 を使用する TINYTEXT 列があり、63 文字を超える文字を含めることができる必要があるとします。この要件を考慮すると、データ型を TEXT などのより長い型に変更しない限り、この列を utf8mb4 に変換することはできません。これは、4 バイト文字で入力しようとすると、63 文字しか入力できず、それ以上は入力できないためです。
インデックスキーについても同様です。 InnoDB ストレージエンジンの最大インデックス長は 767 バイトなので、utf8 または utf8mb4 列の場合はそれぞれ最大 255 文字または 191 文字までインデックスを作成できます。現在、191 文字を超える utf8 列のインデックスがある場合は、utf8mb4 を使用するときに、より少ない文字数のインデックスを作成する必要があります。 (その結果、インデックス付きのVARCHAR(255)列の一部をVARCHAR(191)に変更する必要がありました。)

ステップ5: 接続、クライアント、サーバーの文字セットを変更する

アプリケーションコードで、接続文字セットを utf8mb4 に設定します。これは、SET NAMES utf8 を SET NAMES utf8mb4 に置き換えるだけで実行できます。同時に、照合ルールもそれに応じて変更する必要があります。たとえば、SET NAMES utf8 COLLATE utf8_unicode_ci は SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci になります。

必ずクライアントとサーバーの両方の文字セットを設定してください。

MySQL 構成ファイル (/etc/my.cnf) には次の内容が含まれています。

[クライアント]
デフォルトの文字セット = utf8mb4

[mysql]
デフォルトの文字セット = utf8mb4

[mysqld]
文字セットクライアントハンドシェイク = FALSE
文字セットサーバー = utf8mb4
照合サーバー = utf8mb4_unicode_ci

ステップ6: すべてのテーブルを修復して最適化する
MySQL サーバーをアップグレードし、上記の必要な変更を行った後、すべてのデータベースとテーブルを修復して最適化してください。そうしないと、エラーがスローされない場合でも奇妙なエラーが発生する可能性があります。
修復および最適化するテーブルごとに、次の MySQL クエリを実行できます。

# 各テーブルについて
REPAIR TABLE テーブル名;
テーブル table_name を最適化します。

これは、コマンドラインの mysqlcheck ユーティリティを使用して、一度に簡単に実行できます。

$ mysqlcheck -u root -p --自動修復 --最適化 --すべてのデータベース

これにより、ルートユーザーのパスワードの入力が求められ、その後、すべてのデータベース内のすべてのテーブルが修復され、最適化されます。

参照する

主な参考資料は、「MySQL データベースで完全な Unicode をサポートする方法」から翻訳されています。元のテキストをお読みください。

MySQL/MariaDB で完全な Unicode をサポートする方法については、これで終わりです。MySQL/MariaDB の Unicode サポートの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: