MySQL での文字セットとソートルールの理解

MySQL を使用する際に、データをクエリするときにテキストが文字化けする、または絵文字表現を書くときにエラーが発生するなど、文字セットとソートルールに関連する問題に遭遇することがよくあります。これらの問題を理解して解決するには、MySQL の文字セットとソートルールを理解する必要があります。

文字セット

コンピュータでは、文字はエンコードされて格納され、各文字にはエンコードがあります。たとえば、文字 A は ASCII エンコーディング方式では 65 とエンコードされます。しかし、ASCII には 128 文字しかなく、数字、大文字と小文字のアルファベット、一般的な英語の句読点しか含まれていません。中国語が含まれる場合、GK2312、GB18030、UTF8 などのより多くの文字セットを使用する必要があります。

指定された規則に従って各文字をエンコードすると、エンコードテーブルのセットが得られます。これを「文字セット」と呼ぶことができます。各文字セットには独自のエンコード規則があり、異なる文字セットで同じ文字をエンコードすると、異なる結果が得られます。データを書き込むときとデータをクエリするときに異なる文字セットを使用すると、対応する文字が正しく解析されず、文字化けが発生します。

中国語では、GB2312、GBK、GB18030、UTF8 などが一般的に使用されます。UTF8 の優れた国際化機能のため、特別な理由がない限り、UTF8 エンコーディングを使用することをお勧めします。

MySQL でのフィールドの文字セットの設定

MySQL の文字セットは、最終的にフィールドに適用されます。フィールドを作成するとき（テーブルを作成するか、テーブルを変更するとき）、フィールドの文字セットを次のように指定できます。

sql

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

上記の SQL の name フィールドは utf8mb4 文字セットと utf8mb4_general_ci ソートルールを使用しています。したがって、フィールドの文字セットは utf8mb4 です。

フィールドで文字セットを指定するだけでなく、テーブル全体のデフォルト文字セットを指定することもできます。上記の SQL の場合、テーブルのデフォルト文字セットも utf8mb4 と指定されています。この場合、新しいフィールドに文字セットが指定されていない場合、テーブルのデフォルト文字セットが使用されます。

さらに、MySQL では、データベース全体、さらには MySQL サーバ全体のデフォルトのデフォルト文字セットを指定することもできます。これらの文字セットもテーブルのデフォルト文字セットと同様であり、フィールド文字セットが指定されていない場合は、デフォルト文字セットが使用されます。要するに、フィールド文字セット > テーブルのデフォルト文字セット > データベースのデフォルト文字セット > MySQL サーバのデフォルト文字セットです。

理論的には、フィールドの文字セットを設定すると、データベースは対応する文字セットで文字を保持できます。しかし、実際には、フィールドの文字セットが設定されているにもかかわらず、期待どおりに動作しない場合がよくあり、この場合は接続文字セットの問題が関係している場合があります。

接続文字セットの設定

MySQL を使用する際に、データベースフィールドに格納されている文字セット以外に、他の文字セットの概念が現れることがあります。

character_set_client クライアントが SQL ステートメントを送信するために使用する文字セット
character_set_connection MySQL が SQL ステートメントを受信したときに変換する文字セット
character_set_results MySQL が結果セットを変換する文字セット

次の SQL 設定を個別に使用できます。

sql

SET character_set_client=utf8mb4;
SET character_set_connection=utf8mb4;
SET character_set_results=utf8mb4;

また、これらの 3 つの文字セットを設定するための別のショートカットがあります。

sql

SET NAMES utf8mb4;

上記の SQL ステートメントを実行するだけで、上記で説明した 3 つの文字セットが設定されます。

コードでの文字セットの設定

コードで MySQL を使用する場合、通常は MySQL ライブラリの設定を介して接続の文字セットを決定する必要があります。Node.js の sequelize モジュールを例にとると、dialectOptions.charset で文字セットを指定する必要があります。

const sequelize = new Sequelize({
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: 'root',
    database: 'test',
    dialect: 'mysql',
    dialectOptions: {
        charset: 'utf8mb4'
    }
});

ソートルール

多くの場所で COLLATE を見ることができ、上記の例では utf8mb4_general_ci がソートルールです。

その名の通り、ソートルールは文字をどのようにソートするかを決定するために使用されます。たとえば、同じ a と b でも、1 つのソートルールでは a の前にあり、別のソートルールでは b の後ろにある場合があります。

MySQL は、utf8mb4 文字セットに対して多数のソートルールを提供しており、一般的なものは次のとおりです。

utf8mb4_general_ci：MySQL のデフォルトのソートルールで、Unicode 部分は Unicode 順に厳密にソートされていません。
utf8mb4_unicode_ci：Unicode 文字順にソート
utf8mb4_0900_ai_ci：Unicode 9.0 文字でソートされ、基本多言語面外の文字も含まれる

現在は、utf8mb4_0900_ai_ci または utf8mb4_unicode_ci を推奨しています。

MySQL での utf8 と utf8mb4

UTF8 の文字は 1～6 バイトで構成されますが、現在使用されている最大の文字は 4 バイトしかありません。MySQL の utf8 文字セットは最大 3 バイトまでしか格納できず、4 バイトの文字に遭遇すると格納できないため、utf8 文字セットのフィールドでは絵文字表現を格納できません。

utf8mb4 は、4 バイトの文字を格納できる utf8 の拡張です。したがって、絵文字表現を格納できます。

utf8mb4 文字セットを指定しない限り、utf8mb4 文字セットを使用することをお勧めします。utf8 文字セットはもはや使用しないでください。

まとめ

MySQL データベースフィールドの文字セットを設定し、接続時に同じ文字セットを使用することで、文字化けの問題が発生しないことを確認できます。
utf8mb4 文字セットが推奨され、utf8 文字セットは使用しないでください。 utf8mb4_0900_ai_ci または utf8mb4_unicode_ci ソートルールを使用することをお勧めします。

MySQL での文字セットとソートルールの理解 ​

文字セット ​

MySQL でのフィールドの文字セットの設定 ​

接続文字セットの設定 ​

コードでの文字セットの設定 ​

ソートルール ​

MySQL での utf8 と utf8mb4 ​

まとめ ​