7.2. Creating Databases

To create a new database, use the CREATE DATABASE statement. The following statement creates a database named mydb:

 CREATE DATABASE mydb; 

If you try to create a database that already exists, an error occurs. If you simply want to ensure that the database exists, add an IF NOT EXISTS clause to the statement:

 CREATE DATABASE IF NOT EXISTS mydb; 

With the additional clause, the statement creates the database only if it does not already exist. Otherwise, the statement does nothing and no error occurs. This can be useful in applications that need to ensure that a given database is available, without disrupting any existing database with the same name.

The CREATE DATABASE statement has two optional clauses, CHARACTER SET and COLLATE, that assign a default character set and collation for the database. If given, they appear at the end of the statement following the database name. The following statement specifies that the mydb database has a default character set of utf8 and collation of utf8_danish_ci:

 CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_danish_ci; 

The default character set and collation for the database are used as the defaults for tables created in the database for which no explicit character set or collation of their own are specified. The database defaults are stored in the db.opt file in the database directory.

Creating a database has no effect on which database currently is selected as the default database. To make the new database the default database, issue a USE statement:

 USE mydb; 

After a database has been created, you can populate it with objects such as tables or stored routines. The CREATE statements for these objects are discussed in later chapters.