ConoHa DBサーバー でutf8mb4を利用する方法
「ConoHa DBサーバー」は,月600円ほどでMariaDBが利用できるという大変便利なサービスです.
しかし,ConoHa DBサーバーにおいては,デフォルトのcharset
がutf8
に設定されており,🍣
を始めとしたEmojiや,𠮷
を始めとしたサロゲートペアを利用する文字を,デフォルトでは利用することができません(ネット上の情報には,ConoHa DBサーバーではutf8mb4
を利用できないとするものもありますが.古いか誤っています).
そこで.本稿では,ConoHa DBサーバー でutf8mb4を利用する方法を説明します(備忘録を付けます).
筆者はMariaDBにあまり詳しくありませんので,間違いがありましたら教えてもらえると嬉しいです.
以下,ConoHa DBサーバーをDBサーバーと略します.
できることできないこと
DBサーバーでは,以下のような制約があります.
- ユーザーはConoHaの管理画面で作成したデータベース以下へのアクセス権しか持ちません
- ユーザーはグローバルシステム変数を変更できません
-
my.conf
を編集できず,サーバーの初期設定を変更できません
ここで,デフォルトではcharset
はすべてutf8
に設定されています.
> show variables like "chara%";
+--------------------------+----------------------------+
| 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.002 sec)
そのため,初期状態ではutf8mb4
を利用できません.一方で,
- 作成したデータベースやテーブルの設定は変更できます
-
SET
等で,セッションシステム変数を変更できます
「できないこと」を踏まえてうえで,「できること」を利用して,utf8mb4
を利用できるようにします.
charset
変更
セッションのmysql
コマンドで接続する際に,--default-character-set=utf8mb4
オプションを付けます.すると,character_set_client
,character_set_connection
,character_set_results
をutf8mb4
にできます.これらの設定は,データをクライアントとDBサーバーでやり取りする際のcharset
を設定しています.
charset
変更
データベースのConoHaの管理画面から作成したデータベースは,character_set_server
のサーバー初期値によって,utf8
で作成されます.そのため,作成されたデータベースのcharset
を変更する必要があります.
> ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
use
でDBを選択し,show variables like "chara%";
をすると,character_set_database
がutf8mb4
になっているのではと思います.
charset
変更
テーブル・カラムの先ほど変更したデータベースが空っぽの場合は,新たに作成されるテーブル・カラムのcharset
はcharacter_set_database
となるため,この設定は不要のはずです.
CREATE TABLE ステートメントでは、テーブル文字セットおよび照合順序が指定されていない場合、データベース文字セットおよび照合順序がテーブル定義のデフォルト値として使用されます。 これをオーバーライドするには、CHARACTER SET および COLLATE テーブルオプションを明示的に指定します。
一方で,すでにデータが存在する場合は,既存データのcharset
変換+テーブル・カラムのcharset
設定変更が必要になります.この操作は不可逆的な操作ですので,バックアップを取ったうえで,実行してください.
以下のクエリで,テーブルを指定し,そのテーブルのcharset
設定とすべてのカラムのcharset
設定,そして.既存データのcharset
変換が行われます.
> ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
utf8 -> utf8mb4
であればデータは破損しないはずです(逆は破滅的な結果をもたらします).
詳細は,以下もご覧ください.
utf8mb4
が使えるようになります
ここまでの設定を行うと,utf8mb4
が利用できるようになります.先述したオプション付きで,mysql
を起動し,charset
を変更したデータベースをuse
します.
> 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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
上記のようになっていれば問題ありません(既存テーブルのcharset
を変換した場合はテーブル・カラムの設定が変更されているか,データは破損していないかに注意してください).
Webアプリケーションにおける設定例 (Djangoの場合)
Webアプリケーションで,DBサーバーを利用する場合は,「セッションのcharset
変更」についてWebアプリケーション側で設定が必要です.
例えば,PythonのWebアプリケーションフレームワークDjangoでは,Settings.pyで,
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
...,
'OPTIONS': {
'charset': 'utf8mb4',
'sql_mode': 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',
},
}
}
のように指定することで,セッションのcharset
を設定できます.
また,余談ですが,DBサーバーではデフォルトで何も設定されていないsql_mode
も,この際に設定できます.特に制約モードであるSTRICT_TRANS_TABLES
の設定が,Djangoでは推奨されていますので設定しておきましょう.
django-environでは,同じように,DBのURIに,mysql://user:password@host:port/dbname?charset=utf8mb4&sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
のように?
オプションをつけてあげることで設定できます.
設定後は,python manage.py dbshell
でDBに接続し,SHOW VARIABLES like 'char%';
を確認しましょう.
他のWebアプリケーションフレームワークでも,似たように設定できると思います.
終わりに
ConoHa DBサーバーでも,utf8mb4は利用できます. 設定がちょっと大変なだけです.
最近値上げしてしまいましたが,やはり価格は魅力的ですし,正しくutf8mb4
を設定し,Webアプリケーションを開発していきたいものです.
Discussion