MySQL MariaDB utf8 to utf8mb4

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

Change database

Run this SQL for each database:

ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Change size of indexed columns

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;

Change Tables

Run this SQL for each table:

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

Change Columns

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;

Repair and Optimize

Run the following command to avoid any issues after migration

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

References

https://mathiasbynens.be/notes/mysql-utf8mb4

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system