How to view database character set and collation in MySQL or MariaDB

Checking a database’s default character set and collation prevents subtle text problems like broken emoji storage, unexpected sort order, and comparisons that behave differently across environments.

In MySQL and MariaDB, the server has global defaults, but each database (schema) can define its own default character set and collation. New tables and columns inherit these database defaults unless a table/column explicitly sets its own character set or collation.

The values shown here describe the database defaults only, not necessarily what existing tables or columns currently use. Results can be filtered by privileges (for example, schemas may be hidden without SHOW DATABASES), and output may contain version comments like /*!40100 ... */ that can be ignored. If utf8mb3 appears, it represents legacy 3-byte UTF-8 behavior, while utf8mb4 supports full Unicode.

1. Connect to your MySQL or MariaDB server.

   $ mysql --user=root --password
   Enter password:
   
   mysql>

2. Show default character set and collation for a specific database.

   mysql> SHOW CREATE DATABASE appdb\G
   *************************** 1. row ***************************
          Database: appdb
   Create Database: CREATE DATABASE `appdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
   1 row in set (0.00 sec)

   Replace appdb with your database name, and use backticks around names with dashes or spaces.

3. List defaults for all databases using information_schema.

   mysql> SELECT
       schema_name AS database_name,
       default_character_set_name AS charset,
       default_collation_name AS collation
     FROM information_schema.schemata
     ORDER BY schema_name;
   +--------------------+---------+--------------------+
   | database_name       | charset  | collation           |
   +--------------------+---------+--------------------+
   | analytics           | utf8mb4  | utf8mb4_unicode_ci  |
   | appdb               | utf8mb4  | utf8mb4_unicode_ci  |
   | information_schema  | utf8mb3  | utf8mb3_general_ci  |
   ##### snipped #####
   +--------------------+---------+--------------------+

   Rows are commonly filtered by privileges, so the list may not include every schema on the server.

4. Select a database for a current-database check.

   mysql> USE appdb;
   Database changed

5. Show the defaults for the currently selected database.

   mysql> SELECT
       schema_name AS database_name,
       default_character_set_name AS charset,
       default_collation_name AS collation
     FROM information_schema.schemata
     WHERE schema_name = DATABASE();
   +--------------+---------+--------------------+
   | database_name | charset  | collation           |
   +--------------+---------+--------------------+
   | appdb         | utf8mb4  | utf8mb4_unicode_ci  |
   +--------------+---------+--------------------+
   1 row in set (0.00 sec)

   DATABASE() returns the active schema name selected by USE.

6. Verify the active database defaults via server variables.

   mysql> SELECT
       @@character_set_database AS charset,
       @@collation_database AS collation;
   +---------+--------------------+
   | charset  | collation           |
   +---------+--------------------+
   | utf8mb4  | utf8mb4_unicode_ci  |
   +---------+--------------------+
   1 row in set (0.00 sec)