MySQL 文字コード周りの確認をするコマンドまとめ

以前MySQLで文字コード周りを調べたときに使ったコマンド類のまとめ書き。
以前：https://qiita.com/oka884/items/e704afe51e35224b0e19

公式のリファレンスマニュアルが普通にわかりやすい。
https://dev.mysql.com/doc/refman/5.6/ja/information-schema.html

##まえおき

基本的にデータベースの情報はインストール時に作られる INFORMATION_SCHEMA データベースの中に格納されているらしい。
なので show コマンドというものがだいたい INFORMATION_SCHEMA データベースの中の一部を見るという操作をしているものだったりする。

というわけで自分用のメモとして、show文と select文でどちらも大体同じものが見れるものについては併記しています。

#####文字コードに関する仕様

起動時に読む my.ini の記述により 変数character_set_xxx に文字コードが代入される
↓
CREATE DATABASE 時に変数が参照され、データベースにデフォルトの文字コードが設定される
↓
データベースに設定されたデフォルトの文字コードで CREATE TABLE される。

文字コードはテーブル毎に設定されている。

##システム変数の値の確認

新しくデータベースを作成する時に使われるシステム変数を確認します。
またこの変数は my.ini ファイルへ記述することで変更できます。
逆に言うと my.ini ファイルの記述を確認することでもあります。

SESSION と GLOBAL とふたつ値があり、
SESSION は現在の接続に有効な値
GLOBAL は新しい接続に利用される値
だそうです。

SESSIONの確認

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'char%';
MariaDB [(none)]> select * from INFORMATION_SCHEMA.SESSION_VARIABLES where Variable_name like 'char%';

GLOBALの確認

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'char%';
MariaDB [(none)]> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES where Variable_name like 'char%';

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------------------------------+
| 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     | utf8                                                           |
| character_sets_dir       | C:\ <--中略--> mysql\share\charsets\ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.000 sec)

##作られたデータベースの DEFAULT_CHARACTER_SET の確認

上で確認した変数が参照されデータベースが作られます。
作られた時の変数の値によって、データベースごとに DEFAULT_CCHARACTER_SET は変わります。
（もちろんALTER DATABASEで後から変えることもできます）

以下でデータベースの一覧と、それらの DEFAULT_CHARACTER_SET と DEFAULT_COLLATION の確認ができます。

MariaDB [(none)]> select * from INFORMATION_SCHEMA.SCHEMATA;

MariaDB [(none)]> select * from INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | db1                | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | phpmyadmin         | utf8                       | utf8_bin               | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
6 rows in set (0.01 sec)

##テーブルの文字コードの確認

上で確認したのはテーブル作成時のデフォルトの CHARACTER_SET です。
CREATE TABLE する時に CHARACTER SET や COLLATE を引数で指定した場合や、あるいは途中でデータベースの DEFAULT_CHARACTER_SET を変更した場合、テーブルごとに文字コード違うこともあります。

テーブルの場合は一覧で確認する方法がわかりませんでした。
データベースに降り以下でテーブルひとつひとつ確認します。

MariaDB [dbname]> SHOW CREATE TABLE tbname;

MariaDB [db1]> SHOW CREATE TABLE tb10;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+--------------------------------------------------------------------------------------------------------------+
| tb10  | CREATE TABLE `tb10` (
  `num` int(11) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

以上でMySQLで文字コード周りの現状の確認がおおよそできる（はず）。

##使える CHARACTER SET と COLLATION の確認

MySQLで利用できる CHARACTER SET と COLLATION の一覧を確認するコマンドは以下です。

利用できる CHARACTER SET の確認

MariaDB [(none)]> SHOW CHARACTER SET;
MariaDB [(none)]> select * from INFORMATION_SCHEMA.CHARACTER_SETS;

利用できる COLLATION の確認（ここでは utf8mb4 の collation を確認しています）

MariaDB [(none)]> > SHOW COLLATION LIKE 'utf8mb4%';