やってみました。
環境はこちら
[root@n54l ~]# mysql --version mysql Ver 15.1 Distrib 10.1.5-MariaDB, for Linux (x86_64) using readline 5.1
まずこの状態から。
MariaDB [(none)]> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec)
server.cnfに書いていた character-set-server だけ utf8mb4 にしてみる
[root@n54l ~]# cat /etc/my.cnf.d/server.cnf [server] character-set-server = utf8 [mysqld] character-set-server = utf8mb4 plugin-load = handlersocket.so
再起動して確認
MariaDB [(none)]> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
変わった
[server] character-set-server = utf8dmb4
にしてみたけどこれは変化がなかった
clientを追加
[root@n54l ~]# cat /etc/my.cnf.d/server.cnf [server] character-set-server = utf8mb4 [mysqld] character-set-server = utf8mb4 plugin-load = handlersocket.so [client] default-character-set = utf8mb4
MariaDB [(none)]> show 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 | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
これで既存のDBに入ってみると
MariaDB [(none)]> use encode; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [encode]> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec)
databaseはutf8のまま
新しく作るとdatabaseも新しいものになる
MariaDB [encode]> create database astel_utf
-> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [encode]> use astel_utf
Database changed
MariaDB [astel_utf]> show 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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
character_set_system は man mysqld によると識別子を保存するための文字コードで、常にutf8とのことです ってどこかに書いてました。
既存のDBを今後はutf8mb4にするには ALTERでデフォルトを設定し直す。 この設定で今後入るデータはutf8mb4になる
MariaDB [astel_utf]> use encode; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [encode]> MariaDB [encode]> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec) MariaDB [encode]> ALTER DATABASE encode DEFAULT CHARACTER SET utf8mb4; Query OK, 1 row affected (0.00 sec) MariaDB [encode]> show 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 | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
既存のDBの型変更もできるみたいです
