Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

Question

I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER statements for every table and column that needs to change?

I'm upgrading MySQL hosted on Amazon RDS from MySQL 5.7 to 8.0. The Pre-patch compatibility tool tells me:

The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

More Information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

It then lists about 6,000 databases and columns that need to be updated:

mydb - schema's default character set: utf8
mydb.mytable.mycolumn - column's default character set: utf8

I'd like to be able to generate all the ALTER statements I'll need to run similar to how MySQL command querying all MyISAM database helped my convert all my tables from MyISAM to Innodb.

Answer 1

Here is a query what will generate all the necessary update statements.

• It temporarily disables foreign key checks so that the update statements succeed.
• It updates the default character set for each database that needs it.
• It updates the default character set of each table:
  • If it doesn't have the right default character set.
  • If it contains text columns that are not the right character set.
  • The same ALTER TABLE ... CONVERT statement both updates the table default and all the columns within the table.

---

SELECT
    /* Disable foreign key checks temporily to be able to make these changes */
    'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement 
UNION SELECT 
    /* Alter the default character set of each database */
    CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM 
    information_schema.SCHEMATA 
WHERE 
    DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND 
    SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')
UNION SELECT
    /* Alter the default character set of each table .
      This also converts all text columns in the table,
      So there is no need to have a statement to alter each
      column individually */
    DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM
(
    SELECT
        /* Find all tables with a text column that isn't utf8mb4 */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.COLUMNS 
    WHERE
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND 
        CHARACTER_SET_NAME IS NOT NULL AND 
        CHARACTER_SET_NAME!='utf8mb4'
    UNION SELECT
        /* Also find all tables that don't have the correct default character set */
        TABLE_SCHEMA, TABLE_NAME
    FROM 
        information_schema.TABLES AS T
    JOIN 
        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation
    WHERE 
        CHARACTER_SET_NAME!='utf8mb4' AND 
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys')
) AS TABLE_UPDATES
UNION SELECT
    /* Re-enable forign key checks */
    'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement 
;

When run, it should generate output like:

SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `mydb`.`mytable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;

Sources:

• MySQL command querying all MyISAM database -- For how to query the information_schema to create UPDATE statements.
• How to change collation of database, table, column? - Stack Overflow -- For the update statements.
• How to convert an entire MySQL database characterset and collation to UTF-8? - Stack Overflow -- For the comment about disabling foreign key checks to be able to make these changes.
• How do I see what character set a MySQL database / table / column is? - Stack Overflow -- For how to join TABLES and COLLATION_CHARACTER_SET_APPLICABILITY

Answer 2

Checklist for UTF8MB4 Conversion:

1. Space should be available
2. Foriegn_key_check should be disabled
3. Need to convert Database
4. Need to convert Routines
5. Need to convert Triggers
6. Need to convert Tables

Note: Bulky tables should be converted usingLOAD DATA ... INTO table syntax

SAMPLE SYNTAX

1. For the database:

   ALTER DATABASE  database123 CHARACTER SET utf8MB4 ; 
   

2. For the routines:

   mysqldump --single-transaction  --set-gtid-purged=OFF   --force --routines  -h 192.168.X.X --no-data --skip-comments --skip-triggers --no-create-db --no-create-info  -u user -p --databases database --lock-tables=false > /backup/database_proc.sql
   

   Open the SQL file in vi & perform below commands.

   :%s/SET character_set_client  = latin1/SET character_set_client  = utf8mb4/
   :%s/SET character_set_results = latin1/SET character_set_results = utf8mb4/
   :%s/SET collation_connection  = latin1_swedish_ci/SET collation_connection  = utf8mb4_general_ci/
   :%s/latin1/utf8mb4/
   :wq
   

   then

   mysql -uuser -p -h192.168.X.X < proc.sql 
   

3. For the triggers:

   mysqldump --single-transaction  --set-gtid-purged=OFF   --force -h 192.168.X.X --no-data --skip-comments --triggers --add-drop-trigger --no-create-db --no-create-info  -u user -p --all-databases --lock-tables=false > /backup/trig.sql
   
   :%s/SET character_set_client  = latin1/SET character_set_client  = utf8mb4/
   :%s/SET character_set_results = latin1/SET character_set_results = utf8mb4/
   :%s/SET collation_connection  = latin1_swedish_ci/SET collation_connection  = utf8mb4_general_ci/
   :wq
   
   mysql -uuser -p -h192.168.X.X < /backup/trig.sql
   

4. For the tables:

   ALTER TABLE Inventory.PINCODE_MAST CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
   SELECT  * FROM  database.table_21 INTO OUTFILE  '/backup1/csv/database_2.csv' CHARACTER SET utf8mb4 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'    ;
   LOAD  DATA INFILE '/backup1/csv/database_2.csv' INTO TABLE database.table_21 CHARACTER SET utf8mb4 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;
   

5. Queries to verify object are converted or not:

   select table_schema,table_name ,table_collation from information_schema.tables where table_schema not like '%latin%' and table_schema not in ('sys','mysql','performance_schema','information_schema') and TABLE_COLLATION<>'utf8mb4_0900_ai_ci';
   select * from information_schema.routines where routine_schema not like '%latin%' and routine_schema not in ('sys','mysql','performance_schema','information_schema') and COLLATION_NAME <>'utf8mb4_0900_ai_ci';
   select * from  information_schema.triggers where trigger_schema not like '%latin%' and trigger_schema not in ('sys','mysql','performance_schema','information_schema') and COLLATION_CONNECTION <>'utf8mb4_0900_ai_ci';