MySQLでテーブルとカラムの文字コードを一括変更する

MySQL linux

既存のデータベースの文字コードを異なる文字コードに変換した際のメモです。

留意すべき点

CONVERT TO CHARACTER SET を用いれば、比較的容易に DB と DB 内の全テーブルの全カラムの文字コードを変換できます。ただし、いくつか留意すべき点があります。

意図しない型の変換に注意！

CONVERT TO CHARACTER SET を用いた場合、型が自動的に変換されてしまうことがあります。
たとえば、utf-8 から utf8mb4 に変更する場合は、text 型のカラムは自動的に mediamtext に変換されます。

これは、VARCHAR や TEXT 系のカラムの文字コードを変換した際、新しいカラムが元のカラムの文字列を格納できることを保証するために行われるとのこと。

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column.

ただし、CONVERT TO CHARACTER SET は文字コードを変換するための機能なので、latin1 から utf8 への変換など、文字コードとして互換性のないデータは変換できません。そのような場合は、CONVERT TO CHARACTER SET を利用せずに以下のように対応する必要があります。

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

詳細は、MySQL :: MySQL 5.5 Reference Manual :: 13.1.7 ALTER TABLE Syntax の

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

という書き出しの段落にかかれています。

仕様や実装の移り変わりについては、以下を参照のこと。
MySQL Bugs: #31291: ALTER TABLE CONVERT TO CHARACTER SET does not change some data types

テーブルのキーとなるカラムの最大長が 767 バイトを超える場合

1文字の最大サイズの異なる文字コード同士で変換を行う場合、文字コード変換後にテーブルのキーとなるカラムの最大長が 767 バイトを超える場合には変換ができず、以下のようなエラーが発生します。

Specified key was too long; max key length is 767 bytes

たとえば、utf-8 から utf8mb4 に変更する場合、varchar(255) のカラムがキー指定されていた場合は元のカラムの最大長は 511 bytes (255*2) ですが、変更後は 1020 bytes(255*4) となり、767バイトを超えてエラーになります。このような場合は手動での対応が必要になります(後述)。

手順

shell 変数定義

事前に必要な情報を定義しておきます。

MYSQL_DATA_DIR=                         # MySQLのデータフォルダ
MYSQL_BKUP_DIR=                         # MySQLのデータフォルダのバックアップフォルダ
MYSQL_DB=                               # 対象のデータベース名
MYSQL_USER=                             # 対象データベースのユーザー名
MYSQL_PASS=                             # 対象データベースのパスワード
TABLE_INFO_BEFORE=table_info_before.txt # 変更前の CREATE TABLE 情報
TABLE_INFO_AFTER=table_info_after.txt   # 変更後の CREATE TABLE 情報
CONVERT_SCRIP_FILE=convert_script       # 文字コード変換用スクリプト名

データのバックアップ

  • mysqlを停止する
  • data ディレクトリ以下をコピーしてバックアップ
cp -rp ${MYSQL_DATA_DIR} ${MYSQL_BKUP_DIR}

※復元する場合

rm -rf $MYSQL_DATA_DIR; cp -rp ${MYSQL_BKUP_DIR} ${MYSQL_DATA_DIR}

変換前のデータベースとテーブルの定義情報の保存

文字コードを変換する前のデータベースとテーブルの定義情報を保存しておきます。

mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "show create database $MYSQL_DB" --batch --skip-column-names > $TABLE_INFO_BEFORE
mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "show tables" --batch --skip-column-names | xargs -I{} echo 'show create table `'{}'`;' | mysql -u$MYSQL_DB -p$MYSQL_PASS $MYSQL_DB | sed -e 's/\\n/\
/g' >> $TABLE_INFO_BEFORE

文字コード変換用スクリプトの作成

文字コードの変換を行うためのスクリプトを作成します。

(echo 'alter database `'"$MYSQL_DB"'` default character set utf8mb4;'; mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;') > $CONVERT_SCRIP_FILE

文字コード変換用スクリプトの目視確認と修正

キー指定されているカラムの最大長が 767 bytes を超えてしまう場合や、自動変換を行いたくないテーブルがある場合などは、必要に応じて修正を加えます。

vi $CONVERT_SCRIP_FILE

手動で指定する場合は、以下のように、1テーブルは1コマンドで変換を行うと効率的です。
(個別に行うとその回数だけテーブル全体の再作成を繰り返すことになります)

例：

alter table MY_TABLE
  default character set utf8mb4,
  modify `my_column1` char(36) character set utf8mb4 NOT NULL,
  modify `my_column2` varchar(16) character set utf8mb4 NOT NULL;

utf8 を utf8mb4 に変換する際に text 型を mediumtext に変換されたくない場合などで、元の DB 内で mediumtext を利用していないのであれば、CONVERT TO CHARACTER SET を利用して変換した後に、以下のように戻すという手もあります。

alter table MY_TABLE modify `my_column` text;

文字コード変換用スクリプトの実行

文字コードの変換を実行します(ついでに時間も計測しておきます)

time mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB < $CONVERT_SCRIP_FILE

変換後のデータベースとテーブルの定義情報の保存

文字コードを変換した後のデータベースとテーブルの定義情報を保存します。

mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "show create database $MYSQL_DB" --batch --skip-column-names > $TABLE_INFO_AFTER
mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "show tables" --batch --skip-column-names | xargs -I{} echo 'show create table `'{}'`;' | mysql -u$MYSQL_DB -p$MYSQL_PASS $MYSQL_DB | sed -e 's/\\n/\
/g' >> $TABLE_INFO_AFTER

変換前後のテーブル定義情報の比較

変換前後のテーブル定義情報を比較し、意図した変換が実行されたかどうかを確認します。

diff $TABLE_INFO_BEFORE $TABLE_INFO_AFTER

my.cnfの設定変更

必要があれば、/etc/my.cnf の設定を変更する

[mysqld]
character-set-server = utf8mb4

以上

雑多メモ

utf8 のカラムに utf8mb4 の文字で where をかけた場合のエラー (select * from MY_TABLE where my_string = ? など)
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
utf8 のカラムに utf8mb4 の文字を格納しようとした場合のエラー(update MY_TABLE set my_string = ? where id = ? など)
Incorrect string value: '\xF3\xBE\x80\x80' for column 'name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF3\xBE\x80\x80' for column 'name' at row 1