How to change collation of database, table, column?

The database is latin1_general_ci now and I want to change collation to utf8mb4_general_ci.

Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

Solution 1:

I am contributing here, as the OP asked:

How to change collation of database, table, column?

The selected answer just states it on table level.

Changing it database wide:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Changing it per table:

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

Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.

Changing collation for a specific column:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Solution 2:

You need to either convert each table individually:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8mb4.