MySQL ALTER DATABASE: Change Character Set and Options

ALTER DATABASE changes the default properties of a database. You cannot rename a database with it (see Copy and Rename a Database).

Change the default character set and collation

query.sql

SQL

CREATE DATABASE shop CHARACTER SET latin1;
SELECT default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = 'shop';
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SELECT default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = 'shop';

Output

OUTPUT

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+
1 row in set

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+
1 row in set

What it does not change

The new defaults apply only to tables created afterwards. Existing tables keep their character set:

query.sql

SQL

CREATE DATABASE legacy CHARACTER SET latin1;
CREATE TABLE legacy.notes (body VARCHAR(50));
ALTER DATABASE legacy CHARACTER SET utf8mb4;
CREATE TABLE legacy.new_notes (body VARCHAR(50));
SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'legacy' ORDER BY table_name;

Output

OUTPUT

+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| new_notes  | utf8mb4_0900_ai_ci |
| notes      | latin1_swedish_ci  |
+------------+--------------------+
2 rows in set

notes is still latin1, and new_notes is utf8mb4. To convert an existing table, use ALTER TABLE ... CONVERT TO CHARACTER SET (Character Sets and Collations). Always test that on a copy first, because it rewrites the table.

Read-only databases (MySQL 8.0.22 and later)

You can make a database read-only. Data and structure cannot change, but selects still work:

query.sql

SQL

CREATE DATABASE archive_2025;
CREATE TABLE archive_2025.reports (id INT);
INSERT INTO archive_2025.reports VALUES (1);
ALTER DATABASE archive_2025 READ ONLY = 1;
INSERT INTO archive_2025.reports VALUES (2);

Output

OUTPUT

ERROR 3989 (HY000): Schema 'archive_2025' is in read only mode.

Turn it off again with READ ONLY = 0. A read-only flag is a neat way to freeze an old database before it is retired. It is MySQL-specific.

Encryption default

DEFAULT ENCRYPTION = 'Y' makes new tables in that database encrypted at rest, when the server has a keyring configured. See the MySQL manual’s InnoDB data-at-rest encryption section. It is outside the scope of an introductory course.

lightbulb

Prefer migration scripts

Changing defaults on a live system is easy to forget. Put the ALTER DATABASE statement in your migration or deployment script, so it is recorded and repeatable.