MySQL Create Database Syntax: Tutorial, Examples, FAQs & Interview Tips

CREATE DATABASE

The CREATE DATABASE statement creates a new database on the MySQL server. A database is a tl-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%';

Previous Next

MySQL Create Database Syntax: Tutorial, Examples, FAQs & Interview Tips

CREATE DATABASE

Character Sets and Collations

Database Naming Rules

Level Up Your My sql Skills

Popular Tutorials

Ready to Level Up Your Skills?