MySQL キャラクタセット（文字コード）の変換方法おさらい

ALTER TABLE xxx MODIFY 〜 でキャラクタセットを変換するのと、ALTER TABLE xxx CONVERT TO〜 で変換するのは、どう違うんだっけ？ってなったので、おさらい。

データベースのデフォルトキャラク タセット

MySQL はカラム単位でキャラクタセット（文字コード）を指定することが出来ます。

データベース（スキーマ）のキャラクタセットは、「テーブル作成時にテーブルのキャラクタセットを指定しなかったときに採用されるキャラクタセット」の指定です。 CREATE DATABASE db1 CHARACTER SET ujis と DEFAULT を省略して書くことが出来ますが、省略せずに書くと、DEFAULT CHARACTER SET です。デフォルトのキャラクタセットの指定であることが、SHOW CREATE TABLEの結果からもわかります。

# データベースのデフォルトキャラクタセットを ujis にする
mysql> CREATE DATABASE db1 CHARACTER SET ujis;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET ujis */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を指定しない場合）

DEFAULT CHARSET=ujis と、データベースのデフォルトキャラクタセットを引き継いでいることがわかる。

mysql> CREATE TABLE tbl_no_char (pk SERIAL, c1 VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_no_char \G
*************************** 1. row ***************************
       Table: tbl_no_char
Create Table: CREATE TABLE `tbl_no_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を明示的に utf8mb4 に指定）

mysql> CREATE TABLE tbl_with_char (pk SERIAL c1 VARCHAR(255) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_with_char \G
*************************** 1. row ***************************
       Table: tbl_with_char
Create Table: CREATE TABLE `tbl_with_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

テーブルのデフォルトキャラク タセット

テーブルのデフォルトキャラクタセットも同様です。CREATE TABLE時にカラムのキャラクタセットを明示的に指定しなかった場合、テーブルのデフォルトキャラクタセットが採用されます。

mysql> CREATE TABLE tbl_mix_char (
    pk SERIAL,
    ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL,
    utf8mb4_col VARCHAR(255) NOT NULL) 
    CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) CHARACTER SET ujis NOT NULL,
  `utf8mb4_col` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

念の為、ujisには存在しない絵文字🍣をINSERTして、utb8mb4_col が utf8mb4 であることを確認してみます。

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('', '🍣たべたい');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('🍣たべたい' '');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 1

テーブルのデフォルトキャラクタセットを ujis にしてみます。

すると、SHOW CRETE TABLEの結果のujis_col varchar(255) CHARACTER SET ujis NOT NULL が ujis_col varchar(255) NOT NULL に変わりました。テーブルのデフォルトキャラクタセットとキャラクタセットが一致するカラムは CHARACTER SET 〜 の表示が省略される作りのようです。

mysql> ALTER TABLE tbl_mix_char CHARACTER SET ujis;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) NOT NULL,
  `utf8mb4_col` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

データを変換する

データベースとテーブルのデフォルトキャラクタセットを変更しても、実際に保存されているデータは変換されません。これらはCREATE時に明示的にキャラクタセットを指定しなかったときに採用されるキャラクタセットを示しているだけですから。

実際にデータのキャラクタセットを変換方法は２つ。

1. カラムごとに変更する

utf8 → utf8mb4 に ALTER TABLE xxx MODIFY 〜 で変換します。この方法では、テーブルのデフォルトキャラクタセットは変更されません（別途、必要に応じて変更）。

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY t1 TEXT CHARACTER SET utf8mb4, MODIFY c1 VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text CHARACTER SET utf8mb4,
  `c1` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2. テーブル全体を変換する

カラム単位での変換は、カラム数が多いと面倒です。ALTER TABLE xxx CONVERT TO CHARACTER SET 〜でテーブルをまるごと変換できます。

TEXT型の扱いに注意が必要です。１文字あたりのバイト数が増える場合、TEXT型が自動的にMEDIUMTEXT型に変更されます。

データ型が VARCHAR か、またはいずれかの TEXT 型であるカラムに対して、CONVERT TO CHARACTER SET は、新しいカラムが確実に元のカラムと同じ数の文字を格納できる十分な長さになるように、必要に応じてデータ型を変更します。たとえば、TEXT カラムには、そのカラム内の値のバイト長 (最大 65,535) を格納するための 2 バイト長があります。 latin1 TEXT カラムの場合は、各文字に 1 バイトが必要なため、このカラムには最大 65,535 文字を格納できます。このカラムが utf8 に変換された場合は、各文字に最大 3 バイトが必要になる可能性があるため、可能性のある最大の長さは 3 × 65,535 = 196,605 バイトになります。その長さは TEXT カラムのバイト長には収まらないため、MySQL はそのデータ型を、バイト長に 196,605 の値を記録できる最小の文字列型である MEDIUMTEXT に変換します。同様に、VARCHAR カラムは MEDIUMTEXT に変換される可能性があります。

https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` mediumtext,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

変換できない文字があるときは？

変換できない文字があればエラーになります。

# utf8mb4 から ujis に変換する、絵文字はujisにないのでエラーに
mysql> ALTER TABLE tbl_mix_char MODIFY ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL;
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 2

デフォルトキャラク タセットの変更も忘れずに

特別な事情がない限りは、データベースとテーブルのデフォルトキャラクタセットと、カラムのキャラクタセットはすべて揃える運用でしょう。データを変更したあとは、忘れずに、データベースとテーブルのデフォルトキャラクタセットも変換が必要です。

mysql> ALTER TABLE t1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER  DATABASE db1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mita2 database life

主にMySQLに関するメモです