# ALTER DATABASE

## Syntax

```sql
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'comment'
```

## Description

`ALTER DATABASE` enables you to change the overall characteristics of a database. These characteristics are stored in the `db.opt` file in the database directory. To use `ALTER DATABASE`, you need the `ALTER` privilege on the database. `ALTER SCHEMA` is a synonym for `ALTER DATABASE`.

The `CHARACTER SET` clause changes the default database character set. The `COLLATE` clause changes the default database collation. See [Character Sets and Collations](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) for more.

You can see what character sets and collations are available using, respectively, the [SHOW CHARACTER SET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-character-set) and [SHOW COLLATION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-collation) statements.

Changing the default character set/collation of a database does not change the character set/collation of any [stored procedures](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures) or [stored functions](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions) that were previously created, and relied on the defaults. These need to be dropped and recreated in order to apply the character set/collation changes.

The database name can be omitted from the first syntax, in which case the statement applies to the default database.

The syntax that includes the `UPGRADE DATA DIRECTORY NAME` clause. It updates the name of the directory associated with the database to use the encoding implemented in MySQL for mapping database names to database directory names (see [Identifier to File Name Mapping](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-to-file-name-mapping)). This clause is for use under these conditions:

* It is intended when upgrading MySQL from older versions.
* It is intended to update a database directory name to the current encoding format if the name contains special characters that need encoding.
* The statement is used by [mariadb-check](https://mariadb.com/docs/server/clients-and-utilities/table-tools/mariadb-check) (as invoked by [mariadb-upgrade](https://mariadb.com/docs/server/clients-and-utilities/deployment-tools/mariadb-upgrade)).

For example, if a database in MySQL 5.0 has a name of `a-b-c`, the name contains instance of the \`-' character. In 5.0, the database directory is also named `a-b-c`, which is not necessarily safe for all file systems. In MySQL, the same database name is encoded as `a@002db@002dc` to produce a file-system-neutral directory name.

When a MySQL installation is upgraded from an older version, the server displays a name such as `a-b-c` (which is in the old format) as `#mysql50#a-b-c`, and you must refer to the name using the `#mysql50#` prefix. Use `UPGRADE DATA DIRECTORY NAME` in this case to explicitly tell the server to re-encode the database directory name to the current encoding format:

```sql
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
```

After executing this statement, you can refer to the database as a-b-c without the special `#mysql50` prefix.

{% hint style="info" %}
**MariaDB starting with** [**10.5.0**](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.0)

From [MariaDB 10.5.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.0), it is possible to add a comment of a maximum of 1024 bytes. If the comment length exceeds this length, a error/warning code 4144 is thrown. The database comment is also added to the db.opt file, as well as to the [information\_schema.schemata table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-schemata-table).
{% endhint %}

## Examples

{% tabs %}
{% tab title="Current" %}

```sql
ALTER DATABASE p COMMENT='Presentations';
```

{% endtab %}

{% tab title="< 10.5" %}

```sql
ALTER DATABASE test CHARACTER SET='utf8'  COLLATE='utf8_bin';
```

{% endtab %}
{% endtabs %}

## See Also

* [CREATE DATABASE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-database)
* [DROP DATABASE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-database)
* [SHOW CREATE DATABASE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-database)
* [SHOW DATABASES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-databases)
* [Character Sets and Collations](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/supported-character-sets-and-collations)
* [Information Schema SCHEMATA Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-schemata-table)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.