Changing CHARACTER SET and COLLATE in all columns in a MySQL table
1 min readApr 29, 2024
I would like to change all columns in a MySQL db table to CHARACTER SET = utf8mb4
and COLLATE = utf8mb4_unicode_ci
. How do I do it?
- Select those columns whose character set is not uft8mb4 or whose collate is not utf8mb4_unicode_ci, coming up with executable SQL command texts
SELECT CONCAT(
'ALTER TABLE foo_table.', table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;',
'ALTER TABLE foo_table.', table_name, ' CONVERT TO 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 = 'foo_table'
AND NOT (C.CHARACTER_SET_NAME = 'utf8mb4' AND C.COLLATION_NAME like 'utf8mb4_unicode_ci')
2. Execute the SQL commands from the result of step 1 to convert the character set and collate.
set foreign_key_checks=0; -- disable the constraint for the operation in case it affects the operation
ALTER TABLE foo_table.bar_column_1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE foo_table.bar_column_2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- ...may be more columns
set foreign_key_checks=1; -- enable foreign key constraint again
That’s it.