View all databases charset and collation:
SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
View specified database charset and collation:
USE db_name;
SELECT @@character_set_database, @@collation_database;
View specified table charset and collation:
SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
OR - will output the complete SQL for create table:
SHOW CREATE TABLE my_tablename
View full columns collation:
SHOW FULL COLUMNS FROM my_tablename;
Change specified database charset and collation to utf8mb4
and utf8mb4_unicode_ci
respectively:
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Change specified table charset and collation to utf8mb4
and utf8mb4_unicode_ci
respectively:
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
References