Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
# For each database:
ALTER DATABASE century21_development CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "century21_development"
# For each column:
select CONCAT('ALTER TABLE `', TABLE_SCHEMA, '.', TABLE_NAME,'` CHANGE ',COLUMN_NAME,' ', COLUMN_NAME, ' ', DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') as column_alter from INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN('varchar', 'text') AND TABLE_SCHEMA = 'century21_development'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.