MySQL CREATE DATABASE

MySQL CREATE DATABASE: Initializing a Database Schema

The MySQL CREATE DATABASE statement creates a new logical database container for your tables, views, and stored procedures. Setting the right character set and collation at creation time ensures every table inside the database handles international text and emojis properly.

Basic Syntax

Always include IF NOT EXISTS and explicit utf8mb4 encoding when running setup scripts:

CREATE DATABASE IF NOT EXISTS demo_app_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

After creating the database, switch your active session context with USE demo_app_db; and confirm existing databases with SHOW DATABASES;.

Practical SQL Example

This setup script initializes a demo_shop database, activates it, and inspects its exact character set configuration:

-- Create the database safely
CREATE DATABASE IF NOT EXISTS demo_shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Switch active database context
USE demo_shop;

-- Confirm creation settings
SHOW CREATE DATABASE demo_shop;

Common Mistakes and Best Practices

• Avoid Legacy utf8 (3-Byte) Encoding: In older MySQL versions, utf8 meant utf8mb3, which fails when inserting 4-byte emojis or certain CJK characters. Always specify utf8mb4 explicitly.
• Never Rely on CREATE SCHEMA Differently: In MySQL, CREATE SCHEMA is an exact synonym for CREATE DATABASE (unlike PostgreSQL, where schemas live inside a database).
• Use Lowercase Snake-Case Names: Keep database names like demo_shop lowercase without spaces or hyphens so shell scripts and Linux filesystems handle them cleanly.

Frequently Asked Questions

What happens if CREATE DATABASE runs without IF NOT EXISTS on an existing database?

MySQL raises error 1007: Can't create database; database exists and halts the migration script.

Does CREATE DATABASE automatically switch my active session to it?

No, you must execute USE database_name; afterward before running CREATE TABLE without a database prefix.

How do I grant a user access to only one newly created database?

Run GRANT ALL PRIVILEGES ON demo_shop.* TO 'demo_user'@'localhost'; so the user cannot touch other databases on the server.