MySQLの既存のデータベース、テーブル、カラムの文字コードの変更方法（utf-8→utf8mb4)

my.cnfを書き込んでMySQL自体の文字コードをutf8からutfmb4へと変更できたのですが、
既に作られているデータベースやテーブルなどが挿入できない事例が発生。これにとても悩んだので備忘録します。

絵文字が挿入できない

mysql> 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/local/Cellar/mysql@5.7/5.7.31/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+

からmy.cnfに書き込んで

mysql> 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/local/Cellar/mysql@5.7/5.7.31/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+

にしたのに既に作られたテーブルに以下のように入力すると

mysql> insert into posts ( user_id , content ) values  ( 4 , '😀' );
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content' at row 1

うーん、謎である。

原因として

確かに、MySQL自体の文字コードを変えたんですけど、既に作られたデータベースの文字コードは変更されていません。
なので、データベース、テーブル、カラムの文字コードを変更方法を書きます。

データベースの文字コードの変更

まず、既存のデータベースの文字コードを確認する
show create database (データベース名);を入力して

mysql> show create database training;
+----------+----------------------------------------------------------------------+
| Database | Create Database                                                      |
+----------+----------------------------------------------------------------------+
| training | CREATE DATABASE `training` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------------+

確かにDEFAULT CHARACTER SET utf8となっているので
alter database (データベース名) default character set utf8mb4;を実行して確認すると

mysql> show create database training;
+----------+----------------------------------------------------------------------+
| Database | Create Database                                                      |
+----------+----------------------------------------------------------------------+
| training | CREATE DATABASE `training` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------------+

となり確かに文字コードが変わった。

テーブルの文字コードの変更

まずは、文字コードを確認する。
show create table (テーブル名);と入力して

mysql> show create table posts;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| posts | CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

となってDEFAULT CHARSET=utf8となっています。ここを変えるために
alter table (テーブル名) default character set utf8mb4;と実行して改めて確認すると

  mysql> show create table posts;
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                         |
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | posts | CREATE TABLE `posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `content` text CHARACTER SET utf8,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 |
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

となり、DEFAULT CHARSET=utf8mb4と変わっています。
また、この中に前にcontentのところにCHARACTER SET utf8と急に出てきました。
こちらを変更しましょう。

カラムの文字コードを変える

ALTER TABLE (table名) MODIFY (カラム名) text CHARACTER SET utf8mb4;を実行して確認する

mysql> show create table posts;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| posts | CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

さっきまであったCHARACTER SET utf8が消えました。
おそらくテーブルと中身のカラムが一致したからかと思います。

これで一番最初のinsert into posts ( user_id , content ) values ( 4 , '😀' );を実行すると

mysql> insert into posts ( user_id , content ) values  ( 4 , '😀' );
Query OK, 1 row affected (0.00 sec)

となりました。これで完了です。

参考文献

https://hodalog.com/modify-the-character-encode-of-mysql/
http://mysql.javarou.com/dat/000573.html
http://artisan.hatenablog.com/entry/2013/11/17/134634