MySQL: Convert Character Set and Collation

The character set and collation changes for a DB have important changes on the storage requirements and query result behavior. The character set for a table can be changed in two ways:
a. Using CONVERT TO query
b. Using MODIFY <column name>

Methods

The CONVERT TO method makes sure that each column fits the new character set range after conversion. So a column type TEXT of character set Latin would not accommodate the character set of UTF8MB4. Since the latter needs 4 bytes for a character, while in Latin one byte is required for a character.

So CONVERT TO rounds of the column type to the nearest next size.

The next method of MODIFY does not make any change to the column type.

Conclusion

I prefer the first method because it’s assuring that the table column sizes are technically large enough and also it eliminates any manual changes for a column.

Examples

ALTER DATABASE <db name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `test_table` MODIFY `column1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

ALTER TABLE `test_table` MODIFY `column2` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: