MySQL MariaDB utf8 to utf8mb4

Monday, December 25, 2017

less than a minute

Change server settings

Refer: https://yottabrain.org/blog/database/mysql/mysql-mariadb-set-utf8mb4-as-default-charset/

on AWS RDS a parameter group needs to be created to apply above settings: refer: http://aprogrammers.blogspot.in/2014/12/utf8mb4-character-set-in-amazon-rds.html

Run this SQL for each database:

ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Reduce the length of indexed columns with utf8mb4 from 256 to 190 because index with 4byte utf8mb4 have issues

ALTER TABLE mydb.mytable MODIFY COLUMN mycolumn varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Run this SQL for each table:

ALTER TABLE mydb.mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Run this SQL for the columns which needs to be stored as utf8mb4:

ALTER TABLE mydb.mytable MODIFY COLUMN mycolumn varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Run the following command to avoid any issues after migration

mysqlcheck -u root -p --auto-repair --optimize --all-databases