How to Create a Database in MySQL

If you want to create a new database in MySQL, you can either use an SQL command or follow the steps in an IDE such as MySQL Workbench.

Learn how to do it in both ways in this guide.

Table of Contents

MySQL Create Database Command

To create a new database in MySQL, you can use the CREATE DATABASE command.

The simple version looks like this:

CREATE DATABASE database_name;

The full command has a lot more options you can set:

CREATE DATABASE [IF NOT EXISTS] database_name
[DEFAULT] {
  CHARACTER SET charset_name
  | COLLATE collation_name
  | ENCRYPTION {'Y' | 'N'}
}

What do these options mean?

IF NOT EXISTS: Add these keywords to your CREATE DATABASE statement, and the database will be created if it does not exist, and it won’t be created if it already exists.

Without these keywords, if you run the CREATE DATABASE command and the database exists, you’ll get an error message:

Error Code: 1007. Can't create database 'demo'; database exists

CHARACTER SET: This lets you specify the character set for the new database. A character set is a set of characters that can be used in strings in a database.

In MySQL 8.0, the default character set is utf8mb4, but you can change it to another one here. You can also specify the character set at the table or column level.

COLLATE: This lets you specify the collation for the database, which are the rules that define how to compare and sort characters.

In MySQL 8.0, The default collation is utf8mb4_0900_ai_ci, but you can also change this for the database, table, or column.

ENCRYPTION: This allows you to specify the default database encryption, which is used by all tables in the database. It can be set to Y to enable it or N to disable it.

Note: in MySQL, a schema is the same as a database. So you can also run the Create Schema command:

CREATE SCHEMA database_name;

Let’s see an example of creating a new database

Example – Simple Command

Here’s a simple example of a Create Database command.

You can run this on the command line, or within an IDE such as MySQL Workbench

CREATE DATABASE new_shop;

This will create a new database called new_shop on your MySQL server.

Example – If Not Exists

This command will create a database if one does not exist.

CREATE DATABASE IF NOT EXISTS new_shop;

If you run this and the database already exists, you’ll get a warning:

1 row(s) affected, 1 warning(s): 1007 Can't create database 'new_shop'; database exists

If you run this and the database does not exist, the database will be created.

Example – Character Set and Collation

Here’s an example where you specify the character set and collation.

CREATE DATABASE new_site
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

When you run this command, the database will be created.

Example – Access Denied

Here’s an example of running a Create Database command and getting an error.

I’ve created a new user:

CREATE USER user_no_privs IDENTIFIED BY 'mypassword';

After connecting as this new user, I run this Create Database command:

CREATE DATABASE new_site_1;

We get this error message:

Error Code: 1044. Access denied for user 'user_no_privs'@'%' to database 'new_site_1'

Why do we get an Access Denied error when creating a database?

This error happens because the user does not have the required privileges to create a database. To create a database, a user needs the CREATE privilege.

To grant this:

GRANT CREATE ON *.* TO user_no_privs;

I can try to run the Create Database command again as the new user:

CREATE DATABASE new_site_1;

This time, it runs successfully.

For more about MySQL privileges, check out this guide.

Use Database

Once you have created the database, you need to set it as the active database, so you can run queries on it.

To do this, you can run the USE command.

USE database_name;

For example, to use the new_shop database:

USE new_shop;

The new_shop database will now be the active database.

If you’re using MySQL Workbench, the database name in the Schema list will be bolded.

Create Database in MySQL Workbench

You can also create a database in MySQL Workbench without writing a Create Database statement.

To do this:

Step 1: Connect to your server using a user that has the right privileges.

Step 2: Right-click in the Schemas panel on the left and select Create Schema.

A schema is the same thing as a database in MySQL.

A new tab appears to create a new schema.

Step 3: Enter a name for your new database into the Schema Name field.

Here we’ve called it my_new_db.

Step 4: Click on the Apply button on the bottom right.

The “Apply SQL Script to Database” window will appear, where you can review the SQL that will be run.

This is the same as the Create Schema command we saw earlier.

Step 5: Click Apply in the bottom right of this window.

The statements should run successfully.

Step 6: Click Close in the bottom right.

You can then see the new database (called my_new_db) in the schema panel on the left.

Step 7: Double-click on the new database to make it the active database (which is the same as running the USE command).

You can now use your new database.

Conclusion

The MySQL Create Database command is simple to use and is how you create a new database on a MySQL server. You can run the command at the command line, or within an IDE such as MySQL Workbench.