Batch change the character set and collation rules of MySQL databases, tables, and fields.

MySQL 8.0 has been available for quite some time now. Recently, there is a plan to upgrade from MySQL 5.7.39 to MySQL 8.0.32. Due to the chaotic character sets and sorting rules used in the original data, and the fact that the default engine and character set in MySQL 8.0 have been changed to InnoDB and utf8mb4, respectively, this is a good opportunity to unify the character sets and sorting rules used in the database.

Originally, manual modification was planned, but it would be a considerable amount of work to modify hundreds of tables. So, the following methods were found through the internet:

1. Change the default character set and sorting rules of the database#

-- ALTER DATABASE `<database_name>` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

SELECT
    CONCAT('ALTER DATABASE `', SCHEMA_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS `SQL`
FROM
    information_schema.`SCHEMATA` 
WHERE
    -- Exclude built-in MySQL databases, you can modify it to: SCHEMA_NAME IN ('database1', 'database2', 'database3')
    SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
;

2. Change the storage engine, default character set, and sorting rules of the tables#

-- ALTER TABLE `<database_name>`.`<table_name>` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;

SELECT
    CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;') AS `SQL`
FROM
    information_schema.`TABLES`
WHERE
    -- Exclude built-in MySQL databases, you can modify it to: TABLE_SCHEMA IN ('database1', 'database2', 'database3')
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (ENGINE != 'InnoDB' OR TABLE_COLLATION != 'utf8mb4_unicode_ci')
;

3. Change the character set and sorting rules of the columns#

-- ALTER TABLE `<database_name>`.`<table_name>` 
-- MODIFY COLUMN `<column_name>` <column_type> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '<comment>';

SELECT 
    CONCAT(
        -- Specified database and table name
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
        -- Specified column and data type
        'MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
        -- Specified character set and corresponding sorting rules
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ',
        -- Combination of non-nullability and default value
        (CASE
            WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL THEN 'NOT NULL '
            WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NOT NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
            WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NULL THEN 'NULL DEFAULT NULL '
            WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
        END),
        -- Preserve the original comment information
        'COMMENT \'', COLUMN_COMMENT, '\';'
    ) AS `SQL`
FROM
    information_schema.`COLUMNS`
WHERE
    -- Exclude built-in MySQL databases, you can modify it to: TABLE_SCHEMA IN ('database1', 'database2', 'database3')
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci')
    AND DATA_TYPE IN ('varchar', 'char', 'text')
;

Note: If you need to modify the production environment database, please execute the SQL statements during off-peak hours. If there are foreign keys involved in the tables, you can temporarily disable foreign key constraints using SET FOREIGN_KEY_CHECKS=0;, and then enable them again using SET FOREIGN_KEY_CHECKS=1; after execution.

【MySQL】Batch Change Character Set of Databases, Tables, and Columns - emdzz - 博客园
 Batch Modify Character Set in MySQL - 简书