HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

データベースの文字エンコーディングを変更する

データベースの文字エンコーディングを変更する、と言っても、「設定ファイルを編集してデフォルトの文字エンコーディングを変更しよう!」などというちゃちな話ではない。(「MySQL 文字コード 変更」で探すとそんな話ばかりがヒットして‥もうね、ばくh(以下自粛))
すでにsjisだのcp932だのujisだのeucjpmsだのlatin1だのが指定され、データも格納されているデータベースについて、内部のデータを例えばUTF-8に変換して、今後挿入されるデータもUTF-8になるようにしよう、というお話。
ちなみに、MySQLにおけるUTF-8のお話はこの辺りで。

前提環境

極端な例を書くと、以下のようなデータベースとテーブルがあるとする。

DROP DATABASE convert_test1;
CREATE DATABASE convert_test1 DEFAULT CHARACTER SET sjis;

use convert_test1;

DROP TABLE IF EXISTS `tab_mixed`;
CREATE TABLE `tab_mixed` (
      k1  INTEGER NOT NULL AUTO_INCREMENT
    , s1t VARCHAR(64)                              NOT NULL
    , s1b VARCHAR(64) BINARY                       NOT NULL
    , s2t VARCHAR(64)        CHARACTER SET sjis    NOT NULL
    , s2b VARCHAR(64) BINARY CHARACTER SET sjis    NOT NULL
    , s3t VARCHAR(64)        CHARACTER SET ujis    NOT NULL
    , s3b VARCHAR(64) BINARY CHARACTER SET ujis    NOT NULL
    , s4t VARCHAR(64)        CHARACTER SET utf8    NOT NULL
    , s4b VARCHAR(64) BINARY CHARACTER SET utf8    NOT NULL
    , s5t VARCHAR(64)        CHARACTER SET cp932   NOT NULL
    , s5b VARCHAR(64) BINARY CHARACTER SET cp932   NOT NULL
    , s6t VARCHAR(64)        CHARACTER SET eucjpms NOT NULL
    , s6b VARCHAR(64) BINARY CHARACTER SET eucjpms NOT NULL
    , s7t VARCHAR(64)        CHARACTER SET latin1  NOT NULL
    , s7b VARCHAR(64) BINARY CHARACTER SET latin1  NOT NULL
    , PRIMARY KEY(k1)
) ENGINE=InnoDB DEFAULT CHARSET=cp932;

まずは、このSQL文を流した上で状況確認。

mysql> show create database convert_test1;
+---------------+------------------------------------------------------------------------+
| Database      | Create Database                                                        |
+---------------+------------------------------------------------------------------------+
| convert_test1 | CREATE DATABASE `convert_test1` /*!40100 DEFAULT CHARACTER SET sjis */ |
+---------------+------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> show create table tab_mixed;
+-----------+------------------------------------------------------------
| Table     | Create Table                                               
+-----------+------------------------------------------------------------
| tab_mixed | CREATE TABLE `tab_mixed` (
  `k1` int(11) NOT NULL AUTO_INCREMENT,
  `s1t` varchar(64) NOT NULL,
  `s1b` varchar(64) CHARACTER SET cp932 COLLATE cp932_bin NOT NULL,
  `s2t` varchar(64) CHARACTER SET sjis NOT NULL,
  `s2b` varchar(64) CHARACTER SET sjis COLLATE sjis_bin NOT NULL,
  `s3t` varchar(64) CHARACTER SET ujis NOT NULL,
  `s3b` varchar(64) CHARACTER SET ujis COLLATE ujis_bin NOT NULL,
  `s4t` varchar(64) CHARACTER SET utf8 NOT NULL,
  `s4b` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `s5t` varchar(64) NOT NULL,
  `s5b` varchar(64) CHARACTER SET cp932 COLLATE cp932_bin NOT NULL,
  `s6t` varchar(64) CHARACTER SET eucjpms NOT NULL,
  `s6b` varchar(64) CHARACTER SET eucjpms COLLATE eucjpms_bin NOT NULL,
  `s7t` varchar(64) CHARACTER SET latin1 NOT NULL,
  `s7b` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  PRIMARY KEY (`k1`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 |
+-----------+------------------------------------------------------------
1 row in set (0.18 sec)

mysql>

せっかくなので、これをMySQL 5.5で追加されたutf8mb4に変換することにする。

変換手順

まず、基本だが、対象のデータベースのフルダンプを取得しておく。
(参考:MySQLクイック・リファレンス)

mysqldump.exe --opt -c -u root -p convert_test1 > mysqldump.convert_test1.20120220.0131.sql

ダンプが取れたら、データベース、テーブルの順に文字エンコーディングを変更していく。
これはデフォルト設定の変更にしか過ぎないので、ここでデータ変換が発生することはない。
まずはデータベース。これは手作業でやってもいいだろう。

mysql> ALTER DATABASE convert_test1 DEFAULT CHARACTER SET utf8mb4;

続いてテーブルだが、数が多いと面倒になるので、PHPスクリプトでも書いて処理することにする。

<?php
// 指定したデータベースに含まれるすべてのテーブルのDEFAULT CHARASETを指定したものに変更する

$config = array('host' => 'localhost', 'port' => 3306,
            'user' => 'root', 'password' => 'hogehoge',
            'database' => 'convert_test1');

change_table_charset($config, 'utf8mb4');

function change_table_charset($config, $targetEncoding) {
    $host = $config['host'] . (isset($config['port']) ? ":{$config['port']}" : '');
    $conn = mysql_connect($host, $config['user'], $config['password']);
    if (!$conn) {
        var_dump(array('Connect Error', __FUNCTION__, $config));
        return false;
    }
    // データベース選択
    if (!mysql_select_db($config['database'], $conn)) {
        mysql_close($conn);
        var_dump(array('SelectDb Error', __FUNCTION__, $config));
        return false;
    }
    // クライアント側文字エンコーディングを明示(別になくても大丈夫)
    mysql_query("SET NAMES {$targetEncoding}", $conn);
    // テーブルの一覧を取得
    $result = execute_select("show tables", $conn, $config);
    if ($result === false) {
        mysql_close($conn);
        var_dump(array('GetTableList Error', __FUNCTION__, $config));
        return false;
    }
    // テーブルのデフォルト文字エンコーディング変更
    foreach ($result as $idx => $row) {
        $table = $row[0];
        $sql = "ALTER TABLE {$table} DEFAULT CHARACTER SET {$targetEncoding}";
        if (!mysql_query($sql, $conn)) {
            mysql_close($conn);
            var_dump(array('ChangeTableEncoding Error', __FUNCTION__, $config));
            return false;
        }
    }

    mysql_close($conn);

    return true;
}

中で使用しているexecute_select関数は次のようなもの。

<?php
function execute_select($sql, & $conn, & $config) {
    $res = mysql_query($sql, $conn);
    if (!$res) {
        var_dump(array('ExecQuery Error', __FUNCTION__, $config));
        return false;
    }
    // 結果取得
    $result = array();
    while (($row = mysql_fetch_row($res))) {
        $result[] = $row;
    }
    if (empty($result)) {
        var_dump(array('GetResult Error', __FUNCTION__, $config));
        return false;
    }

    return $result;
}

で、これを実行した後に再度show create tableでの確認をしてほしいのだが、DEFAULT CHARSET=cp932の時には表示されていなかったs1t、s5tの各列のCHARACTER SETも表示されるようになっている。
各列の文字エンコーディング変更はこれをベースに行わなければならない。
これも数が多いと面倒なので、スクリプトで自動化する。
基本的な流れは次のスクリプトのとおり。

<?php
// 指定したデータベースの指定したテーブルに含まれるすべてのカラムのCHARACTER SETとCOLLATEを指定したものに変更する
require_once('execute_select.php');

$config = array('host' => 'localhost', 'port' => 3309,
            'user' => 'root', 'password' => 'admin',
            'database' => 'convert_test1');
$table = 'tab_mixed';

$srcEncodings = array(
    'sjis' => 'sjis_bin',
    'cp932' => 'cp932_bin',
    'ujis' => 'ujis_bin',
    'eucjpms' => 'eucjpms_bin',
    'utf8' => 'utf8_bin',
);
change_column_charset($config, $table, $srcEncodings, 'utf8mb4');

function change_column_charset($config, $table, $srcEncodings, $targetEncoding, $targetCollate = null) {
    if (!isset($targetCollate)) {
        $targetCollate = $targetEncoding . '_bin';
    }

    $host = $config['host'] . (isset($config['port']) ? ":{$config['port']}" : '');
    $conn = mysql_connect($host, $config['user'], $config['password']);
    if (!$conn) {
        var_dump(array('Connect Error', __FUNCTION__, $config, $table));
        return false;
    }
    // データベース選択
    if (!mysql_select_db($config['database'], $conn)) {
        mysql_close($conn);
        var_dump(array('SelectDb Error', __FUNCTION__, $config, $table));
        return false;
    }
    // クライアント側文字エンコーディングを明示(別になくても大丈夫)
    mysql_query("SET NAMES {$targetEncoding}", $conn);

    $result = execute_select("show create table {$table}", $conn, $config);
    if ($result === false) {
        mysql_close($conn);
        var_dump(array('GetTableDefinition Error', __FUNCTION__, $config, $table));
        return false;
    }

    $lines = preg_split("/[\r\n]/", $result[0][1]);
    foreach ($lines as $line) {
        if (substr($line, 0, 3) === '  `' && preg_match('/CHARACTER SET/i', $line)) {
            $patterns = array(
                '/,$/',
                '/(`[^`]+`)/',
            );
            $replacements = array(
                '',
                '${1} ${1}',
            );
            foreach ($srcEncodings as $enc => $collate) {
                if (!isset($collate)) {
                    $collate = $enc . '_bin';
                }
                $patterns[] = '/CHARACTER SET ' . $enc . '( |,|$)/i';
                $patterns[] = '/COLLATE ' . $collate . '( |,|$)/i';
                $replacements[] = 'CHARACTER SET ' . $targetEncoding . '${1}';
                $replacements[] = 'COLLATE ' . $targetCollate . '${1}';
            }
            $line = preg_replace($patterns, $replacements, $line);
            $sql = "ALTER TABLE {$table} CHANGE {$line}";
            if (!mysql_query($sql, $conn)) {
                mysql_close($conn);
                var_dump(array('ChangeColumnEncoding Error', __FUNCTION__, $config, $table));
                return false;
            }
        }
    }

    mysql_close($conn);

    return true;
}

「基本的な」と書いたのは、変換に際して注意しなければならないケースがあるから。

なので、変換するに当たっては、変換前のクエリ結果と変換後のクエリ結果が同じであることを検証しなければならないのだが、疲れてきたのでその話はまた次回にということで。

(2012/02/23 01:00追記)
実は外部キー制約の被参照列の文字エンコーディングを変えようとするとエラーになることが発覚。なので、上記のスクリプトは文字列型の列に対する外部キー制約が存在しない環境でしか使えない。
自分が使うためにがんばって書いたのに結局‥orz