MySQL utf8をutf8mb4に変更する

Linuxめも

MySQLにてUTF-8の4バイト文字を使用するためには文字コードはutf8mb4を指定すればいいことは別記事 MySQL 寿司ビール問題 にて書いた通りです。

では既存のutf8のデータベースをutf8mb4に変更するにはどうしたらいいのでしょうか。

基本的な方法†

テーブルの文字コードを変更します。

ALTER TABLE `table` DEFAULT CHARACTER SET utf8mb4;

4バイト文字が登録されるカラムの文字コードを変更します。

ALTER TABLE `table`
   modify `field1` varchar(255) CHARACTER SET utf8mb4,
   modify `field2` varchar(255) CHARACTER SET utf8mb4;

utf8mb4対象となるフィールドがINDEX対象であれば767byte問題 MySQL 767byte問題 に引っかかるため、データ型を変更する必要があります。
3,072byte使用できるようになっていればデータ型の変更は不要です。

ALTER TABLE `table`
   modify `index_field1` varchar(191) CHARACTER SET utf8mb4,
   modify `field2` varchar(255) CHARACTER SET utf8mb4;

あとはPHPからの接続時に文字コードを指定するようにします。

$dbh = new PDO('mysql:dbname=DBNAME;host=localhost;charset=utf8mb4;');

Movable Typeの場合†

Movable Type6.3系で試しました。
ブログの記事に絵文字を登録し、公開画面にて表示されることを確認しています。

utf8mb4で接続する†

lib/MT/ObjectDriver/Driver/DBD/mysql.pm
69行目あたりの記述を変更します。

            my %Charset = (
                #'utf-8'     => 'utf8',
                'utf-8'     => 'utf8mb4',
                'shift_jis' => 'sjis',
                'shift-jis' => 'sjis',
                'euc-jp'    => 'ujis',

                #'iso-8859-1' => 'latin1'
            );

テーブル定義を変更†

-- ブログ記事を保存するテーブルの文字コードを変更
ALTER TABLE `mt_entry` DEFAULT CHARACTER SET utf8mb4;

-- utf8で設定されているカラムの文字コードを変更
ALTER TABLE mt_entry
    modify `entry_approver_ids` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_atom_id` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_basename` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_class` varchar(255) CHARACTER SET utf8mb4 DEFAULT 'entry',
    modify `entry_convert_breaks` varchar(60) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_excerpt` mediumtext CHARACTER SET utf8mb4,
    modify `entry_ext_datas` mediumtext CHARACTER SET utf8mb4,
    modify `entry_keywords` mediumtext CHARACTER SET utf8mb4,
    modify `entry_pinged_urls` mediumtext CHARACTER SET utf8mb4,
    modify `entry_prefs` mediumtext CHARACTER SET utf8mb4,
    modify `entry_revision_comment` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_tangent_cache` mediumtext CHARACTER SET utf8mb4,
    modify `entry_text` mediumtext CHARACTER SET utf8mb4,
    modify `entry_text_more` mediumtext CHARACTER SET utf8mb4,
    modify `entry_title` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    modify `entry_to_ping_urls` mediumtext CHARACTER SET utf8mb4;

他にも4バイト文字を登録したい部分があれば適宜同様にテーブル定義を変更すれば対応できるはずです。

WordPressの場合†

Wordpressのバージョンを上げて対応する†

WordPress4.2からutf8mb4に対応しています。
それ以前のバージョンは条件を満たしていれば4.2へのアップグレード時に変更されます。
https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/

• You’re currently using the utf8 character set.
• Your MySQL server is version 5.5.3 or higher (including all 10.x versions of MariaDB).
• Your MySQL client libraries are version 5.5.3 or higher. If you’re using mysqlnd, 5.0.9 or higher.

utf8でMySQL5.5.3以上であれば対象となりそうです。

WordPressのバージョンを上げずに対応する†

アップグレードプログラムを実行してデータベースに変更を加えます。

WordPress4.1.20をインストールし、以下の方法で変更後、4バイト文字を投稿し表示されることを確認しました。

文字コードを変更します。

wp-config.php

define('DB_CHARSET', 'utf8mb4');

Twenty Fifteenテーマを使用している場合、functions.phpの最後に追記します。

wp-content/themes/twentyfifteen/functions.php

if (isset($_GET["upgradeUtf8mb4"]) && $_GET["upgradeUtf8mb4"] == 1) {
    if ( is_multisite() ) {
        $tables = $wpdb->tables( 'blog' );
    } else {
        $tables = $wpdb->tables( 'all' );
        if ( ! wp_should_upgrade_global_tables() ) {
            $global_tables = $wpdb->tables( 'global' );
            $tables = array_diff_assoc( $tables, $global_tables );
        }
    }

    foreach ( $tables as $table ) {
        maybe_convert_table_to_utf8mb4( $table );
    }

    echo "utf8mb4 convert done.";
    exit();
}

function maybe_convert_table_to_utf8mb4( $table ) {
    global $wpdb;

    $results = $wpdb->get_results( "SHOW FULL COLUMNS FROM `$table`" );
    if ( ! $results ) {
        return false;
    }

    foreach ( $results as $column ) {
        if ( $column->Collation ) {
            list( $charset ) = explode( '_', $column->Collation );
            $charset = strtolower( $charset );
            if ( 'utf8' !== $charset && 'utf8mb4' !== $charset ) {
                return false;
            }
        }
    }

    $table_details = $wpdb->get_row( "SHOW TABLE STATUS LIKE '$table'" );
    if ( ! $table_details ) {
        return false;
    }

    list( $table_charset ) = explode( '_', $table_details->Collation );
    $table_charset = strtolower( $table_charset );
    if ( 'utf8mb4' === $table_charset ) {
        return true;
    }

    return $wpdb->query( "ALTER TABLE $table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" );
}

function wp_should_upgrade_global_tables() {

    if ( defined( 'DO_NOT_UPGRADE_GLOBAL_TABLES' ) ) {
        return false;
    }

    $should_upgrade = true;

    if ( ! is_main_network() ) {
        $should_upgrade = false;
    }

    if ( ! is_main_site() ) {
        $should_upgrade = false;
    }

    return apply_filters( 'wp_should_upgrade_global_tables', $should_upgrade );
}

http://example.com/?upgradeUtf8mb4=1 へアクセスして「utf8mb4 convert done.」と表示されれば成功です。
functions.phpに追記した部分は削除しておきます。

EC-CUBE2系の場合†

調査中

EC-CUBE3系の場合†

EC-CUBE3系は2018年4月10日現在で3.0.15が最新バージョンで文字コードはutf8となっています。
3.0.13にてutf8mb4環境を考慮し、emailについてのインデックスが191byteに変更されています。
https://github.com/EC-CUBE/ec-cube/pull/1996