ハトネコエ Web がくしゅうちょう

プログラミングとかAndroid

MySQL の文字エンコーディングを utf8mb4 にあとから変更する

前も書いたけど、MySQL をいじるには、入力補完してくれる mycli が超便利。
これなしの環境だとめんどうに感じてしまうくらいに!

あとダミーデータの作成は generatedata.com が良さげ。
これはもっと良いサイトありそうなので、ご存知の方、教えてくださいますと幸いです。

さて、練習のためになんとなくデータベースとテーブル作っちゃったけど、
なんの設定もしてないから文字コードが utf8mb4 じゃないじゃん、ってときの対処法。

なんで utf8 でなく utf8mb4 ?

MySQL の公式に解説が出ています。
https://dev.mysql.com/doc/refman/5.6/ja/charset-unicode-upgrading.html

MySQL での utf8 は、1文字を3バイトとする文字エンコーディングです。
一方の utf8mb4 は、1文字を4バイトとしています。
ここから mb4 というのは、「マルチバイト4」の略だと想像できますね。

4バイト文字には、以下のページで書かれている文字群と、絵文字の大部分が該当します。
UTF-8で4バイトになる文字 at softelメモ

これが含まれていると、正しくデータはインサートされず、対応外の文字の部分で切れてしまうそうです。
(参考 : MySQLに絵文字を格納するにはutf8mb4を使う必要があった - F.Ko-Jiの「一秒後は未来」

『今日は初めてのデート💖 わくわく✨』という文が
『今日は初めてのデート』と保存されてしまうので、ブログサービスでは致命的です。

ちなみに MySQL コマンドラインツール上ではエラーが出てはじかれます。
f:id:nekonenene:20160802113705p:plain

(注:後述する character-set-client の設定が utf8mb4 になっていないと、カラムの設定が utf8mb4 でもコマンドラインからデータを入れられません。しばらくそこでハマった)

カラムを utf8mb4 に設定変更

上の画像で出しちゃってますけど、alter table で設定変更します。

今回は
データベース名 : television
テーブル名 : channel
カラム :

`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`region` varchar(10) DEFAULT NULL

となっています。
これの name と region の文字コードを変更したいのですから、

ALTER TABLE television.channel MODIFY `name` VARCHAR(30) character set utf8mb4, MODIFY `region` varchar(10) character set utf8mb4;

と設定します。以上です。カンタンね!

my.cnf の設定

カラム設定にて utf8mb4 で格納できるようにしたところで、
文字列の受け渡し部分が utf8mb4 を認識できないと、
「なんか文字じゃないものが入ってるよ!」って言ってエラー出してしまいます。

上で書きましたが、character-set-client の設定が utf8mb4 でないといけないわけです。

設定は my.cnf に記述します。

/etc/my.cnf を開き(または新規作成し)、以下のように記述します。

[mysqld]
  character-set-server=utf8mb4 ## database 新規作成時のデフォルト文字エンコーディング

[client]
  default-character-set=utf8mb4 ## character_set_client, character_set_connection, character_set_results の設定

その後、以下のコマンドで MySQL サーバーを再起動します。

mysql.server restart

(私はいつも間違うんですが、ここ sudo mysql.server restart と sudo を付けてしまわないこと。もちろんファイル権限の設定によるんですが)


MySQLサーバーの再起動後は再び MySQL を起動し、

SHOW VARIABLES LIKE "chara%";

とコマンドを試して

+--------------------------+------------------------------------------------------+
| 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/local/Cellar/mysql/5.7.11/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+

というふうに、utf8mb4 に設定されているのが確認できればOKです。
(それぞれの意味については、こちらのページが簡潔にまとめてくれています : MySQLに日本語でINSERTできない場合 - たこはちの「へのかっぱ」日記

新規テーブル, カラム作成時のデフォルトを変更

上の my.cnf の設定で大丈夫なのですが、
今回のようにすでにデータベースやテーブルを作っている場合は、
新規テーブルや新規カラム作成時のデフォルト文字エンコーディングを変えておくといいでしょう。

新規カラム作成時のための、テーブルのデフォルト文字エンコーディングを変更

ALTER TABLE channel DEFAULT character set utf8mb4 COLLATE utf8mb4_bin;

新規テーブル作成時のための、データベースのデフォルト文字エンコーディングを変更

ALTER DATABASE television DEFAULT character set utf8mb4 COLLATE utf8mb4_bin;

これで安心です。
なお、COLLATE utf8mb4_bin は文字の区別についての設定です。
詳しくは、「寿司ビール問題」の解説をご覧ください。
MySQL と寿司ビール問題 - かみぽわーる