mariadb 文字コードをutf8からutf8mb4へ

Pocket

やってみました。

環境はこちら

[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の型変更もできるみたいです
http://wiki.minaco.net/index.php?MySQL%2FALTER

4 Replies to “mariadb 文字コードをutf8からutf8mb4へ

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください