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.

Table of Contents

• 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

Which collation can you use?

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

Before Proceeding

Identifying Columns with the incorrect character set or collation:

In the example below, change: 

• <yourDB> to your actual database name
• <charset> to either utf8 or utf8mb4
• <collation> to either utf8_bin or utf8mb4_bin

Run the following query:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '<yourDB>' 
AND 
(
	CHARACTER_SET_NAME != '<charset>'
	OR
	COLLATION_NAME != '<collation>'
);

Adjusting the collation and character set of varchar columns

To adjust all varchar columns in a database, you'll need to first identify those columns. The following script will generate an alter table statement for each column that is varchar and is using the incorrect collation or character set. 

In the example below, change: 

• <yourDB> to your actual database name
• <charset> to either utf8 or utf8mb4
• <collation> to either utf8_bin or utf8mb4_bin

Run the following query:

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>'
);

Adjusting the collation and character set of non varchar columns

To adjust the non-varchar columns in the database to use the correct collation, you'll need to first identify those columns. The following script will generate an alter table statement for each column that is not varchar and is using the incorrect collation or character set. 

In the example below, change: 

• <yourDB> to your actual database name
• <charset> to either utf8 or utf8mb4
• <collation> to either utf8_bin or utf8mb4_bin

Run the following query:

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>'
);

Dealing with Foreign Key Constraints

It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.

SET FOREIGN_KEY_CHECKS=0;

-- Insert your other SQL Queries here...

SET FOREIGN_KEY_CHECKS=1;