株式会社TECH LUCKという会社で代表兼エンジニアをしている齊藤です。
開発している際にMySQLの文字コード関連でエラーが出てきたので、いい機会だと思って調べたことをまとめました。
前提条件
macOS13.2.1（M1チップ）
MySQL：8.0.32
また記事の後半で、AWSのRDSも出てきます。
文字コードと照合順序について
MySQLは文字コードとソート順の2つの設定を持っています。ソート順の部分を照合順序と呼びます。
MySQL内で文字列を比較する際に、文字コードだけでなく照合順序が一致するかどうかも比較します。そのため、照合順序が合わないと比較できず、JOINしようとした際にエラーになってしまいます。
文字コード（characterset）は、MySQL内ではutf8mb4, utf8, cp932などで表されています。
文字コードは、MySQLクライアント（接続元）の設定、MySQLサーバー（接続先）設定の2種類があるので注意が必要です。
照合順序（collation）は、MySQL内でutf8mb4_general_ci, utf8mb4_binなどで表されています。
照合順序は、データベース、テーブル、カラムごとに設定することができるので注意が必要です。
文字コード（characterset）について
文字コードとは
文字コードとはある文字をどのようなバイト列で表現するかを決めたルールを文字コードと言います（これ以上深く掘るのと長くなるので割愛）。utf8とutf8mb4などで表されるものです。
MySQLの文字コードの注意点
MySQLでのutf8は、みなさんが実際に思っているutf8とは別物なので注意が必要です。
MySQLの文字コードのutf8は1～3バイトまでの対応となっており、絵文字などが保存できません。
絵文字などを保存するには、MySQLの文字コードをutf8mb4にしなければなりません。
これからMySQLサーバーを新しく構築する際には、MySQLの文字コードはutf8mb4にしておくことが良いかと思います。
文字コードの設定項目
以下にMySQLで設定できる文字コードの一覧とその意味を記載しておきます。
| 設定項目 | 設定の影響範囲 |
|---|---|
| character_set_client | クライアント側（接続元）から受け取った命令文の文字コード |
| character_set_connection | イントロデューサー（※）がない場合に、文字列や数値から文字列への変換に利用される文字コード |
| character_set_database | デフォルトデータベース（※）で利用する文字コード。 |
| character_set_filesystem | ファイルシステムで使う文字コード。csvやtsvなどを読み込むときに、ファイル名の解釈（ファイル名を参照する文字列リテラル）に使う文字コード。 |
| character_set_results | SQLの実行結果（クエリ）をクライアント側（接続元）に返すときに利用する文字コード |
| character_set_server | MySQLサーバーがデフォルトで利用する文字コード |
※イントロデューサーとは
イントロデューサーとはSQLの文字列（正確には文字列リテラル）の前に記載する文字コードのことです。以下の例では、_latin1の部分がイントロデューサーにあたります。これによりSQLを解析する際に（SQLパーサー）に、後に続く文字列は_latin1文字コードと教えています。
SELECT _latin1'string';
※デフォルトデータベースとは
デフォルトデータベースとはuse文を使って選択したデータベースのことです。
use 'データベース名';
MySQL8.0でのおすすめの設定
色々調べた結果、以下のように設定しておくといいのではないかと思っています。
| 設定項目 | 設定 |
|---|---|
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary（※） デフォルトのままがよい。 |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb3（※） デフォルトのままがよい。 |
※character_set_filesystem
character_set_filesystemの設定を変更するとややこしいことになるとのこと。
※character_set_server
character_set_serverの変更を変更するとややこしいことになるとのこと。
character_setの設定を確認するクエリ
show variables like "chara%"
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 ※ |
| character_sets_dir | /~~~/ |
+--------------------------+---------------------------------------------------------+
※character_set_system
character_set_systemはMySQLの内部実装の文字コードで、常にutf8にしておくのがよいとのこと。
照合順序（collation）について
照合順序（collation）とは、データベース内のデータの文字同士を比較するときのルールのこと。
この設定により、「大文字のA」と「小文字のa」、「全角のア」と「半角のｱ」を区別するかどうかなどの挙動が変わってきます。
照合順序は、データベース、テーブル、カラムごとに設定することができるので注意が必要です。
照合順序の設定項目
| 設定項目 | 設定の影響範囲 |
|---|---|
| collation_connection | character_set_connectionを用いて文字コードを設定する際の照合順序。 |
| collation_database | 特定のデータベースで利用されるデフォルトの照合順序。 |
| collation_server | MySQLサーバー全体で利用されるデフォルトの照合順序。 |
データベース全体の照合順序の（collation）の確認
show variables like "col%"
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
特定のデータベース内にある全テーブルの照合順序（collation）の確認
SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';
+-------------------------------+--------------------+
| TABLE_NAME | TABLE_COLLATION |
+-------------------------------+--------------------+
| articles | utf8mb4_general_ci |
| users | utf8mb4_general_ci |
~~~
~~~
~~~
+-------------------------------+--------------------+
MacのMySQLでの設定方法
MacのMySQLでは、my.cnfというファイルを作成してMySQLの設定を行うことができます。
以下のディレクトリの場所に、my.cnfを置くことで設定が反映されます。
/etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf
おすすめの設定は以下です。
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
| 設定項目 | どのような設定か |
|---|---|
| [mysqld] character-set-server=utf8mb4 | character_set_database, character_set_serverがutf8mb4として設定される |
| [mysqld] collation-server=utf8mb4_general_ci | collation_database, collation_serverがutf8mb4_general_ciで設定される。 |
| [client] default-character-set=utf8mb4 | character_set_client, character_set_connection, character_set_resultsがutf8mb4として設定される ※ |
※default-character-set=utf8mb4に関しては、設定をしていなくてもMySQLの接続の際に以下のように記述することで変更することができる。
mysql -u user -p --default-character-set=utf8
AWSのMySQLでの設定方法
AWSのMySQLでcharactersetとcollationを変更するには、パラメーターグループで以下の項目を設定します。
| 文字コード（character_set）のパラメータ | 設定値 |
|---|---|
| character_set_client | utf8mb4 ※ |
| character_set_connection | utf8mb4 ※ |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 ※ |
| character_set_server | utf8mb4 |
| 照合順序（collation）のパラメータ | 設定値 |
|---|---|
| collation_connection | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
注意点
character_set_client、character_set_connection、character_set_results は、パラメータグループで設定は無視されます。というのも、これらの設定値はクライアント（接続元）がMySQLサーバー（接続先）に接続するときに、クライアント（接続元）で設定された文字セットが反映されるためです。
そのため、character_set_client、character_set_connection、character_set_resultsをutf8mb4を設定するには、クライアント側（接続元）のmy.cnfの[client]の項目で変更をするか、MySQLサーバー（接続先）に接続後に以下のクエリで変更することになります。
set character_set_client = utf8mb4;
set character_set_connection = utf8mb4;
set character_set_results = utf8mb4;
また、collation_databaseに関しては、変更不可の項目になっていました。おそらく、これは既存で作られたデータベースに関しては変更に関してはALTER TABLEのクエリで変更しなければならないと思います。
既存のデータベース、テーブル、カラムのcollationの変更方法
既存データベースのcharasetとcollationの変更
ALTER DATABASE データベース名 CHARACTER SET utf8mb4 collate utf8mb4_general_ci;
既存のデータベースの中にあるテーブルのcharacter_setとcollationの変更
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_sql FROM information_schema.tables WHERE table_schema = 'データベース名';
上記のコマンドで出てきた結果がクエリになっており、出力された結果をコピーしてクエリを実行します。
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
設定がなされているかどうか確認するクエリは以下です。
SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';
参考記事
MySQLの文字コード（characterset）関連
MySQLの照合順序（Collation）関連
AWSのMySQLの設定関連
my.cnf関連