Create database
MySQL

MySQL CREATE DATABASE Statement: Definitive Guide

intro

Creating a database is the first step to achieving great goals. See three different ways to create databases in MySQL.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

Adding new databases is one of the most common tasks when dealing with a MySQL server. There are several procedures to do that, but they are all based on the MySQL CREATE DATABASE statement.

In this article, you will explore three different approaches to creating a MySQL database:

  • With a command-line command
  • With a query
  • With no code in a database client

Let’s learn how to create a database in MySQL!

What Is a MySQL Database?

In MySQL, a database is a structured collection of data organized in tables. Behind the scenes, that is a directory with files in it stored under the Data directory of a MySQL server. Specifically, a MySQL database is a directory that contains files corresponding to the tables. As of MySQL 8.0, it is no longer feasible to add a database by manually creating a directory under the Data directory. Instead, you need to use the CREATE DATABASE statement.

Time to learn more about it!

MySQL CREATE DATABASE: Syntax and Options

To create a database in MySQL, you need to use a CREATE DATABASE statement with the following syntax:

Copy
        
1 CREATE DATABASE [IF NOT EXISTS] <database_name> 2 [CHARACTER SET [=] <charset_name>] 3 [COLLATE [=] <collation_name>] 4 [ENCRYPTION [=] <encryption_answer>]

<database_name> is the name of the new database you want to create. If there is already a database with the same name in the server, the MySQL CREATE DATABASE statement will fail with:

Can't create database '<database_name>'; database exists

Avoid the error with IF NOT EXISTS. When added to the statement, the MySQL server will:

  • Create the database if it does not already exist
  • Do nothing, otherwise

The remaining part of the statement is optional and allows you to specify the create database MySQL options. These involve:

  • <charset_name>: The name of the character set to use in the database. By default, the MySQL server character set is utf8mb4.
  • <collation_name>: The name of the collation to set for the database. The default collation used by MySQL is utf8mb4_0900_ai_ci.
  • <encryption_answer>: 'Y' to enable the database table encryption feature, 'N' to prevent it. When the ENCRYPTION option is omitted, the default database encryption is read by the default_table_encryption system variable.

Note that If CHARACTER SET is specified without COLLATE, MySQL automatically sets the default collation for the specified <charset_name>. If you are not familiar with these two concepts, the character set defines the specific characters and their encoding used in the database, while the collation determines the rules for comparing and sorting those characters. Use the SHOW CHARACTER SET statement to see the default collation for a character set.

Great, time to see CREATE DATABASE in action!

Understanding the Create Database SQL Statement

In most cases, creating a database in MySQL boils down to running the query below:

Copy
        
1 CREATE DATABASE account;

The above statement will add the account database to the MySQL server:

As you can see from the list of databases on the left, account has been created successfully.
As you can see from the list of databases on the left, account has been created successfully.

Sometimes, you may need to specify more options. Here is what a more complex MySQL CREATE DATABASE sample statement looks like:

Copy
        
1 CREATE DATABASE IF NOT EXISTS user 2 CHARACTER SET utf8mb4 3 COLLATE utf8mb4_unicode_ci;

If it does not already exist, the above query will create an account database with the utf8mb4 character set and utf8mb4_unicode_ci collation.

How to Create a Database in MySQL: 3 Approaches

Here, you will see three different approaches to adding a new database to a MySQL server.

Approach 1: With the MySQL Command-Line Client

Follow the steps below to create a new database with the mysql client tool.

Step 1. Log in to the MySQL Server with a user that has the CREATE privilege on databases:

Copy
        
1 mysql -u <username> -p

Replace <username> with the name of the user you want to log in with. The server will prompt you to enter a password. Type it in and then press Enter.

Logging in to the MySQL server
Logging in to the MySQL server

Step 2. Launch the MySQL CLI CREATE DATABASE statement:

Copy
        
1 CREATE DATABASE <database_name>;

Replace <database_name>  with the name of the database you want to add to the MySQL server. Then, press Enter.

For example, create the user database:

Copy
        
1 mysql> CREATE DATABASE user;

If everything goes as expected, you will receive the following message:

Query OK, 1 row affected (0.04 sec)

Note the feedback message
Note the feedback message

Step 3. Make sure that the database has been created with the SHOW DATABASES command:

Note the “user” database
Note the “user” database

That will return the list of the databases on the server. Note that user is in the list.

Great, mission complete!

Extra tip: To see the creation details, you can launch the SHOW CREATE DATABASE command as follows:

Copy
        
1 SHOW CREATE DATABASE <database_name>;

This will return the complete CREATE DATABASE statement used to create the <database_name> database.

Note the character set and collation used by MySQL by default
Note the character set and collation used by MySQL by default

Fantastic! You are now a master of the MySQL create database command line approach!

Approach 2: Through a Query

If the CLI is not your thing, you can create a MySQL database directly with the CREATE DATABASE statement as seen earlier:

Copy
        
1 CREATE DATABASE <database_name>;

For example, execute the query below to initialize a user database:

Copy
        
1 CREATE DATABASE user;

Depending on the database client you launch the query in, you will receive a different success message.

Approach 3: In a MySQL Database Client

The easiest way to achieve the desired goal is by using a complete database client like DbVisualizer. Such a powerful tool enables you to create a database in MySQL with a few clicks and no code involved. This is just one of the many features offered by the database client with the highest user satisfaction on the market!

Download the DbVisualizer installer, double-click on it, and follow the installation wizard. Launch it and follow the instructions from the docs to set up a connection to your MySQL server database.

Perfect, you’re now fully set up!

Follow the instructions below to create a MySQL database:

Step 1. In the “Databases” tab on the right, open the dropdown associated with your MySQL server to connect to it.

Connecting to the server
Connecting to the server

Step 2. Right click on the “Databases” dropdown and select the “Create Database…” option.

Opening the database creation modal
Opening the database creation modal

The following modal will open:

The database creation modal in Dbvisualizer
The database creation modal in Dbvisualizer

Step 3. Fill out the “Database Name:” field in the creation modal and press the “Execute” button:

Visually adding a new database
Visually adding a new database

A confirmation modal will show up. Select “Yes,” and DbVisualizer will create a database for you:

Note the new “user” database in the list
Note the new “user” database in the list

Et voilà! You did not even have to launch a MySQL CREATE DATABASE query!

Conclusion

In this article, you explored how to create a database in MySQL in three different ways. As you saw here, creating a MySQL database is a simple task. There are three methods to achieve that, and here we dug into all of them. These are:

  1. Launch a command in the mysql CLI client
  2. Run a MySQL CREATE DATABASE query
  3. Use a visual database client such as DbVisualizer

To avoid using the CLI and writing a query manually, you should go for a SQL client like DbVisualizer. On top of supporting dozens of databases, the tool comes with a drag-and-drop UI to build queries, complete query optimization capabilities, and ER schema representation functionality. Download DbVisualizer for free!

FAQ

What are the main things to consider when creating a database?

When creating a database in MySQL, you should keep in mind the following key factors:

  1. Character set and collation: Choose an appropriate character set and collation based on your language and text sorting requirements.
  2. Encryption: Enable data encryption mechanisms to enhance security.
  3. Normalization: Design your database schema with normalization principles to reduce data redundancy and improve data integrity.
  4. Backup and recovery: Develop a robust backup and recovery strategy to ensure data resilience and minimize downtime in case of data loss or corruption.

What are the permissions required to create a database in MySQL?

To create a MySQL database, a user needs the CREATE privilege. To grant a user the permissions required to create a database, you can use the following SQL statement:

Copy
        
1 GRANT CREATE, GRANT OPTION ON *.* TO <target_user>;

Replace <target_user> with the name of the user you want to grant the privileges to.

What is the difference between a MySQL schema vs database?

In MySQL, the terms “schema” and “database” can be used interchangeably, as they essentially refer to the same thing. In other words, there is no difference between MySQL schema vs database. Note that the CREATE SCHEMA and CREATE DATABASE statements produce the same result, which is adding a new database to the server.

How to create database in MySQL Workbench?

To create a database in MySQL Workbench, follow these steps:

  1. Open MySQL Workbench and connect to a server.
  2. Click the “Create a New Schema” button in the top menu and enter the name of your new database.
  3. Choose the character set and collation for your database, or leave it as default.
  4. Click the “Apply” button to create the database.

Is there a MySQL create database if not exists statement?

Yes, there is a MySQL CREATE DATABASE IF NOT EXISTS statement that allows you to create a database only if it does not already exist. That create database MySQL statement is useful to prevent errors when attempting to add a new database with a name that might already be in use.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13
title

mysqldump: How to Backup and Restore MySQL Databases

author Antonello Zanini tags Backup MySQL SQL 11 min 2024-06-06
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗