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;