MySQL: The Complete Guide to the World's Most Popular Open Source Database

Introduction

MySQL is the world’s most popular open-source relational database, powering millions of applications from small websites to enterprise-scale systems. Originally developed by Michael Widenius and David Axmark in 1995, MySQL has become synonymous with web applications, powering platforms like Facebook, Twitter, YouTube, and WordPress.

In 2026, MySQL continues to evolve with the 8.0 series and the newer 8.4 LTS (Long Term Support) version. This comprehensive guide covers everything you need to get started with MySQL.

What is MySQL?

MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). It offers:

ACID Compliance: Atomicity, Consistency, Isolation, Durability through InnoDB
Cross-Platform: Runs on Linux, Windows, macOS, and more
Multiple Storage Engines: InnoDB, MyISAM, Memory, CSV, etc.
Scalability: From single-server to clustered deployments
Replication: Master-slave and Group Replication
Stored Procedures: Server-side programming

MySQL vs PostgreSQL

Feature	MySQL	PostgreSQL
License	GPL (Oracle)	PostgreSQL License
Default Engine	InnoDB	N/A (built-in)
JSON Support	Good	Excellent
Full-Text Search	Native	Native
GIS Support	Good	Excellent (PostGIS)
Window Functions	MySQL 8.0+	Native
Complex Queries	Good	Excellent
Performance	Fast for simple queries	Better for complex OLAP

Installation

Docker Installation (Recommended)

# Start MySQL container
docker run --name mysql \
  -e MYSQL_ROOT_PASSWORD=mysecretpassword \
  -e MYSQL_DATABASE=mydb \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppassword \
  -v mysql_data:/var/lib/mysql \
  -p 3306:3306 \
  -d mysql:8.4

# Connect to MySQL
docker exec -it mysql mysql -u root -p

Linux Installation

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# Start MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# Secure installation
sudo mysql_secure_installation

# Connect
sudo mysql -u root -p

macOS Installation

# Using Homebrew
brew install [email protected]
brew services start [email protected]

# Connect
mysql -u root -p

Basic SQL Operations

Creating Databases and Tables

-- Create database
CREATE DATABASE myapp;

-- Use database
USE myapp;

-- Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Create orders table with foreign key
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CRUD Operations

-- INSERT: Create new records
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', '[email protected]', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password_hash)
VALUES 
    ('alice', '[email protected]', 'hash1'),
    ('bob', '[email protected]', 'hash2');

-- INSERT...RETURNING (MySQL 8.0+)
INSERT INTO users (username, email, password_hash)
VALUES ('new_user', '[email protected]', 'hash')
RETURNING id, username;

-- SELECT: Read data
SELECT * FROM users;
SELECT username, email FROM users;
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- UPDATE: Update records
UPDATE users 
SET email = '[email protected]' 
WHERE username = 'john_doe';

-- DELETE: Delete records
DELETE FROM users WHERE id = 1;

Query Filtering

-- Comparison operators
SELECT * FROM users WHERE age >= 18;
SELECT * FROM products WHERE price < 100;

-- IN and NOT IN
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');

-- BETWEEN
SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 500;

-- LIKE and ILIKE
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IS NULL
SELECT * FROM users WHERE phone IS NOT NULL;

-- AND, OR, NOT
SELECT * FROM users WHERE is_active = TRUE AND country = 'USA';

Data Types

Numeric Types

-- Integer types
TINYINT      -- -128 to 127
SMALLINT     -- -32,768 to 32,767
INT          -- -2,147,483,648 to 2,147,483,647
BIGINT       -- -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Floating point
FLOAT        -- Single precision
DOUBLE       -- Double precision
DECIMAL(p, s) -- Exact numeric

-- Examples
CREATE TABLE financials (
    amount DECIMAL(15, 2),
    rate DECIMAL(5, 4),
    quantity INT
);

String Types

-- Character types
CHAR(n)        -- Fixed length, padded
VARCHAR(n)     -- Variable length, max n
TINYTEXT       -- Up to 255 bytes
TEXT           -- Up to 65,535 bytes
MEDIUMTEXT     -- Up to 16MB
LONGTEXT       -- Up to 4GB

-- Binary types
BINARY(n)      -- Fixed length binary
VARBINARY(n)   -- Variable length binary
BLOB           -- Binary large object

-- Examples
CREATE TABLE profiles (
    code CHAR(2),
    name VARCHAR(100),
    bio TEXT
);

Date/Time Types

-- Date and time types
DATE           -- '2026-03-05'
TIME           -- '14:30:00'
DATETIME       -- '2026-03-05 14:30:00'
TIMESTAMP      -- Unix timestamp
YEAR           -- Year (2 or 4 digit)

-- Examples
CREATE TABLE events (
    event_date DATE,
    start_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Date functions
SELECT CURRENT_DATE;
SELECT NOW();
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
SELECT DATEDIFF(end_date, start_date) FROM bookings;

JSON Type (MySQL 5.7+)

-- JSON data type
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content JSON,
    metadata JSON
);

-- Insert JSON
INSERT INTO documents (title, content, metadata)
VALUES (
    'Invoice',
    '{"items": [{"qty": 2, "price": 10}]}',
    '{"author": "John", "version": 1}'
);

-- Query JSON
SELECT content->>'$.items[0].qty' FROM documents;
SELECT JSON_EXTRACT(content, '$.items') FROM documents;
SELECT * FROM documents WHERE JSON_CONTAINS(content, '"John"', '$.author');

Spatial Data Types (MySQL 8.0+)

-- Spatial types
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT,
    area POLYGON
);

-- Insert spatial data
INSERT INTO locations (name, coordinates)
VALUES 
    ('Office', ST_GeomFromText('POINT(37.7749 -122.4194)', 4326)),
    ('Warehouse', ST_GeomFromText('POINT(37.8049 -122.2694)', 4326));

-- Query spatial data
SELECT ST_Distance(
    (SELECT coordinates FROM locations WHERE name = 'Office'),
    (SELECT coordinates FROM locations WHERE name = 'Warehouse')
) AS distance;

Constraints

-- Primary Key
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- Composite Primary Key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Foreign Key
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Unique Constraint
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- Check Constraint (MySQL 8.0.16+)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0),
    quantity INT CHECK (quantity >= 0)
);

Indexes

Index Types

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Prefix index (for large text)
CREATE INDEX idx_users_email ON users(email(10));

-- Full-text index (MySQL 5.6+)
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);

-- Spatial index (MySQL 8.0+)
CREATE SPATIAL INDEX idx_locations_coords ON locations(coordinates);

Index Optimization

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Force index
SELECT * FROM orders USE INDEX (idx_orders_user_id) WHERE user_id = 1;

-- Ignore index
SELECT * FROM orders IGNORE INDEX (idx_orders_user_status) WHERE user_id = 1;

Joins

-- INNER JOIN
SELECT o.id, u.username, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN
SELECT u.username, o.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.username, o.id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Multiple joins
SELECT o.id, u.username, p.name, oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Aggregations and Grouping

-- Basic aggregations
SELECT COUNT(*) FROM users;
SELECT SUM(total_amount) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(created_at) FROM orders;
SELECT MAX(price) FROM products;

-- GROUP BY
SELECT user_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
FROM orders
GROUP BY user_id;

-- HAVING
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Multiple aggregations
SELECT 
    status,
    COUNT(*) as count,
    SUM(total_amount) as total,
    AVG(total_amount) as average
FROM orders
GROUP BY status;

Window Functions (MySQL 8.0+)

-- ROW_NUMBER
SELECT 
    username,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

-- RANK and DENSE_RANK
SELECT 
    product_name,
    sales,
    RANK() OVER (ORDER BY sales DESC) as rank
FROM products;

-- Running total
SELECT 
    created_at,
    amount,
    SUM(amount) OVER (ORDER BY created_at) as running_total
FROM transactions;

-- Partition by
SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

Transactions

-- Basic transaction
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 500);
COMMIT;

-- Rollback
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

-- Savepoint
START TRANSACTION;
INSERT INTO orders (total_amount) VALUES (100);
SAVEPOINT order_1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
ROLLBACK TO SAVEPOINT order_1;
COMMIT;

Isolation Levels

SET TRANSACTION ISOLATION LEVEL 
    READ UNCOMMITTED 
    | READ COMMITTED 
    | REPEATABLE READ 
    | SERIAlIZABLE;

-- Check current isolation
SELECT @@transaction_isolation;

Views

-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = TRUE;

-- Updatable view
CREATE VIEW order_summary AS
SELECT 
    o.id,
    u.username,
    o.total_amount,
    o.status
FROM orders o
JOIN users u ON o.user_id = u.id;

-- Materialized view (simulated with table)
CREATE TABLE monthly_sales AS
SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

Stored Procedures and Functions

-- Stored procedure
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
    SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

-- Call procedure
CALL get_user_orders(1);

-- Stored function
DELIMITER //
CREATE FUNCTION get_order_count(user_id INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM orders WHERE user_id = user_id;
    RETURN count;
END //
DELIMITER ;

-- Use function
SELECT username, get_order_count(id) as order_count FROM users;

Conclusion

MySQL provides a solid foundation for building data-driven applications. Its balance of simplicity, performance, and features makes it an excellent choice for web applications and beyond.

In the next article, we’ll explore MySQL operations: backup strategies, replication, high availability, and monitoring.