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

7758 ワード
RDS MySQL 絵文字 AWS MySQL テキストリンク

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

「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のドキュメントから引用します。
* charecter_set_database

デフォルトデータベースで使用される文字セット。
デフォルトのデータベースが変更されるたびに、サーバーはこの変数を設定します。
デフォルトデータベースが存在しない場合、変数は 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関連の文字コードを変えている
* 照合順序も変えている

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

参考文献