How to convert an MySQL database characterset and collation to UTF-8?

Firstly, we will check which MySQL version is currently being used with the help of version() function −

The query is as follows −

mysql> SELECT version();

The following is the output

+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

As you can see in the above output, version 8.0.12 is being used. Now, we can check the current character encoding using the following syntax −

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 = "yourDatabaseName" AND T.table_name =
"yourTableName";

Apply the above query −

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 = "sample" AND T.table_name = "mytable";

The following is the output:

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)

Here is the query by which we can set utf8 for a database −

mysql> ALTER DATABASE sample CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.27 sec)

Let us check −

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 = "sample" AND T.table_name = "mytable";

The following is the output

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)