MySQL MariaDB utf8 to utf8mb4

Dec 25, 2017 / Updated on Dec 25, 2017

Change server settings

Refer: http://www.techiewiki.org/wiki/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

comments powered by Disqus
Categories:
Tags: