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 CHARACTERSET 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 CHARACTERSET utf8 COLLATE utf8_unicode_ci;
Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).
