MySQL Create Database Syntax

CREATE DATABASE

Verify the active database, effective character set, collation, and account privileges before creating tables or loading data.

The CREATE DATABASE statement creates a new database on the MySQL server. A database is a container that holds tables, views, stored procedures, and other objects. You need the CREATE privilege to run this statement.

Creating and Selecting a Database

-- Basic syntax
CREATE DATABASE shop;

-- Avoid error if database already exists
CREATE DATABASE IF NOT EXISTS shop;

-- Create with explicit character set and collation (recommended)
CREATE DATABASE IF NOT EXISTS shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- List all databases on the server
SHOW DATABASES;

-- Select the database to use for subsequent queries
USE shop;

-- Confirm which database is currently active
SELECT DATABASE();

Character Sets and Collations

Always create databases with utf8mb4 character set and utf8mb4_unicode_ci collation. The older utf8 in MySQL is actually a 3-byte subset that cannot store emoji or some rare Unicode characters - utf8mb4 is the true 4-byte UTF-8 encoding.

CHARACTER SET - Defines which characters can be stored (e.g. utf8mb4 supports all Unicode characters including emoji).
COLLATE - Defines how strings are compared and sorted. utf8mb4_unicode_ci is case-insensitive and accent-insensitive, following Unicode rules.

ALTER DATABASE and DROP DATABASE

-- Change the character set and collation of an existing database
ALTER DATABASE shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Show the CREATE DATABASE statement (includes charset/collation)
SHOW CREATE DATABASE shop;

-- Drop a database (CAUTION: deletes all tables and data permanently)
DROP DATABASE shop;

-- Drop only if it exists (avoids error)
DROP DATABASE IF EXISTS shop;

Database Naming Rules

Names can contain letters, digits, underscores (_), and dollar signs ($).
Names are case-sensitive on Linux/macOS file systems but case-insensitive on Windows by default.
Maximum length is 64 characters.
Avoid reserved words (e.g. SELECT, TABLE) as database names - if you must, wrap them in backticks: `select`.
Use lowercase with underscores for consistency: my_shop, ecommerce_db.

Setting Up the shop Database

-- Full setup for the shop database used throughout this tutorial
CREATE DATABASE IF NOT EXISTS shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE shop;

-- Verify the database was created with correct settings
SHOW CREATE DATABASE shop\G

-- Check available character sets
SHOW CHARACTER SET LIKE 'utf8%';

-- Check available collations for utf8mb4
SHOW COLLATION WHERE Charset = 'utf8mb4' AND Collation LIKE '%unicode%';

Creating a MySQL database with the right defaults

CREATE DATABASE creates a logical container for tables, views, routines, and other database objects. The database name should describe the application or module clearly. In real projects, separate databases are often used for development, testing, staging, and production so data does not get mixed accidentally.

Character set and collation are not minor details. utf8mb4 supports a wide range of Unicode characters, including emoji and many international scripts. Collation controls how text comparison and sorting behave. Choosing these defaults at creation time prevents many text storage and ordering problems later.

Use clear database names such as school_app or inventory_dev.
Prefer utf8mb4 for modern applications.
Choose a collation that matches expected sorting rules.
Grant permissions deliberately instead of using root for application access.

Create a Unicode-ready application database

CREATE DATABASE inventory_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE inventory_app;

Before you move on