Creating a New Database

You use the CREATE DATABASE command to create a new database. To create a new database named newdb, issue the following command. The output will be as shown if the database is created successfully.

 mysql> CREATE DATABASE newdb; Query OK, 1 row affected (0.02 sec) 

Creating Database

Usually you execute this command when you are connected as the root user, but you can create databases when logged in as any user who has the CREATE privilege. You will learn more about the privilege system in MySQL in Lesson 18, "Managing User Access."

The database name can be up to 64 characters in length and can contain any character that is allowed in a directory name on your underlying operating system, except for the / and \ characters. The database name also cannot be one of the SQL reserved words listed in Appendix F, "MySQL Reserved Words," which can be found on the book's website at www.samspublishing.com.

The database name must be unique as well. If you attempt to issue a CREATE DATABASE statement for a database that already exists, you will see an error. The following output is produced when you attempt to create newdb for a second time:

 mysql> CREATE DATABASE newdb; ERROR 1007 (HY000): Can't create database 'newdb'; database exists 

The optional attribute IF NOT EXISTS can be added to a CREATE DATABASE statement to avoid an error if the database already exists. Instead, a warning is generated, which you can view using the SHOW WARNINGS command.

 mysql> CREATE DATABASE IF NOT EXISTS newdb; Query OK, 0 rows affected, 1 warning (0.00 sec) 

 mysql> SHOW WARNINGS; +-------+------+--------------------------------------------------+ | Level | Code | Message                                          | +-------+------+--------------------------------------------------+ | Note  | 1007 | Can't create database 'newdb'; database exists   | +-------+------+--------------------------------------------------+ 1 row in set (0.00 sec) 

Because a warning is generated instead of an error, you can use this technique in a batch script that contains a series of SQL commands to set up a database; the script will continue on to the next command if the database already exists.