MySQL Create Database

Overview

Creating a database is one of the first tasks when working with MySQL. A database is a container that holds related data and database objects like tables, views, and indexes.

Database Concepts

• Database: Container for tables and other objects
• Character Set: Character encoding for data storage
• Collation: Rules for comparing and sorting strings
• Schema: Database structure definition

CREATE DATABASE Statement

Basic Syntax

-- Create a new database
CREATE DATABASE database_name;

-- Create if not exists (recommended)
CREATE DATABASE IF NOT EXISTS database_name;

Complete Syntax

CREATE DATABASE [IF NOT EXISTS] database_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

Examples

-- Create basic database
CREATE DATABASE myapp;

-- Create with specific character set
CREATE DATABASE myapp CHARACTER SET utf8mb4;

-- Create with specific collation
CREATE DATABASE myapp COLLATE utf8mb4_unicode_ci;

-- Create with both character set and collation
CREATE DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Create if not exists
CREATE DATABASE IF NOT EXISTS myapp;

Character Sets and Collations

Common Character Sets

| Character Set |----------------------|------------------| | latin1 | Latin1 (Western European) | utf8 | UTF-8 (up to 3 bytes per character) | utf8mb4 | UTF-8 (full Unicode, up to 4 bytes)

Common Collations

| Collation |--------------------|------------------| | utf8mb4_general_ci | General case-insensitive | utf8mb4_unicode_ci | Unicode case-insensitive | utf8mb4_0900_ai_ci | Unicode 9.0, accent-insensitive | utf8mb4_0900_as_cs | Unicode 9.0, accent-sensitive

Setting Character Set and Collation

-- Check available character sets
SHOW CHARACTER SET;

-- Check available collations
SHOW COLLATION;

-- Create database with UTF-8
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create database for Chinese
CREATE DATABASE chinese_app
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

-- Create database for case-sensitive data
CREATE DATABASE sensitive_data
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_bin;

Creating Databases in Different Ways

Using mysqladmin

# Create database from command line
mysqladmin -u root -p create database_name

# Create with character set
mysqladmin -u root -p create database_name --default-character-set=utf8mb4

# Drop database
mysqladmin -u root -p drop database_name

Using MySQL Workbench

1. Open MySQL Workbench
2. Connect to MySQL server
3. Click "Create Schema" or use SQL Editor
4. Enter schema name and options
5. Click "Apply"

Using Programming Languages

Python / Python

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)

cursor = conn.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS myapp")

# With character set
cursor.execute("CREATE DATABASE IF NOT EXISTS myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")

conn.close()

PHP / PHP

<?php
$conn = new mysqli('localhost', 'username', 'password');

// Create database
$conn->query("CREATE DATABASE IF NOT EXISTS myapp");

// Check if database exists
if ($conn->query("CREATE DATABASE IF NOT EXISTS myapp") === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Command Line with Options

# Create and use database
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS myapp"

# Create multiple databases
mysql -u root -p -e "
CREATE DATABASE IF NOT EXISTS app1;
CREATE DATABASE IF NOT EXISTS app2;
CREATE DATABASE IF NOT EXISTS app3;
"

Managing Databases

Show Databases

-- List all databases
SHOW DATABASES;

-- List databases matching pattern
SHOW DATABASES LIKE 'myapp%';

-- List databases with extended info
SHOW DATABASES\G

-- Using information_schema
SELECT schema_name FROM information_schema.schemata;

Select Database

-- Use database
USE database_name;

-- Check current database
SELECT DATABASE();

-- Set default database in connection
mysql -u username -p database_name

Database Information

-- Get database character set
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.schemata
WHERE schema_name = 'database_name';

-- Get database size
SELECT 
    schema_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'database_name';

-- Get table count in database
SELECT 
    COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'database_name';

Alter Database

-- Change character set
ALTER DATABASE database_name CHARACTER SET utf8mb4;

-- Change collation
ALTER DATABASE database_name COLLATE utf8mb4_unicode_ci;

-- Change both
ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Change database comments (MySQL 8.0+)
ALTER DATABASE database_name
    COMMENT = 'Production database for my application';

Drop Database

-- Drop database
DROP DATABASE database_name;

-- Drop if exists (recommended)
DROP DATABASE IF EXISTS database_name;

-- Drop with patterns
DROP DATABASE IF EXISTS test%;

Rename Database

-- Method 1: Use RENAME (MySQL 8.0+)
RENAME DATABASE old_name TO new_name;

-- Method 2: Create new and migrate
CREATE DATABASE new_db;
RENAME TABLE old_db.table1 TO new_db.table1;
RENAME TABLE old_db.table2 TO new_db.table2;
-- ... repeat for all tables
DROP DATABASE old_db;

Database Templates

Default Database

MySQL includes template databases:

| Database |-----------------|--------------| | information_schema | Metadata about all databases | mysql | System database | performance_schema | Performance metrics | sys | System views for diagnostics

Cloning Database

# Export and import
mysqldump -u root -p source_db | mysql -u root -p target_db

# Using MySQL Shell
mysqlsh -- util dump-instance dump_directory /tmp/db_dump
mysqlsh -- util load dump_directory /tmp/db_dump

Practical Examples

Example 1: Development Database

-- Create development database
CREATE DATABASE IF NOT EXISTS myapp_dev
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Add comment
ALTER DATABASE myapp_dev
    COMMENT = 'Development database for testing';

Example 2: Production Database

-- Create production database with strict mode
CREATE DATABASE IF NOT EXISTS myapp_prod
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Production settings
ALTER DATABASE myapp_prod
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
    COMMENT = 'Production database - Do NOT modify';

Example 3: Multi-Tenant Database

-- Create tenant database
CREATE DATABASE IF NOT EXISTS tenant_company_a
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

CREATE DATABASE IF NOT EXISTS tenant_company_b
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Use tenant database
USE tenant_company_a;

Example 4: Database with Regional Settings

-- Database for Chinese users
CREATE DATABASE IF NOT EXISTS app_cn
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_chinese_ci;

-- Database for German users
CREATE DATABASE IF NOT EXISTS app_de
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_german_ci;

-- Database for Japanese users
CREATE DATABASE IF NOT EXISTS app_jp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_ja_0900_as_cs;

Best Practices

Naming Conventions

-- Use descriptive names
CREATE DATABASE IF NOT EXISTS e_commerce;     -- Good
CREATE DATABASE IF NOT EXISTS ec;             -- Avoid

-- Use lowercase with underscores
CREATE DATABASE IF NOT EXISTS user_management;  -- Good
CREATE DATABASE IF NOT EXISTS UserManagement;   -- Avoid

-- Avoid reserved words
CREATE DATABASE IF NOT EXISTS orders;       -- Good (not reserved)
CREATE DATABASE IF NOT EXISTS select;       -- Avoid (reserved)

Security Considerations

-- Create dedicated user for database
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

-- Restrict network access
-- In my.cnf:
bind-address = 127.0.0.1  # Only local access

Performance Considerations

-- Choose appropriate character set
-- utf8mb4 is recommended for Unicode support

-- Use consistent collation across databases
CREATE DATABASE IF NOT EXISTS myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Troubleshooting

Common Errors

-- Database already exists
-- Error: Can't create database 'name'; database exists
-- Solution: Use IF NOT EXISTS

-- Permission denied
-- Error: Access denied for user to create database
-- Solution: Grant CREATE privilege or use root user

-- Invalid character set
-- Error: Unknown character set or collation
-- Solution: Use SHOW CHARACTER SET to check available

-- Database doesn't exist
-- Error: Unknown database
-- Solution: Check database name with SHOW DATABASES

Checking Database Status

-- Check if database exists
SELECT SCHEMA_NAME FROM information_schema.schemata
WHERE SCHEMA_NAME = 'database_name';

-- Get database details
SELECT * FROM information_schema.schemata
WHERE schema_name = 'database_name';

-- List databases with sizes
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

Summary

Creating databases in MySQL involves:

• CREATE DATABASE: Basic database creation
• Character Sets: Choosing appropriate encoding
• Collations: Defining comparison rules
• Management: Show, alter, drop databases
• Security: Proper user privileges

---

Previous: Connection

Next: Drop Database