Convert whole database, tables and attributes to specific charset and collation

Published on August 5, 2020 5:29:40AM by Administrator

In this article we show you how to convert charset and collation of specific database including its tables and attributes.


Convert whole database, tables and attributes to specific charset and collation

First of all, we convert the database collation by using the below command.

For example, we converting a database named 'database_name' using character set utf8mb4 and its collate is utf8mb4_unicode_ci

 ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then we run a query to list out all the necessary command to be execute to convert all the tables inside the database.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME,' COLLATE utf8mb4_unicode_ci;') AS 'query_collation_table' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' AND TABLE_TYPE='BASE TABLE';

Then we will run a query to list out all the necessary command to be execute to convert all the attributes for each table inside the database.

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS 'query_collation_column' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "database_name" AND TABLE_TYPE='BASE TABLE';


Make sure that we always back up the original database first before making any changes and set the foreign_key_checks to 0 first.

SET foreign_key_checks=0;