👨‍👩‍👦‍👦

ConoHa DBサーバー でutf8mb4を利用する方法

2023/04/09に公開

ConoHa DBサーバー」は,月600円ほどでMariaDBが利用できるという大変便利なサービスです.

しかし,ConoHa DBサーバーにおいては,デフォルトのcharsetutf8に設定されており,🍣を始めとした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_clientcharacter_set_connectioncharacter_set_resultsutf8mb4にできます.これらの設定は,データをクライアントと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_databaseutf8mb4になっているのではと思います.

テーブル・カラムのcharset変更

先ほど変更したデータベースが空っぽの場合は,新たに作成されるテーブル・カラムのcharsetcharacter_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であればデータは破損しないはずです(逆は破滅的な結果をもたらします).

詳細は,以下もご覧ください.

https://dev.mysql.com/doc/refman/8.0/ja/charset-conversion.html

https://www.karakaram.com/changing-the-character-set-to-utf8mb4-after-creating-mysql-table/

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