Generate alter table queries for charset and collation changes

mysql-charset-collation-change.sql

# Generate alter table queries for charset and collation changes - change $DBNAME$ / $CHARSET$ / $COLLATION$

# Tables
SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET $CHARSET$ COLLATE $COLLATION$;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = '$DBNAME$'
AND
(
    C.CHARACTER_SET_NAME != '$CHARSET$'
    OR
    C.COLLATION_NAME != '$COLLATION$'
);

# Columns
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET $CHARSET$ COLLATE $COLLATION$', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '$DBNAME$'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != '$CHARSET$'
    OR
    COLLATION_NAME != '$COLLATION$'
)

# -----------------------------------------------------------------------------

# Examples

# Tables
SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'recette_acteongroup_monkees_pro_jp'
AND
(
    C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
    C.COLLATION_NAME != 'utf8mb4_unicode_ci'
);

# Columns
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'recette_acteongroup_monkees_pro_jp'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_unicode_ci'
)