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:
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:
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:
1
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:
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:
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.
Step 2. Launch the MySQL CLI CREATE DATABASE statement:
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:
1
mysql> CREATE DATABASE user;
If everything goes as expected, you will receive the following message:
Query OK, 1 row affected (0.04 sec)
Step 3. Make sure that the database has been created with the SHOW DATABASES command:
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:
1
SHOW CREATE DATABASE <database_name>;
This will return the complete CREATE DATABASE statement used to create the <database_name> database.
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:
1
CREATE DATABASE <database_name>;
For example, execute the query below to initialize a user database:
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.
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!
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:
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:
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:
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:
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.
