Alter charset and collation in database and all tables in MySQL

There are various reasons why you might need to change the character set and collation of MySQL databases and tables. For instance, if you are developing a web application that handles multilingual data, you may need to configure the character set and collation based on the language and sorting order of the data.

Below, we will guide you through the steps to bulk change the character set and collation of MySQL databases and tables.

Step 1: Backup Databases and Tables

To prevent data loss, back up all databases and tables before making changes. This allows you to revert to the original state at any time.

Step 2: Change the Character Set and Collation of Databases

To change the character set and collation of a database, use the following query. The SQL example used here is based on utf8mb4 character set with utf8mb4_0900_ai_ci collation, which is beneficial for storing multilingual data and supports various full-text search methods in MySQL 8.0 and later. If you are using a database version that does not support utf8mb4_0900_ai_ci, use utf8mb4_unicode_ci.

ALTER DATABASE `database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Replace database_name with the name of the database you want to change.

Step 3: Change the Character Set and Collation of Tables

If you want to use different character sets and collations for each table, you need to use the following SQL for each table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

However, if you have a large number of tables, it can be cumbersome to perform this manually. You can generate and execute SQL statements for each table using the following SQL query:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS sql_statements FROM information_schema.tables
WHERE table_schema = 'database_name' AND table_type = 'BASE TABLE';

After running the above SQL query, execute all the generated ALTER TABLE SQL statements.

Step 4: Verify the Changes

To confirm that the character set and collation of databases and tables have been successfully changed, you can use the following queries to check the changes:

SHOW CREATE DATABASE database_name;
SHOW CREATE TABLE table_name;