提供しているサービスで絵文字を利用できるようにした際の記録です。
「MySQL 絵文字」のようなクエリで検索をかけると、色々な記事にヒットします。
文字コードをutf8mb4にすれば良い、というのはすぐに分かるのですが
- どうしてこの設定を変更するのか？
の解説記事が少なかったので、調べた内容をまとめておきます。
前提条件&環境
- AWSのRDSでデータベースを管理している
- MySQL 5.6.x
- 変更前の文字コードはUTF-8
RDSで文字コードを確認する方法
RDSのコンソールでcharacter_ で検索かけると確認できます。
6つの項目がヒットすると思います。（画像は一部抜粋）
それぞれ「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パーサーに、後続する文字列リテラルが●●文字セットですよ、と教えています。
例：
SELECT _latin1'string';
character_set_database
MySQLのドキュメントから引用します。
デフォルトデータベースで使用される文字セット。
デフォルトのデータベースが変更されるたびに、サーバーはこの変数を設定します。
デフォルトデータベースが存在しない場合、変数は character_set_server と同じ値になります。
デフォルトデータベースとは？
use文を使って選択したデータベースのことです。
# これで指定したデータベース
use database_name
character_set_filesystem
SQLのLOAD_FILE()関数などで、ファイル名を参照するときに、どの文字コードで読み取るかのオプションです。
（あくまで、ファイル名の解釈であり中身ではない）
私の環境では、この値は空でした。
デフォルト値はbinaryです。
Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター
character_set_results
SQLの実行結果（クエリ）をクライアントに返すときに利用する文字コード
character_set_server
MySQLサーバーのデフォルトの文字コード
collation_server
collationは照合する、という意味です。
SQLのWHERE等で、文字列比較を行う際に設定します。
詳しいことは参考文献を読んでいただくと良いのですが、このパラメータの設定次第では、
寿司の絵文字とビールの絵文字が同一になってしまう問題が発生してしまいます。
（🍣 = 🍺：寿司ビール問題というそうです。天才的なネーミングですね。）
設定例
最後にクラウドフォーメーションの設定例です。
ポイントは下記の通り
- しれっとMySQLのversionを上げている
- パラメータグループ名が衝突して更新できないので、名前を変えている
- MySQL関連の文字コードを変えている
- 照合順序も変えている
- 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
-
Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター
-
寿司とビールについて話し合いをしてきました
- https://labs.gree.jp/blog/2017/04/16406/
- 絵文字の「🍣 = 🍺」の問題を説明してくれています。
- collationの例がわかりやすかったです