多言語サイトで推奨されるデータベースの照合順序と文字セット

MySQLにおける文字セットと照合順序

WordPressがサイトのコンテンツ（投稿、タイトル、翻訳された文字列など）をデータベースに保存する際、文字を表現するために文字セットを使用し、それらの文字を比較および並べ替える方法を定義するために照合順序を使用します。

文字セット（Charset）

文字がデータベースにどのように保存されるかを定義します。つまり、どのバイトがどの文字を表すかを決定します。

照合順序（Collation）

文字を比較するためのルール（大文字と小文字の区別、アクセントの区別、並べ替え順など）を定義します。

多言語サイトで重要となる理由

WPMLは多言語コンテンツをサポートしており、これには多くの場合以下が含まれます。

• 特殊文字（例：ñ、é、ö）
• ラテン文字以外の文字（例：アラビア語、日本語、中国語、ヘブライ語）
• 絵文字や記号（🎉、✔️など）

これらの多くは、正しく保存するために3バイト以上を必要とします。データベースが互換性のない照合順序を使用している場合、文字が失われたり、�に置き換えられたり、データベースエラー（「Incorrect string value」など）が発生したりする可能性があります。

そのため、4バイトのUnicode文字をサポートする照合順序を使用することを強く推奨します。

MySQLのデフォルト設定

MySQLバージョン	デフォルトの文字セット	デフォルトの照合順序	Unicode互換性
< 5.5	latin1	latin1_swedish_ci	いいえ
5.5.x	utf8	utf8_general_ci	いいえ
5.7+	utf8mb4	utf8mb4_general_ci / utf8mb4_unicode_ci	はい
8.0+	utf8mb4	utf8mb4_0900_ai_ci	はい

WordPressは必ずしもMySQLのデフォルトに従うとは限らず、設定や移行によって上書きされる場合があります。

新規サイトの文字セットと照合順序の設定

WordPressが正しい文字セットと照合順序で新しいテーブルを作成するようにするには、wp-config.phpで以下を定義します。

define( 'DB_CHARSET', 'utf8mb4' ); // If you have utf8 that's fine, WP will automatically map it as utf8mb4
define( 'DB_COLLATE', 'utf8mb4_unicode_ci' );

これは、新規インストールおよび新しく作成されたテーブルに適用されます。

既存のサイトとテーブルの文字セットと照合順序の確認

既存のサイトで文字セットと照合順序を確認する方法は以下のとおりです。

オプション1 phpMyAdmin

1. サイトのデータベースに移動します。
2. 各テーブルの横にある「照合順序（Collation）」列を確認します。
3. テーブルをクリックして、各列の個別の照合順序を表示します。

オプション2 WP-CLI

bash

wp db query "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE table_schema = 'your_db_name';"

個別の列を検査することもできます。

bash

wp db query "SHOW FULL COLUMNS FROM wp_posts;"

照合順序がutf8mb4_で始まっている場合、すべての種類のUnicodeおよび4バイトの表意文字をサポートする準備が整っています。文字セットも問題ありません。

それ以外のもの、例えばutf8_*などが表示されている場合は、変更する必要があります。

照合順序は列ごとに異なる場合があることに注意してください。

既存のテーブルと列の文字セットと照合順序の更新

wp-config.phpファイルのDB_CHARSETおよびDB_COLLATE定数を更新しても、新しいテーブルにしか影響しません。既存のテーブルや列に変更を適用するには、SQLを使用して手動で変換する必要があります。

また、以下のコマンドを使用して、データベースのデフォルトのMySQL照合順序を更新することをお勧めします。

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

常にバックアップから開始する

変換クエリを実行する前に、以下の手順を行ってください。

• phpMyAdminまたはmysqldumpを使用してデータベースをエクスポートします。
• 問題が発生した場合に備えて、安全に保管します。

警告：厳格モード（Strict Mode）では照合順序の更新が失敗する可能性があります

テーブル全体（またはデータベース）の照合順序を更新する場合、MySQLはテーブルを再構築し、DATETIMEなどの関連しない列も含め、すべての列のデフォルト値を再検証する可能性があります。データベースが厳格なSQLモード（STRICT_TRANS_TABLES、NO_ZERO_DATE）で実行されている場合、「0000-00-00 00:00:00」のようなデフォルト値を持つ列は、次のようなエラーを引き起こします。

#1067 - Invalid default value.

厳格モード（Strict Mode）とは

厳格モードは、データがデータベースに追加または更新される際に、MySQLが無効な値や欠落している値をどのように処理するかを制御します。値が無効になる理由はいくつかあります。例えば、列のデータ型が間違っていたり、範囲外であったりする場合があります。

厳格モードのさまざまな種類のデータ検証に関する情報は、MySQLのドキュメントで確認できます。

厳格モードがWordPressサイトのデータベースに与える影響

デフォルトでは、WordPressは一部のdatetime列のデフォルトとして「0000-00-00 00:00:00」を使用しますが、これはNO_ZERO_DATEの厳格モードでは受け入れられません。

これらのデフォルト値は、元のテーブル作成時（例：WordPressのセットアップ時）には許可されていましたが、厳格な環境でのALTER操作時には失敗することに注意してください。

厳格モードが有効かどうかを確認する方法

以下のコマンドを実行します。

SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

以下を探します。

• STRICT_TRANS_TABLES
• STRICT_ALL_TABLES
• NO_ZERO_DATE

これらのいずれかが見つかった場合、厳格モードが有効になっています。

文字セットと照合順序の更新中のエラーを回避するには、NO_ZERO_DATEの厳格モードを無効にするだけで済みます。

安全に進める方法

安全に進めるために、以下のいずれかを行ってください。

1. 照合順序を実際に変更する必要がない限り、列やテーブルの変更を避けます。

2. 変換クエリを実行している間、そのセッションのみ一時的に厳格モードを無効にします。これは以下のコマンドを使用して行うことができます。

SET SESSION sql_mode = REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

注：MySQLには厳格モードを統合する将来の計画があります。

データに特有のその他の問題が発生した場合は、以下のコマンドを使用してすべての厳格モードを無効にすることができます。

SET SESSION sql_mode = '';

データ変換用のクエリ

データベース全体の変換

以下は、データベース内の各テーブルに対してALTER TABLEクエリを生成します。

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS query
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'your_database_name';

生成されたクエリをphpMyAdminで実行するか、MySQL CLIまたはWP-CLIにコピーして貼り付けます。

これにより、データベース内のすべてのテーブルが新しい文字セットと照合順序を採用するようになります。

特定のテーブルの変換 オプション/付加的

ALTER TABLE wp_posts 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

これにより、wp_postsテーブルのすべてのテキスト列が更新され、utf8mb4と指定された照合順序が使用されるようになります。

---

特定の列の変換 オプション/付加的

ALTER TABLE wp_posts 
  CHANGE post_title post_title TEXT 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

変換時の一般的なエラー

テーブルや列を変換する際に発生する可能性のある2つの一般的なエラーを以下に示します。

文字セットと照合順序の不一致

-- INVALID: utf8 collation with utf8mb4 charset
ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;
-- Error: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

解決策：

• 一致する照合順序を使用します：utf8mb4_*

---

インデックスキーの長さ制限

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

一部の構成、特にMySQLバージョン5.7未満では、1文字あたり1バイト追加されると、インデックスキーの最大長を超える場合があります。

解決策：

• 必要に応じて、インデックス付けされたVARCHAR(255)をVARCHAR(191)に短縮します。
• または、MySQL 5.7以降にアップグレードし、innodb_large_prefixが有効になっていることを確認します。