MySQL for Application Developers

MySQL has been my default database for about as long as PHP has been my default language. And a database is a lot more than a bucket for app data. Constraints, indexes, transactions, backups, and how you write queries decide whether the app stays correct and fast as it grows.

Design for correctness first

Validation in your PHP code is nice for users, but the database gets the final say on what's unique, what's related to what, and what has to happen together. A good schema makes bad data hard to store in the first place, even when the app has a bug.

Schema and integrity

Queries and performance

Operations

Practical principles

Use utf8mb4 consistently.
Use UTC for stored timestamps unless local wall time is the domain value.
Enforce uniqueness and relationships in the database.
Index for real query patterns.
Test restores, not only backups.

A small starting schema

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(254) NULL,
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
        ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email),
    KEY idx_customers_status_name (status, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;