How to Change Character Set from latin1 to UTF8 in MySQL

By default MySQL databases have latin1 character set and collation. However, sometimes you may need to store UTF8 characters in MySQL database. Here’s how to change character set from latin1 to UTF8.

How to convert MySQL database from latin1 to UTF8

Here are the steps to change character set from latin1 to UTF for MySQL database.

1. Determine current character set

Log into MySQL command line tool. You will see a password prompt. Enter your password to log into MySQL database.

$ sudo mysql -uroot -p

Run the following command to determine the present character set of your database. Replace database_name below with your database name

mysql> SELECT default_character_set_name 
       FROM information_schema.SCHEMATA S 
       WHERE schema_name = "database_name";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1                     |
+----------------------------+

If you want to determine the character set for a specific database table, run the following command. Replace database_name and table_name with your database and table names respectively.

mysql> SELECT CCSA.character_set_name 
       FROM information_schema.TABLES T,
       information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA 
       WHERE CCSA.collation_name = T.table_collation 
       AND T.table_schema = "database_name" 
       AND T.table_name = "table_name";

Bonus Read : How to Increase Max Connections in MySQL

2. Change Character Set from latin1 to UTF8

Run the following command to change character set of MySQL database from latin1 to UTF8. Replace database_name with your database name

For MySQL > 5.5

mysql> ALTER DATABASE database_name CHARACTER 
       SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For MySQL <= 5.5

mysql> ALTER DATABASE database_name CHARACTER 
       SET utf8 COLLATE utf8_unicode_ci;

Bonus Read : How to Rank over Partition in MySQL

Similarly, here’s the command to change character set of MySQL table from latin1 to UTF8. Replace table_name with your database table name.

For MySQL > 5.5

mysql> ALTER TABLE table_name CONVERT TO CHARACTER 
       SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For MySQL <= 5.5

mysql> ALTER TABLE table_name CONVERT TO CHARACTER 
       SET utf8 COLLATE utf8_unicode_ci;

Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).