MySQLの文字コード（characterset）と照合順序（collation）についてとその設定

[株式会社TECH LUCK](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)という会社で代表兼エンジニアをしている齊藤です。

DXプロジェクト、開発プロジェクト、Rails開発などでお困りごとがありましたら[弊社HP](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)からご相談をいただけますと幸いです。
以下のような問題に対応することが可能です。
- プロジェクトでRailsエンジニアが足りなくて困っている
- Railsのバージョンアップをしたいがノウハウ・リソースが足りなくて困っている
- オフショア開発をしているが、要件の齟齬やコード品質が悪いので改善したい

また、Railsエンジニアも募集しておりますので、興味がありましたら[弊社HP](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)からご連絡いただけますと幸いです。

# 前提
開発している際に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`文字コードと教えています。

```sql:例
SELECT _latin1'string';
```

### ※デフォルトデータベースとは
デフォルトデータベースとはuse文を使って選択したデータベースのことです。

```sql:例
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の設定を確認するクエリ

```sql
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）の確認

```sql
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）の確認

```sql
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`

おすすめの設定は以下です。

```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の接続の際に以下のように記述することで変更することができる。

```bash:
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サーバー（接続先）に接続するときに、クライアント（接続元）で設定された文字セットが反映されるためです。

[参照元の記事](https://aws.amazon.com/jp/blogs/news/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout/)

そのため、`character_set_client`、`character_set_connection`、`character_set_results`をutf8mb4を設定するには、クライアント側（接続元）のmy.cnfの[client]の項目で変更をするか、MySQLサーバー（接続先）に接続後に以下のクエリで変更することになります。

```sql
set character_set_client = utf8mb4;
set character_set_connection = utf8mb4;
set character_set_results = utf8mb4;
```

また、collation_databaseに関しては、変更不可の項目になっていました。おそらく、これは既存で作られたデータベースに関しては変更に関しては`ALTER TABLE`のクエリで変更しなければならないと思います。


## 既存のデータベース、テーブル、カラムのcollationの変更方法

### 既存データベースのcharasetとcollationの変更
```sql
ALTER DATABASE データベース名 CHARACTER SET utf8mb4 collate utf8mb4_general_ci;
```

### 既存のデータベースの中にあるテーブルのcharacter_setとcollationの変更

```sql
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 = 'データベース名';
```

上記のコマンドで出てきた結果がクエリになっており、出力された結果をコピーしてクエリを実行します。

```sql
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```

設定がなされているかどうか確認するクエリは以下です。

```sql
SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';
```




# 参考記事

## MySQLの文字コード（characterset）関連
https://qiita.com/TanakanoAnchan/items/dc03ac7402c7075e1cad

https://qiita.com/yoshi-taka/items/ceb5d14684b93715f1a6

https://www.slideshare.net/tmtm/mysql-62004569

https://penpen-dev.com/blog/character_set_database

https://qiita.com/decoch/items/bfa125ae45c16811536a

https://qiita.com/kazu56/items/6af85ffcf8d3954455ad

https://hara-chan.com/it/programming/mysql-character-set-collation

https://qiita.com/k8uwall/items/79dbe6dd03aa8b0ed119

https://astail.net/?p=1040](https://astail.net/?p=1040


## MySQLの照合順序（Collation）関連
https://www.wakuwakubank.com/posts/797-mysql-collation

https://qiita.com/devzooiiooz/items/d8e774d0c8be718e89a1

https://yoneyore.hatenablog.com/entry/2020/11/26/232128


## AWSのMySQLの設定関連
https://zenn.dev/delta/articles/60fb7a6a8272f4

https://www.playfulit.net/articles/2018/09/29/19qyUiUoPAa8vCVmx7WngDZS

https://www.karakaram.com/utf8mb4-in-aurora-mysql57

https://aws.amazon.com/jp/blogs/news/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout

https://www.karakaram.com/utf8mb4-in-aurora-mysql57


### my.cnf関連
https://www.playfulit.net/articles/2018/09/13/UZwV7yw2pqhYCpiXdwZdWKEo

https://oki2a24.com/2018/10/21/how-to-set-mysql-8-cnf-to-utf8mb4

https://qiita.com/is0me/items/12629e3602ebb27c26a4

https://qiita.com/YusukeHigaki/items/2cab311d2a559a543e3a