MySQL CREATE DATABASE Guide for Beginners
For readers who have just installed MySQL and know a little SQL, such as CREATE TABLE and SELECT.
On this page
CREATE DATABASE makes a new, empty database on a MySQL server, a named place for your tables. For example, you can create a database for an online shop before you create the shop's first table. Every MySQL table belongs to a database.
The statement takes a name. It can also set the default character set and collation of the database's tables:
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];
CREATE DATABASEcreates the database.CREATE SCHEMAdoes the same.IF NOT EXISTSavoids the error when a database of that name already exists.database_nameis the name, up to 64 characters.CHARACTER SETsets the default character set of new tables in the database. A character set decides which characters text can hold.COLLATEsets the default collation of those tables. A collation decides how text compares and sorts.
Run the examples in the mysql command-line client of MySQL 8.4. Log in as root, or as another account that may create databases (the CREATE privilege):
mysql -u root -p
-p with no value makes mysql ask for the password. A password typed after -p would be visible to other users of the machine, through programs such as ps.
You create the sample database in the next section, and every later statement uses it.
CREATE DATABASE
CREATE DATABASE with only a name uses the server's default character set and collation. For example, you can create a database named shop for an online shop.
CREATE DATABASE shop;
On disk, MySQL creates only a directory for shop under the server's data directory, the folder where MySQL keeps its data. Later, the tables of shop go into that directory.
The statement names no character set or collation, so shop takes the server defaults. On a server with default settings, the defaults are utf8mb4 and utf8mb4_0900_ai_ci. utf8mb4 stores any Unicode text.
A table created in shop takes the defaults of shop:
SHOW DATABASES
SHOW DATABASES lists the databases on the server. For example, you can check that shop exists.
SHOW DATABASES;
The list holds shop and the other databases where your account has any privilege. With the SHOW DATABASES privilege for the whole server, the list holds every database. On a server with many databases, SHOW DATABASES LIKE 'sh%' lists only the names that start with "sh".
USE
USE sets the default database: the database that MySQL uses when a statement names none. For example, you can select shop and then create a table in it.
USE shop;
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
shop stays the default until you run another USE, or until you log out and the session ends.
CREATE DATABASE does not select the new database. Without USE, write the database name and the table name with a dot between them: shop.customers.
To see which database is selected, run SELECT DATABASE();. DATABASE() returns the name of the selected database, or NULL when none is selected. To select shop as you log in, add shop after -p and a space: mysql -u root -p shop.
IF NOT EXISTS
IF NOT EXISTS creates the database only if the database is missing. For example, a setup script (a file of SQL statements) can create shop every time it runs.
CREATE DATABASE IF NOT EXISTS shop;
When shop exists, the statement raises no error, and the script carries on. Without IF NOT EXISTS, a second CREATE DATABASE shop; fails with an error, because shop exists.
CHARACTER SET and COLLATE
CHARACTER SET and COLLATE give a new database its own defaults in place of the server's. For example, you can create a database for product codes, where 'ab-1' and 'AB-1' are two different codes.
Create a second database, shop_codes:
CREATE DATABASE shop_codes
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;
The server default, utf8mb4_0900_ai_ci, ignores case and accents, so 'abc' equals 'ABC'. Tables in shop_codes default to the binary collation utf8mb4_bin. A binary collation compares the number that encodes each character, so 'ab-1' and 'AB-1' differ.
You can also name only one of the two. With only CHARACTER SET, the database gets the default collation of that character set. With only COLLATE, the database gets the character set of that collation.
SHOW CREATE DATABASE
SHOW CREATE DATABASE prints the statement that creates the database, with the character set and collation filled in. For example, you can see which defaults shop took from the server.
SHOW CREATE DATABASE shop;
For shop, the printed CREATE DATABASE names the two server defaults.
In the manual's example, MySQL writes the CHARACTER SET and COLLATE clauses inside version comments, which start with /*!. MySQL runs the SQL inside a version comment. Other database servers skip the comment.
The same two values are in the DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME columns of information_schema.SCHEMATA.
ALTER DATABASE
ALTER DATABASE changes the defaults of a database that already exists. For example, you can give shop the binary collation utf8mb4_bin.
ALTER DATABASE shop COLLATE utf8mb4_bin;
New tables in shop default to utf8mb4_bin. The table customers keeps utf8mb4_0900_ai_ci, because a table takes the database default only when the table is created.
To change customers as well, convert the table: ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;.
DROP DATABASE
DROP DATABASE deletes a database and every table in it. For example, you can delete shop_codes, the database for product codes.
DROP DATABASE IF EXISTS shop_codes;
IF EXISTS avoids the error when the database doesn't exist. If shop_codes was the default database, MySQL has no default database, and SELECT DATABASE(); returns NULL.
The manual warns: "Be very careful with this statement!"
What goes wrong
Access denied
CREATE DATABASE needs the CREATE privilege for the database. Without that privilege, MySQL refuses with an access-denied error, as the manual's tutorial shows. Log in as root, or ask the server's administrator to grant you the privilege.
utf8 is not utf8mb4
CHARACTER SET utf8 looks like full UTF-8, but in MySQL 8.4 it means utf8mb3. utf8mb3 stores at most three bytes per character. So utf8mb3 can't store the characters that need four bytes, such as most emoji.
Write utf8mb4 instead. utf8mb4 is the server default, and utf8mb3 is also deprecated: a later MySQL release may remove it.
Upper and lower case in names
Each database is a directory on disk, so the operating system decides whether shop and Shop are one database.
On most Unix systems, shop and Shop are two databases. On Windows, and by default on macOS, they are one. The server setting lower_case_table_names decides this, and you can change it only when the data directory is first created.
Write database names in lowercase. Lowercase names work the same on every server.
Names with a hyphen
Without backticks, a name may hold letters, digits, $ and _. A name with a hyphen, a space or other punctuation needs backticks in every statement that uses it:
CREATE DATABASE `my-shop`;
Write my_shop in place of my-shop. An underscore needs no backticks.
Create the database in DbSchema
DbSchema can create a database on the MySQL server from its connection dialog:
- Choose File → New Model, and keep Connect to Database selected.
- Click MySQL, choose New Connection, and press Connect to MySQL.
- Set Method to Standard, and fill in Database User and Password.
- Next to Database, press Create New, type
shop, and confirm. - Press Connect, then tick
shopin Reverse Engineer the Database and press Import.
The Create New button runs CREATE DATABASE on the MySQL server, so shop exists on the server as soon as you confirm. DbSchema then reads shop into a new model and draws the tables of shop in a diagram. A model is DbSchema's own copy of the database structure.
The model exists only in DbSchema until you save it, and saving the model to a file needs DbSchema Pro.
Download DbSchema at https://dbschema.com/download.html, connect it to your MySQL server, and create the next database from the connection dialog. You can also run the statements of this article in the DbSchema SQL Editor. The free Community Edition covers the connection dialog, its Create New button, and the SQL Editor.
