How to change database default character set and collation in MySQL or MariaDB

Correct database defaults prevent silent drift in how new tables store, compare, and sort text, especially when multiple applications or migrations create objects over time. A consistent default (commonly utf8mb4) reduces “mystery mojibake” and keeps modern Unicode (including emoji) working in newly created schema objects.

In MySQL and MariaDB, the character set defines how text is encoded, while the collation defines comparison and sort rules. Defaults exist at the server, database, and table levels; when a CREATE statement omits an explicit character set or collation, the database defaults are inherited and recorded in the table metadata.

ALTER DATABASE changes only the database-level defaults and does not rewrite existing tables or columns. Collation names and availability vary between MySQL and MariaDB versions, so validating the target collation first avoids errors, and planning a separate conversion is required when existing data must be migrated to the new encoding.

1. Connect to your MySQL or MariaDB server.

   $ mysql -u root -p
   Enter password:
   
   mysql>

2. Confirm the current defaults for the database.

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

3. List supported collations for the target character set.

   mysql> SHOW COLLATION WHERE Charset = 'utf8mb4' LIMIT 8;
   +----------------------------+---------+-----+---------+----------+---------+
   | Collation                  | Charset | Id  | Default | Compiled | Sortlen |
   +----------------------------+---------+-----+---------+----------+---------+
   | utf8mb4_general_ci         | utf8mb4 | 45  |         | Yes      | 1       |
   | utf8mb4_bin                | utf8mb4 | 46  |         | Yes      | 1       |
   | utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      | 8       |
   ##### snipped #####
   8 rows in set (0.00 sec)

   Collation lists differ by server version and vendor; select a COLLATE value that appears in the output.

4. Change the database defaults using ALTER DATABASE.

   mysql> ALTER DATABASE `appdb`
       -> CHARACTER SET utf8mb4
       -> COLLATE utf8mb4_unicode_ci;
   Query OK, 1 row affected (0.01 sec)

   Replace appdb and the collation values for the environment; an unsupported collation returns ERROR 1273 (HY000).

5. Verify the updated defaults.

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

   Existing tables and columns keep their current character set and collation unless converted explicitly (for example: ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;).