Creating a database is the first step to achieving great goals. See three different ways to create databases in MySQL.
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:
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:
CREATE DATABASE [IF NOT EXISTS] <database_name>
[CHARACTER SET [=] <charset_name>]
[COLLATE [=] <collation_name>]
[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:
The remaining part of the statement is optional and allows you to specify the create database MySQL options. These involve:
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:
CREATE DATABASE account;
The above statement will add the
account database to the MySQL server:
Sometimes, you may need to specify more options. Here is what a more complex MySQL
CREATE DATABASE sample statement looks like:
CREATE DATABASE IF NOT EXISTS user
CHARACTER SET utf8mb4
If it does not already exist, the above query will create an
account database with the
utf8mb4 character set and
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:
mysql -u <username> -p
<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.
Step 2. Launch the MySQL CLI
CREATE DATABASE statement:
<database_name> with the name of the database you want to add to the MySQL server. Then, press Enter.
For example, create the
mysql> CREATE DATABASE user;
If everything goes as expected, you will receive the following message:
Query OK, 1 row affected (0.04 sec)
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:
SHOW CREATE DATABASE <database_name>;
This will return the complete
CREATE DATABASE statement used to create the
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:
For example, execute the query below to initialize a
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!
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.
Step 2. Right click on the “Databases” dropdown and select the “Create Database…” option.
The following modal will open:
Step 3. Fill out the “Database Name:” field in the creation modal and press the “Execute” button:
A confirmation modal will show up. Select “Yes,” and DbVisualizer will create a database for you:
Et voilà! You did not even have to launch a MySQL
CREATE DATABASE query!
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:
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!
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:
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:
GRANT CREATE, GRANT OPTION ON *.* TO <target_user>;
<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:
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.