まとめてMySQLのテーブル文字コードを'utf8mb4'に変換する【WordPressなど・MariaDB】
7年ぶりというか下手したらサイト開設以来、初めてバックエンドを刷新した際の話。
久しぶりにすべてのサーバー設定を見直したわけですが、その中で MariaDB のいう 'utf8' が実は最長3バイトの 'utf8mb3' のエイリアスだと初めて知りました。（2024年5月時点）
文字コードが 'utf8mb3' だと何が困るかというと、例えば4バイト長になる絵文字や竈門禰󠄀豆子の「禰󠄀」といった漢字が扱えない。
この影響から、例えば WordPress では記事の保存時、
「更新に失敗しました。データベース内の投稿を更新できませんでした。」
というエラーが出て保存できない、といった症状が出るわけです。
これを解決するには、データベース接続周りや WordPress のテーブル文字コードを最長4バイトの 'utf8mb4' に変更・変換してやる必要があります。
この内、my.cnf や mariadb.conf.d の設定は他サイトにどれだけでも情報があるので譲るとして、その先の各テーブルの Character Set 変換がこの記事の主題。
WordPress にはテーブルの Character Set を1発で 'utf8mb4' へ変換してくれる関数 maybe_convert_table_to_utf8mb4() が用意されている。
それを使った以下のコードを公開されている方がみえたので、お借りしてテスト系の functions.php に入れて実行してみたりもした。
if( isset($_GET["upgradeUtf8mb4"]) && $_GET["upgradeUtf8mb4"]==1 )
{
require_once ABSPATH."wp-admin/includes/upgrade.php";
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 "done.";
exit;
}Code language: PHP (php)（※ コード引用元 https://planet-green.com/upgrade-wp-utf8mb4/1475）
しかしその後、下のクエリで全テーブルの DEFAULT_CHARACTER_SET を確認しても、
SELECT
TABLE_SCHEMA,
TABLE_NAME,
CCSA.character_set_name AS DEFAULT_CHARACTER_SET
FROM
information_schema.`TABLES` AS T
JOIN
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS CCSA ON T.TABLE_COLLATION = CCSA.COLLATION_NAME
WHERE
TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
Code language: PHP (php)肝心の wp_posts を始め半数以上のテーブルが 'utf8mb3' のままで変換されていない。ダメ。
仕方なく全てのテーブルの Character Set と Collation を 'utf8mb4' へ変換するクエリを生成するクエリを作ったので、同じ境遇の人のために共有しておく。（これがこの記事の本題）
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS query
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_COLLATION LIKE 'utf8%';Code language: PHP (php)※ 'your_database_name' 部は自分の WordPress のデータベース名に置き換え
上のクエリは、下のようなクエリを大量生成するだけのものだ。なので基本的には WordPress 以外でも使えるはず。
ALTER TABLE `your_database_name`.`wp_posts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Code language: JavaScript (javascript)あとは、生成されたクエリを手動で流せば解決。
これで絵文字が使えるようになりました！😃
ちなみに、MariaDB は現状でも 'utf8mb4' を推奨しており、将来的に 'utf8' は 'utf8mb4' のエイリアスに変更される予定とのこと。
さて、ここはテラダス、私は "ひろも" ですから余談を少し。
字面しか見ていないので勘違いかもだけど、鬼滅の刃の登場人物「竈門禰󠄀豆子」の名前には、実は JIS90 → JIS2004 で字形変更となった文字が2文字も含まれている。
1つは「竈」で、もう1つが「禰󠄀」だ。
また、煉獄さんの「煉」も確か字形変更の対象文字で、わずか168文字しかない JIS90 → JIS2004 の字形変更文字がこうも偶然に使われるものかとは感じるところがあります。
さらに「竈」はJIS2004字形で、「禰󠄀」はJIS90字形というのもややこしいし、「竈」は一見して字形の違いが分かりにくい文字であることも面白い。これは出版畑やソフトウェア畑の方がワザとこういう文字を登場人物名に入れたのではないか、と勘ぐりたくもなってしまうというものです。
未検証の戯言であるから、好事家の皆さんは感化されて人に言いふらす前に各自検証されるように。
参考情報：
