MySQL CREATE DATABASE

Before you can store any data in MySQL, you need a database to hold it. A database is a named container that groups all related tables together. The CREATE DATABASE statement is used to create a new, empty database on the MySQL server.

Syntax

The basic syntax for creating a database is:

CREATE DATABASE database_name;

Replace database_name with the name you want to give your database. The name must be unique on the server — no two databases can share the same name.

Note: Database names in MySQL are case-sensitive on Linux servers but case-insensitive on Windows. To avoid confusion, always use consistent lowercase naming.

Basic Example

The following statement creates a new database called school:

CREATE DATABASE school;

Once executed successfully, MySQL will respond with:

Query OK, 1 row affected (0.01 sec)

This means the database was created without any errors. The database is now empty — no tables or data yet. You will add those in the next steps of this tutorial series.

Using IF NOT EXISTS

If you try to create a database that already exists, MySQL will return an error:

-- This will throw an error if 'school' already exists
CREATE DATABASE school;
-- ERROR 1007 (HY000): Can't create database 'school'; database exists

To avoid this error — especially useful in scripts that may run more than once — use the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS school;

With this version, if the database already exists, MySQL simply skips the creation and shows a warning instead of an error. Your script continues running without interruption.

Best Practice: Always use IF NOT EXISTS when writing database setup scripts. It makes your scripts safe to run multiple times without causing errors.

Setting Character Set and Collation

When creating a database, you can specify how text characters are stored (character set) and how they are compared and sorted (collation). If you skip these options, MySQL uses its server-wide defaults.

For most modern applications — especially those handling multiple languages or emojis — the recommended settings are:

CREATE DATABASE school
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Option	Value	What it means
`CHARACTER SET`	`utf8mb4`	Supports all Unicode characters including emojis (4-byte UTF-8)
`COLLATE`	`utf8mb4_unicode_ci`	Case-insensitive sorting and comparison using Unicode rules

Why utf8mb4? MySQL's older utf8 character set only supports 3-byte characters and cannot store emojis or certain rare Unicode symbols. utf8mb4 is the correct full Unicode support and should be your default choice for any new database.

Database Naming Rules

When choosing a name for your database, follow these rules:

Names can contain letters, numbers, and underscores (_)
Names cannot contain spaces — use underscores instead (e.g. my_app)
Names cannot be MySQL reserved keywords (e.g. select, table)
Maximum length is 64 characters
Keep names short, lowercase, and descriptive (e.g. ecommerce_db, blog, hospital)

Viewing the New Database

After creating a database, you can confirm it exists by running:

SHOW DATABASES;

Your new database will appear in the list. We will cover this command in detail in the next lesson.

Key Points to Remember

CREATE DATABASE name; creates a new empty database.
Use IF NOT EXISTS to prevent errors if the database already exists.
Always set CHARACTER SET utf8mb4 and COLLATE utf8mb4_unicode_ci for full Unicode support.
Database names cannot have spaces — use underscores instead.
A newly created database is empty — you still need to create tables inside it.

« Previous Lesson Next Lesson »