読者です 読者をやめる 読者になる 読者になる

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

プログラミングとかAndroid

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

Mac SQL ターミナル プログラミング系 設定

前も書いたけど、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 と寿司ビール問題 - かみぽわーる

広告を非表示にする