Home / Database Structure / Question No: 199

The ALTER DATABASE statement changes options for an existing database. The allowable options are the same as for CREATE DATABASE; that is, CHARACTER SET and COLLATE. The following statement changes the default collation of the db_name database to utf8_polish_ci:

ALTER DATABASE db_name COLLATE utf8_polish_ci;

This statement changes both the default character set and collation:

ALTER DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Changing the default character set or collation affects only creation of new tables in the database. It does not affect existing tables. The database name is optional for ALTER DATABASE. If no database is named, the statement changes the options for the default database. This requires that there be a currently selected database. Otherwise, an error occurs.

You cannot use ALTER DATABASE to rename a database. One way to accomplish this is to dump the database, create a database with the new name, reload the data into the new database, and drop the old database.

When you no longer need a database, you can remove it with DROP DATABASE:

DROP DATABASE db_name;

It is an error if the database does not exist. To cause a warning instead, include an IF EXISTS clause:

DROP DATABASE IF EXISTS db_name;

Any warning generated when IF EXISTS is used can be displayed with SHOW WARNINGS.  DROP DATABASE does not require the database to be empty. Before dropping the database, MySQL removes any objects that it contains, such as tables, stored routines, and triggers.

DROP DATABASE is a dangerous statement and you should use it with care. There is no statement to "undo" DROP DATABASE. If you drop a database by mistake, your only option is to recover the database and its contents from your backups.