View or change charset and collation of database and table in sql

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;

Create database by specifying charset and collation:

CREATE DATABASE databasename
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

References

poudelmadhav/charset-and-collation.md