MySQLの文字コード関連設定を1つ1つ説明する


提供しているサービスで絵文字を利用できるようにした際の記録です。

「MySQL 絵文字」のようなクエリで検索をかけると、色々な記事にヒットします。
文字コードを`utf8mb4`にすれば良い、というのはすぐに分かるのですが

* どうしてこの設定を変更するのか？

の解説記事が少なかったので、調べた内容をまとめておきます。

# 前提条件&環境
* AWSのRDSでデータベースを管理している
* MySQL 5.6.x
  * 変更前の文字コードはUTF-8

# RDSで文字コードを確認する方法

RDSのコンソールで```character_``` で検索かけると確認できます。
6つの項目がヒットすると思います。（画像は一部抜粋）


![Kobito.KWYxc9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37873/2c445012-92c8-e2cb-dc6e-74c96dcc1244.png "Kobito.KWYxc9.png")


それぞれ「utf8」が設定されていました。

# MySQLの文字コード関連の設定値の意味
## 概要

英文はRDSまま、翻訳は筆者訳です。


| 項目       | 意味（英）                    | 意味（日） |
|----------|--------------------------|-------------------------|
| character_set_client | The character set for statements that arrive from the client | クライアント側から受け取った命令文の文字コード |
| character_set_connection | The character set used for literals that do not have a character set introducer and for number-to-string conversion | イントロデューサー（※1）なしの文字列や数値から文字列への変換に利用される文字コード |
| character_set_database | 	The character set used by the default database. | デフォルトデータベースで使う文字コード |
| character_set_filesystem | The file system character set. | ファイルシステムで使う文字コード。csvやtsvなどを読み込むときに、ファイル名の解釈（ファイル名を参照する文字列リテラル）に使う文字コード。 |
| character_set_results | The character set used for returning query results to the client. | SQLの実行結果（クエリ）をクライアントに返すときに利用する文字コード |
| character_set_server | The server's default character set. | MySQLサーバーのデフォルトの文字コード |

また、下記も変更が必要です（正確には変更したほうがベター）

| 項目       | 意味（英）                    | 意味（日） |
|----------|--------------------------|-------------------------|
| collation_server | - | 文字列の照合順序 |

## 詳細
概要だけだとわかりにくいので、詳細も調べました。（概要のままのもあります。すいません。）

### character_set_client


クライアント（mysql）から送られてきた文字コードをどのように解釈するか、です。


### character_set_connection

イントロデューサーなしの文字列や数値から文字列への変換に利用される文字コードです。

#### イントロデューサーとは？

文字列（正確には文字列リテラル）の前に記載されている文字コードのことです。
例では、`_latin1`の部分です。
イントロデューサーが、SQLパーサーに、後続する文字列リテラルが●●文字セットですよ、と教えています。


例：

```sql
SELECT _latin1'string';
```


### character_set_database

MySQLのドキュメントから引用します。
* [charecter_set_database](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#character_set_database)


```
デフォルトデータベースで使用される文字セット。
デフォルトのデータベースが変更されるたびに、サーバーはこの変数を設定します。
デフォルトデータベースが存在しない場合、変数は character_set_server と同じ値になります。
```


#### デフォルトデータベースとは？

use文を使って選択したデータベースのことです。

```mysql
# これで指定したデータベース
use database_name
```


### character_set_filesystem

SQLの`LOAD_FILE()`関数などで、ファイル名を参照するときに、どの文字コードで読み取るかのオプションです。
（あくまで、ファイル名の解釈であり中身ではない）

私の環境では、この値は空でした。
デフォルト値は`binary`です。

[Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター](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_results

SQLの実行結果（クエリ）をクライアントに返すときに利用する文字コード


### character_set_server

MySQLサーバーのデフォルトの文字コード

### collation_server


`collation`は照合する、という意味です。
SQLのWHERE等で、文字列比較を行う際に設定します。

詳しいことは参考文献を読んでいただくと良いのですが、このパラメータの設定次第では、
寿司の絵文字とビールの絵文字が同一になってしまう問題が発生してしまいます。
（🍣 = 🍺：寿司ビール問題というそうです。天才的なネーミングですね。）

* [寿司とビールについて話し合いをしてきました](https://labs.gree.jp/blog/2017/04/16406/)

## 設定例

最後にクラウドフォーメーションの設定例です。

ポイントは下記の通り
* しれっとMySQLのversionを上げている
* パラメータグループ名が衝突して更新できないので、名前を変えている
* MySQL関連の文字コードを変えている
* 照合順序も変えている


```diff:template.yml
- RdsParameterGroup
+ RdsParameterGroup57:
    Type: "AWS::RDS::DBParameterGroup
    Properties:
-      Family: mysql5.6
+      Family: mysql5.7
      Description: "MySQLのパラメータグループの設定です。実環境では適切な名前を設定してください。"
      Parameters:
        slow_query_log: 1	
        query_cache_type: 1	 
        query_cache_size:  10485760
        query_cache_limit: 1048576	
        innodb_buffer_pool_dump_at_shutdown: 1
-       character_set_client: utf8	   
-       character_set_connection: utf8     
-       character_set_database: utf8	        
-       character_set_results: utf8
-       character_set_server: utf8	  
-       collation_server: utf8_general_ci
+       character_set_client: utf8mb4
+       character_set_connection: utf8mb4
+       character_set_database: utf8mb4
+       character_set_results: utf8mb4
+       character_set_server: utf8mb4
+       collation_server: utf8mb4_bin
        skip-character-set-client-handshake: 1
      Tags:
        - Key: Name
```

## 参考文献

* charecter_set_database
  * https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#character_set_database)

* Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター
  * 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://labs.gree.jp/blog/2017/04/16406/
  * 絵文字の「🍣 = 🍺」の問題を説明してくれています。
  * collationの例がわかりやすかったです