MySQL ALTER DATABASE Statement

---

---

MySQL Alter Database Statement

The ALTER DATABASE Statement of MySQL allows you to modify/change the characteristics of an existing database.

Syntax

Following is the syntax of the ALTER DATABASE statement −

ALTER DATABASE [database_name] alter_option ...

Where, database_name is the name of the database you need to change and this statement provides four options −

• CHARACTER SET − This option allows you to change the default character set of the database.

• COLLATE − This option allows you to change the collation of the database.

• ENCRYPTION − This option allows you enable (Y) or disable (N) the default database encryption.

• ReadOnly − Using this option you can allow modifications on the database along with the objects within (0) or make it read only (1).

Example

Suppose we have created a database as shown below −

CREATE DATABASE myDatabase;

Following query changes the character set of the above create database −

ALTER DATABASE myDatabase CHARACTER SET= ascii;

You can see the list of all the available character sets using the SHOW CHARACTER SET Statement

Altering the COLLATION

Similarly, following query changes the collation of the database named mydatabase −

ALTER DATABASE mydatabase COLLATE utf8_general_ci;

You can see the list of all the available collations using the SHOW COLLATION Statement.

Making the database ReadOnly

You can set mydatabase read-only using the following query −

ALTER DATABASE mydatabase READ ONLY = 1;

All Options in one query

You can use all the options in one query as follows −

ALTER DATABASE mydatabase CHARACTER SET utf8 COLLATE 
utf8_general_ci ENCRYPTION = 'Y' READ ONLY = 1;

You can verify the characteristics of the created database as shown below −

SHOW CREATE DATABASE mydatabase;

Output

Following is the output of the above query −

Database	Create Database
mydatabase	CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='Y' */ /* READ ONLY = 1 */