Changing column collation and character set in mysql database / schema

5
(1)

In the examples below, change: 

  • <yourDB> to your actual database name
  • <charset> to either utf8 or utf8mb4
  • <collation> to either utf8_bin or utf8mb4_bin

Notice Title

Please note, similar to the query above, the queries below (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. 

To create the ALTER SQLs, run the below SQL and use the ALTER SQLs in the resultset to update the collation for varchar 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 = '<yourDB>'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != '<charset>'
    OR
    COLLATION_NAME != '<collation>'
);

To create the ALTER SQLs, run the below SQL and use the ALTER SQLs in the resultset to update the collation for non-varchar columns:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '<yourDB>'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != '<charset>'
    OR
    COLLATION_NAME != '<collation>'
);

You might also need to make sure that your collation and character set are properly defined at your my.cnf file (e.g. using character-set-server = utf8mb4 collation-server = utf8mb4_bin)

you can also export these requests to a file for convenience, for example:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '<yourDB>'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != '<charset>'
    OR
    COLLATION_NAME != '<collation>'
)
INTO OUTFILE '/tmp/modify-columns.sql'

Similar Posts:

457

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top