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:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 116
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

2. Confirm the current defaults for the database.

   mysql> SHOW CREATE DATABASE `charset_demo`\G
   *************************** 1. row ***************************
          Database: charset_demo
   Create Database: CREATE DATABASE `charset_demo` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */
   1 row in set (0.00 sec)

3. List supported collations for the target character set.

   mysql> SELECT
       ->   collation_name AS Collation,
       ->   character_set_name AS Charset,
       ->   id AS Id,
       ->   is_default AS `Default`,
       ->   is_compiled AS Compiled,
       ->   sortlen AS Sortlen
       -> FROM information_schema.COLLATIONS
       -> WHERE character_set_name = 'utf8mb4'
       -> ORDER BY collation_name
       -> LIMIT 8;
   +-----------------------+---------+-----+---------+----------+---------+
   | Collation             | Charset | Id  | Default | Compiled | Sortlen |
   +-----------------------+---------+-----+---------+----------+---------+
   | utf8mb4_0900_ai_ci    | utf8mb4 | 255 | Yes     | Yes      |       0 |
   | utf8mb4_0900_as_ci    | utf8mb4 | 305 |         | Yes      |       0 |
   | utf8mb4_0900_as_cs    | utf8mb4 | 278 |         | Yes      |       0 |
   | utf8mb4_0900_bin      | utf8mb4 | 309 |         | Yes      |       1 |
   | utf8mb4_bg_0900_ai_ci | utf8mb4 | 318 |         | Yes      |       0 |
   | utf8mb4_bg_0900_as_cs | utf8mb4 | 319 |         | Yes      |       0 |
   | utf8mb4_bin           | utf8mb4 |  46 |         | Yes      |       1 |
   | utf8mb4_bs_0900_ai_ci | utf8mb4 | 316 |         | Yes      |       0 |
   +-----------------------+---------+-----+---------+----------+---------+
   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 `charset_demo`
       -> CHARACTER SET utf8mb4
       -> COLLATE utf8mb4_0900_ai_ci;
   Query OK, 1 row affected (0.00 sec)

   Replace charset_demo 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 = 'charset_demo';
   +--------------+---------+--------------------+
   | SCHEMA_NAME  | charset | collation          |
   +--------------+---------+--------------------+
   | charset_demo | utf8mb4 | utf8mb4_0900_ai_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;).