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

Open MySQL Workbench
Connect to MySQL server
Click "Create Schema" or use SQL Editor
Enter schema name and options
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

#MySQL Create Database

#Overview

#Database Concepts

#CREATE DATABASE Statement

#Basic Syntax

#Complete Syntax

#Examples

#Character Sets and Collations

#Common Character Sets

#Common Collations

#Setting Character Set and Collation

#Creating Databases in Different Ways

#Using mysqladmin

#Using MySQL Workbench

#Using Programming Languages

#Python / Python

#PHP / PHP

#Command Line with Options

#Managing Databases

#Show Databases

#Select Database

#Database Information

#Alter Database

#Drop Database

#Rename Database

#Database Templates

#Default Database

#Cloning Database

#Practical Examples

#Example 1: Development Database

#Example 2: Production Database

#Example 3: Multi-Tenant Database

#Example 4: Database with Regional Settings

#Best Practices

#Naming Conventions

#Security Considerations

#Performance Considerations

#Troubleshooting

#Common Errors

#Checking Database Status

#Summary