This document is part of the guide on How to Fix the Collation and Character Set of a MySQL Database. Please refer to that page for more information.

目次

• MySQL Collation Repair: Database Level Changes
• MySQL Collation Repair: Table Level Changes
• MySQL Collation Repair: Column Level Changes
• MySQL Collation Repair: Column Level Encoding Issues
• MySQL Collation Repair: Case Study - Repairing a Production Database

使用可能な照合

Not all versions of Confluence support utf8mb4 (which provides support for 4-btye characters). You may need to use utf8.

Before Proceeding

Identifying the Collation and Character set of your database

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

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

次のクエリを実行します。

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S
WHERE schema_name = '<yourDB>'
AND 
(
	DEFAULT_CHARACTER_SET_NAME != '<charset>'
	OR
	DEFAULT_COLLATION_NAME != '<collation>'
);

This query will return a list of each database that is not using the correct character set and collation.

Fixing the collation for the database

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

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

次のクエリを実行します。

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

例:

ALTER DATABASE confluence CHARACTER SET utf8mb4 COLLATE utf8mb4_bin