MySQL5.7と8.0における文字コード/照合順序の設定方法

前置き

・パラメータ名がハイフン表記か、アンダースコア表記か常に意識しておく必要有
・初めてMySQLを起動する前に設定すべき
　後でも直せなくはないが、苦行
　また、mysql/performance/sysスキーマをalter文で変更するの怖い(一部放置している…)

前提

・MySQL5.7/8.0におけるmy.cnf(Linux)/パラメータグループ(AmazonRDS)での設定を考慮
・文字コードはutf8mb4、照合順序はutf8mb4_general_ciに設定
・データベースのことをスキーマ、MySQLサーバーのプロセス全体をサーバーと記述する

文字コード

１．文字コードとは

　・みんな大好き文字コードのため割愛
　・MySQL5.7も8.0もutf8mb4が無難
　　(cp932で保管しないといけないシステムもあるであろうが…)

２．文字コードに関するパラメータ

２．１．character-set-server

　・サーバー全体で使用する文字コードを指定
　・当パラメータを設定する事で以下のパラメータが従属する
　　－ character_set_server
　　－ character_set_database

２．１．１．character_set_server
　・サーバーの文字コード

２．１．２．character_set_database
　・デフォルトスキーマの文字コード
　・useコマンドでスキーマを変更する毎に当パラメータで設定した値に戻る
　・但し、globalパラメータとしての利用は、5.7及び8.0ではdeprecatedなパラメータ

(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_character_set_database より抜粋)

The global character_set_database and collation_database system variables are deprecated; expect them to be removed in a future version of MySQL.

２．２．default-character-set

　・クライアント(ex. JDBC、MySQL Workbench)が接続してきた際の文字コードを指定
　・当パラメータを設定する事で以下のパラメータが従属する
　　－ character_set_client
　　－ character_set_connection
　　－ character_set_results
　・後述するcharacter-set-client-handshakeというパラメータにより当設定群を無視する事も可能

２．２．１．character_set_client
　・クライアントが接続してきた際の文字コードを指定

２．２．２．character_set_connection
　・MySQLがSQLを実行する際にリテラル値の解釈を行う際の文字コード

２．２．３．character_set_results
　・クライアントに結果を返却する際の文字コードを指定

２．３．character_set_filesystem

　・MySQLがOS上のファイルにアクセスする際に用いる文字コード
　・binary(無変換)がデフォルトで変更しない方が無難
　・よって、MySQL関連のファイルにマルチバイト文字は扱わない方が無難

２．４．character_set_system

　・MySQLの内部実装の文字コード
　・触るな危険(utf8mb4を設定する箇所ではない)
　　なお、MySQL5.7ではutf8、MySQL8.0ではutf8mb3が設定されている

２．５．character_sets_dir

　・MySQLの文字セットの管理場所
　・触るな危険(ディレクトリパスを指定する箇所であり、utf8mb4を設定する箇所ではない)

３．設定方法

３．１．my.cnfの場合

　MySQL5.7の場合：character-set-serverとdefault-character-setに設定する
　(∵MySQL5.7のデフォルト文字コードはutf8のため)
　MySQL8.0の場合：デフォルトのままでOK
　(∵MySQL8.0のデフォルト文字コードはutf8mb4のため)

３．２．パラメータグループの場合

　MySQL5.7の場合：２．１．ｘ～２．２．ｘのパラメータを設定する
　(∵MySQL5.7のデフォルト文字コードはutf8で、character-set-serverの様なハイフン指定パラメータが存在しないため)
　MySQL8.0の場合：デフォルトパラメータグループのままでOK
　(∵MySQL8.0のデフォルト文字コードはutf8mb4のため)

４．character-set-client-handshakeとskip-character-set-client-handshake

４－１．効能

　クライアントが文字コードをこれでエンコしてねとお願いしてきても跳ね除ける。
　そして、サーバー側に設定しているcharacter_set_client, character_set_connection, character_set_clientを用いてエンコードする。
　なお、当パラメータを設定する事で、character_set_clientなどの値が変更する事は無いと思われる。(未実験)

４－２．character-set-client-handshakeとskip-character-set-client-handshake

　MySQLのマニュアルには、一覧としては両方が記載されている。
　MySQL :: MySQL 8.0 Reference Manual :: 5.1.4 Server Option, System Variable, and Status Variable Reference
　しかし、リンク先に遷移するとどちらも--character-set-client-handshakeの説明に飛ばされる。
　マニュアルには、--character-set-client-handshakeはboolean値との記載はある。
　一方、--skip-character-set-client-handshakeは記載がない。
　(my.cnfとかで定義する場合も当パラメータを定義するだけで値の設定は必要ない)
　しかし、RDSのパラメータグループは、どちらもboolean値なので排他的に設定しないと思わぬ事態が起きるかもしれないので注意が必要。
　(my.cnfでも排他的に定義しないといけないという状況は変わらないが)

４－３．当パラメータを設定する事について

　当パラメータはクライアント側での文字コードが指定されても無視してサーバー側の設定を用いるという設定値である。
　そして、MySQL4.0からの移行用なのであまりお勧めしないという記事を多く見かける。
　しかしながら、MySQL8のドキュメントには非推奨パラメータみたいな記載は発見できない。
　(只のメンテ不足という可能性はあるが…)
　MySQL :: MySQL 8.0 Reference Manual :: 5.1.7 Server Command Options
　MySQL8.0においても、FAQで当パラメータの設定に関する回答は存置である。
　当FAQでは、アジア地域においてはMySQL4.0の設定が望ましい場合もあるという見解である。
　また、推奨していないという文面をマニュアルからは発見できなかった。
　MySQL :: MySQL 8.0 Reference Manual :: A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
　一方、以下の様な脆弱性に関する観点から設定するべきではないという意見もある。
　404 エラー | へぼい日記
　結果、非推奨ではないが、効能把握した上で使わないと問題或るパラメータと理解とした。

照合順序

１．照合順序とは

　・ソートなどを行う際の順番を決定する
　・また、例えば"あ"と"ア"は同じ文字か違う文字かなどを決定する
　・aiやciが何なのか？具体的な照合順序の違いなどに関しては、最後の参考リンク先を参考に確認して欲しい

２．照合順序に関するパラメータ

２．１．collation-server

　・サーバー全体で使用する照合順序を指定
　・character-set-serverを指定してcollation-serverを指定しない場合は、character-set-serverで指定した文字コードのデフォルト照合順序が設定される
　・collation_serverとcollation_databaseが従属し、collation_connectionもこの値を参照していそう

２．１．１．collation_server
　・character_set_serverを用いて文字コードを設定する際の照合順序
　・指定が無ければutf8mb4の場合はdefault_collation_for_utf8mb4の値が適応される
　　それ以外は文字コードに設定されている照合順序が適応されると思われる(未検証)

２．１．２．collation_database
　・character_set_databaseを用いて文字コードを設定する際の照合順序
　・但し、globalパラメータとしての利用は、5.7及び8.0ではdeprecatedなパラメータ

(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_collation_database より抜粋)

The global character_set_database and collation_database system variables are deprecated; expect them to be removed in a future version of MySQL.

２．２．collation-client

　・当該パラメータは存在しない
　・照合順序に関するクライアントのパラメータが、collation_conectionのみのためと推測

２－２－１．collation_connection
　・character_set_connectionを用いて文字コードを設定する際の照合順序
　・指定が無ければutf8mb4の場合はdefault_collation_for_utf8mb4の値が適応される
　　それ以外は文字コードに設定されている照合順序が適応されると思われる(未検証)

２．３．default_collation_for_utf8mb4

　・MySQL8.0から用意されているパラメータ
　・utf8mb4を設定した場合のデフォルト照合順序を決めるためのパラメータ
　・レプリケーションみたいな記載がマニュアルにあるが、普通に当値を元に照合順序が決定している模様
　・utf8mb4のMySQL5.7のデフォルトがutf8mb4_general_ci、MySQL8.0のデフォルトがutf8mb4_0900_ai_ciという差を吸収するために用意されたパラメータ
　・設定に際し、きちんと設定されているか要注意(詳細は4.1に記載)

３．設定方法

３．１．my.cnfの場合

　MySQL5.7の場合：未指定でOK
　(∵MySQL5.7のutf8mb4のデフォルト照合順序がutf8mb4_general_ciのため)
　MySQL8.0の場合：collation-server=utf8mb4_general_ci, default_collation_for_utf8mb4=utf8mb4_general_ciを設定
　(∵MySQL8.0のutf8mb4のデフォルト照合順序がutf8mb4_0900_ai_ciのため)

３．２．パラメータグループの場合

　MySQL5.7の場合：未編集でOK
　(∵MySQL5.7のutf8mb4のデフォルト照合順序がutf8mb4_general_ciのため)
　MySQL8.0の場合：以下をutf8mb4_general_ciを設定
　　・default_collation_for_utf8mb4
　　・collation_server
　　・collation_connection
　サーバー起動済の場合は再起動すればcollation_databaseの値もutf8mb4_general_ciに変わった
　(collation_databaseがglobalでは非推奨値なので気にする必要はないやろけど)

４．注意点

４．１．default_collation_for_utf8mb4がデータベースを再起動すると初期化される場合がある

　MySQL8のバグいけてない仕様で、再起動するとクリアされる。
　詳細はサイボウズさんのブログにうまくまとめられていた。
MySQL 8.0 への移行が完了しました～さようなら全ての MySQL 5.7～ - Cybozu Inside Out | サイボウズエンジニアのブログ
　(SET PERSIST使わず、CREATE TABLE時に付与する対応方法も採用見送っているとのこと。
　独自の設定ファイルというところで対応された感じでしょうか・・・？)

　そのため、以下の様な対応をすれば永続化が可能である。
　[インストール版の場合]

SET PERSIST default_collation_for_utf8mb4=utf8mb4_general_ci;

　なお、SET PERSISTは別のところに保存されるため取り扱いは注意する必要がある。
　gihyo.jp

　[AmazonRDS for MySQLの場合]
　persisted_globals_loadがパラメータグループで無効化されており、永続化できない。。。
　パラメータグループにdefault_collation_for_utf8mb4は存在するが、
　RDSが再起動かかるとutf8mb4_0900_ai_ciに変わってしまっている…。
　そのため、現状各テーブル、カラムに照合順序を付けてDDLを流すしかないと思われる。

Tips

●現在設定されている文字コードと照合順序を確認したい(要権限)

テーブルやカラムを確認する場合は、絞り込んで実行しないとびっくりする位出力する場合があるため注意

select * from information_schema.schemata; ----- スキーマ
select * from information_schema.tables where table_schema = スキーマ名; ----- テーブル
select * from information_schema.columns where table_schema = スキーマ名 and table_name = テーブル名; ----- カラム
show create schema(database) スキーマ名; ----- スキーマ
show create table スキーマ名.テーブル名; ----- テーブル情報全体

●現在のセッションで有効な文字コードのパラメータを確認したい

show variables like '%character_set\_%';

●現在のセッションで有効な照合順序のパラメータを確認したい

show variables like %collation\_%

●データベース側のパラメータを確認したい

show variablesは現在のセッションで用いているパラメータが確認可能
global variablesはデータベース自体のパラメータを確認可能
(skip-character-set-client-handshakeやinit_connectなどで上書きしている場合に差異が発生してくる)

show global variables like %hogehoge\_%

なお、select @@global.パラメータ名がglobal variables、select @@パラメータ名がvariablesの照会結果と等価
(表示幅を対応させたい場合に用いる構文とのことで、通常はshow (global) variablesを使った方が便利)
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.7.41 SHOW VARIABLES ステートメント

●利用できる文字コード一覧が知りたい

各文字コードにそれぞれデフォルトの照合順序が設定されており、照合順序のパラメータの設定がされていない場合はこの値を用いて設定する

SHOW CHARACTER SET

●利用できる照合順序一覧が知りたい

SHOW COLLATION

●スキーマの照合順序を変更したい

----- 照合順序のみ
alter database スキーマ名 collate utf8mb4_general_ci;
----- 文字コードも
alter database スキーマ名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

●テーブルの照合順序を変更したい

時間かかっても問題無い時でないと、データ量などによっては自分の首も羽交い絞めとかでロックされるかも。。

----- 照合順序のみ
alter table テーブル名 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

●カラムの照合順序を変更したい

スキーマやテーブルは設定変更位で行える。
一方、カラムは再定義扱いとなる。
そのため、引数にカラム定義が必要であったり、設定されているデータなどによって上手くいかないなど変更作業は覚悟しておいた方がいい。。

----- 照合順序のみ
alter table テーブル名 MODIFY COLUMN カラム名 カラム定義 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 MODIFY COLUMN カラム名 カラム定義 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

但し、制約条件は存在するが、以下の方法で一括置換する方法も存在する。

alter table テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

(制約条件の一例)
・元々のDDLにはCHARACTER SETを無記名だったのが必ず設定された状態になる
・意図しない型に強制変換される場合が有 → この場合は個別にやっていくしかない…
・CONVERT TO CHARACTER SET binary～という指定は不可
詳細は、ALTER TABLE構文の最後らへんに記載有。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文
後、ブログに纏めてくれている方も。
MySQLでテーブルとカラムの文字コードを一括変更する - Be an Idealistic Realist