MySQL CREATE DATABASE

What is a Database in MySQL?

Think of a MySQL database as a filing cabinet. Each cabinet (database) contains drawers (tables), and each drawer holds folders (rows) with documents (data). Before you can store anything, you need the cabinet.

In MySQL, a database is a container that holds related tables, views, stored procedures, and other objects. You'll typically have separate databases for different applications or environments (development, staging, production).

Basic Syntax

Creating a database is one of the simplest operations in MySQL:

CREATE DATABASE database_name;

That's it. MySQL will create an empty database ready for tables.

CREATE DATABASE my_app;

Character Set and Collation

Here's where most tutorials fall short. Character set and collation matter—especially if your app will ever handle non-English text, emojis, or special characters.

Character Set

Defines which characters can be stored. Always use utf8mb4 (not utf8—MySQL's utf8 only supports 3 bytes and can't handle all Unicode characters like emojis).

CREATE DATABASE my_app
CHARACTER SET utf8mb4;

Collation

Defines how characters are compared and sorted. Use utf8mb4_unicode_ci for case-insensitive comparisons that handle international characters correctly.

CREATE DATABASE my_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Why utf8mb4_unicode_ci? The ci means case-insensitive, so 'Apple' equals 'apple' in searches. The unicode part means it follows Unicode sorting rules, handling accented characters properly (é, ñ, ü).

Handling Existing Databases

Check If Database Exists First

CREATE DATABASE IF NOT EXISTS my_app;

This prevents errors if the database already exists. Useful in scripts that might run multiple times.

Drop and Recreate (Dangerous!)

DROP DATABASE IF EXISTS my_app;
CREATE DATABASE my_app;

Warning: This deletes ALL data in the database permanently. Only use this in development or when you're absolutely certain.

Viewing Your Databases

List all databases on the server:

SHOW DATABASES;

See details about a specific database:

SHOW CREATE DATABASE my_app;

This shows the exact CREATE statement with character set and collation.

Switching Databases

After creating a database, you need to select it before creating tables:

USE my_app;

Or specify the database in your queries:

CREATE TABLE my_app.users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

Naming Best Practices

Good database names are:

• Lowercase: Avoids case-sensitivity issues across operating systems
• Snake_case or single word: my_app, user_service, analytics
• Descriptive: The name should hint at what's inside
• Short but clear: You'll type this a lot

-- Good names
CREATE DATABASE ecommerce;
CREATE DATABASE user_service;
CREATE DATABASE analytics_2024;

-- Avoid these
CREATE DATABASE MyApp;           -- Mixed case causes issues
CREATE DATABASE db1;             -- Not descriptive
CREATE DATABASE my-app;          -- Hyphens aren't allowed
CREATE DATABASE my app;          -- Spaces aren't allowed

Common Patterns

One Database Per Application

Most web applications use a single database:

CREATE DATABASE myapp_production
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Environment-Specific Databases

For development workflows:

CREATE DATABASE myapp_development;
CREATE DATABASE myapp_test;
CREATE DATABASE myapp_production;

Multi-Tenant (Database Per Customer)

Some applications create a database per customer for isolation:

CREATE DATABASE tenant_acme_corp;
CREATE DATABASE tenant_globex;

Permissions

After creating a database, you'll typically create a user with access to it:

-- Create the database
CREATE DATABASE my_app;

-- Create a user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant permissions
GRANT ALL PRIVILEGES ON my_app.* TO 'app_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Quick Reference

| Command | Description | |---------|-------------| | CREATE DATABASE name | Create a new database | | CREATE DATABASE IF NOT EXISTS name | Create only if it doesn't exist | | DROP DATABASE name | Delete a database (permanent!) | | SHOW DATABASES | List all databases | | SHOW CREATE DATABASE name | Show creation statement | | USE name | Switch to a database |

Summary

Creating a MySQL database is simple, but getting the details right matters:

1. Always use utf8mb4 character set for full Unicode support
2. Use utf8mb4_unicode_ci collation for case-insensitive, international-friendly comparisons
3. Use IF NOT EXISTS in scripts to make them idempotent
4. Follow naming conventions: lowercase, no spaces, descriptive

With your database created, you're ready to start creating tables.