This is another short blog where we try to answer questions commonly asked (at least per Google) by MySQL users. Today we will take a look at the following question: how to create a database in MySQL?
What is a database in MySQL?
First of all, the question is – what is a database in MySQL? In other RDBMS’ database and a schema can be different things. A database might contain multiple schemas and schemas are namespaces that contain different objects like tables, views, indexes and so on. In MySQL, though, there is no difference between schema and database. In both cases we are talking about the same entity.
In MySQL a single database server may store multiple databases/schemas and each of them may contain different tables. Databases can also be used to separate data between users and create multi-tenant systems. MySQL allows us to grant users access to data located in a particular database, making it possible to implement fine-grained role access control.
Physically, a database in MySQL is a separate directory in MySQL data directory and it may contain files that represent other objects stored in the database.
How to create a database in MySQL?
The simple answer is that you execute following commands, both have the same result and outcome:
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.03 sec)
mysql> CREATE SCHEMA mydb2;
Query OK, 1 row affected (0.02 sec)
There are a couple more options to those statements. If we want to create a database but we do not want MySQL to return an error when such database already exists, we can use “IF NOT EXISTS”:
mysql> CREATE DATABASE mydb; ERROR 1007 (HY000): Can't create database 'mydb'; database exists mysql> CREATE DATABASE IF NOT EXISTS mydb; Query OK, 1 row affected, 1 warning (0.04 sec) mysql> SHOW WARNINGS; +-------+------+-----------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------+ | Note | 1007 | Can't create database 'mydb'; database exists | +-------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
As you can see, in the first attempt we got the error on the statement. In the second attempt MySQL returned only a warning message. The good practice is to use ‘IF NOT EXISTS’ in all cases, especially if you use MySQL replication. Sure, theoretically speaking replicas should contain the same data as source so creating a database on the source should work just fine on replicas but if, for some reason, someone has created such database already on the replica, once we execute CREATE DATABASE on the source, replication will break. Some people may prefer that – it is a clear indication that the replica is not consistent with the source node but for that you have to monitor the state of the replication and detect that it is down. As you can imagine, this is also a good practice to have such monitoring. Some people, though, won’t notice the fact that the replication broke and the replica will serve outdated information. This is up to you to determine which approach is better: use ‘IF NOT EXISTS’ or not.
Another two settings that can be applied when creating the database are character set and collation. MySQL has those defined on multiple levels – whole server, database, table and column. We can set those as default settings for given database like this:
mysql> CREATE DATABASE mydb_latin1 CHARACTER SET='latin1' COLLATE='latin1_general_cs'; Query OK, 1 row affected (0.01 sec) mysql> SHOW CREATE DATABASE mydb_latin1; +-------------+-------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-------------+-------------------------------------------------------------------------------------------------------------------------------------+ | mydb_latin1 | CREATE DATABASE `mydb_latin1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-------------+-------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
If we won’t specify the character set and collation explicitly, default values for the MySQL server will be used:
mysql> SHOW CREATE DATABASE mydb; +----------+--------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------------------------------------------------------------------+ | mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
As you may have noticed, there’s one more setting that you can configure on the database level: encryption. You can either set it to ‘Y’ or to ‘N’ and it defines if the tables in the database should, by default, be encrypted or not. Usually, if you have enabled table encryption, you probably want the tables to be encrypted by default – it would help to avoid mistakes like skipping enabling of the encryption on some of the tables. Human mistakes and errors in the scripts happen.
As you can see, a simple CREATE DATABASE is a bit more complex than one may have thought. If you have any questions or comments related to this functionality, feel free to leave them below.
