MySQL データベースの照合と文字セットを修正する方法

照合とは

照合は、結果の並べ替え順を定義します。アトラシアン アプリケーションの新しいバージョンでは、アプリケーションで特定の照合を要求するため、照合の変更が厳密になる可能性があります。データベースの照合が、それを使用するアプリケーションに適切であることを確認する必要があります。

MySQL では、次のレベルで異なる照合セットがあるため、照合が複雑になる場合があります。

1. データベース レベル
2. テーブル レベル
3. カラム レベル

また、カラム内の情報が誤った方法でエンコードされ、それによってカラムのデータが誤って表示される場合もあります。

使用可能な照合

Jira と Confluence のすべてのバージョンが utf8mb4 (4 バイト文字をサポート) をサポートしているわけではありません。utf8 を使用する必要がある場合があります。

外部キー制約について

大量のカラムに変更を行う際に、外部キーの制約を無視する必要がある場合があります。SET FOREIGN_KEY_CHECKS コマンドを使用して、データベースを更新するときに外部キー制約を無視できます。

SET FOREIGN_KEY_CHECKS=0;
 
-- Insert your other SQL Queries here...
 
SET FOREIGN_KEY_CHECKS=1;

データベースの照合の変更

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

データベースの照合を変更するには、次のように実行します。

ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>

テーブルの照合の変更

次のクエリは一連の ALTER TABLE ステートメントを生成します。これをデータベースに対して実行する必要があります。 

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

テーブルの照合を変更するには、次のように実行します。

SELECT CONCAT('ALTER TABLE `',  table_name, '` CHARACTER SET <charset> COLLATE <collation>;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = '<yourDB>'
AND
(
    C.CHARACTER_SET_NAME != '<charset>'
    OR
    C.COLLATION_NAME != '<collation>'
);

カラムの照合の変更

次のクエリ (varchar カラム用に 1 つ、非 varchar カラム用に 1 つ) は一連の ALTER TABLE ステートメントを生成します。これをデータベースに対して実行する必要があります。 

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

varchar カラムのカラム照合を変更するには、次のように実行します。

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '<yourDB>'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != '<charset>'
    OR
    COLLATION_NAME != '<collation>'
);

非 varchar カラムの照合を変更するには、次のように実行します。

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '<yourDB>'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != '<charset>'
    OR
    COLLATION_NAME != '<collation>'
);

Confluence のサーバーでの文字セットの考慮事項

utf8mb4 を使用しているが、MySQL Server の my.cnf または my.ini ファイルで character_set_server が utf8mb4 に設定されておらず、これを変更できない場合 (例: utf8 は別のアプリケーションが使用するデータベースで必要)、utf8mb4 を使用するには、接続 URL に connectionCollation=utf8mb4_bin パラメーターを追加する必要があります。詳細については「Connector/J 8.0」または「Connector/J 5.1」をご確認ください。

<property name="hibernate.connection.url">jdbc:mysql://yourhost:3306/confluence?connectionCollation=utf8mb4_bin</property>

my.cnf ファイルで照合と文字セットが適切に定義されている (character-set-server = utf8mb4 collation-server = utf8mb4_bin を使用) ことを確認する必要がある場合があります。