🐬 Mastering MySQL: Comprehensive Technical Guide

Welcome to the Mastering MySQL technical guide. This document is a comprehensive, structured, and actionable resource designed for developers ranging from beginners to intermediate levels. It covers the end-to-end spectrum of MySQL database management—from foundational SQL syntax and core relational concepts to advanced topics like indexing, transactions, security, and Node.js integration.

This guide emphasizes practical application, providing robust explanations, industry best practices, and extensive real-world examples to help you build resilient and scalable backend systems.

1. Introduction to MySQL

What is MySQL?

MySQL is an open-source Relational Database Management System (RDBMS) backed by Oracle. It uses Structured Query Language (SQL) to manage, manipulate, and retrieve data. Since its release in 1995, it has become one of the world's most popular databases due to its reliability, performance, and ease of use.

MySQL organizes data into structured tables with predefined columns and rows, establishing relationships between these tables to form a comprehensive data model.

SQL vs. NoSQL

Understanding when to choose MySQL requires contrasting it with NoSQL databases (like MongoDB or Redis).

Feature	SQL (MySQL, PostgreSQL)	NoSQL (MongoDB, DynamoDB)
Data Model	Relational, structured tables with rows/columns.	Document, key-value, graph, or wide-column.
Schema	Rigid, predefined schema. Schema changes require migrations.	Flexible, dynamic schema. Fields can vary per document.
Query Language	SQL (Standardized, powerful for complex joins).	Varies by database (e.g., MQL for MongoDB).
ACID Compliance	Strong ACID guarantees out-of-the-box.	Varies, historically eventual consistency, but improving.
Scaling	Vertical scaling (scaling up hardware). Harder to scale horizontally.	Horizontal scaling (sharding across servers) is often easier.
Best For	Complex queries, multi-row transactions, strict data integrity.	Rapid development, unstructured data, massive horizontal scale.

Use Cases for MySQL

MySQL excels in scenarios where data integrity and structured relationships are paramount:

E-commerce Platforms: Managing products, carts, orders, and complex financial transactions where consistency is critical.
Content Management Systems (CMS): WordPress, Drupal, and Joomla are heavily reliant on MySQL.
Financial Systems: Banking or ledger applications requiring strict ACID properties.
SaaS Applications: Managing user accounts, subscriptions, and tenant data.

2. Core Concepts

Databases, Tables, Rows, and Columns

A MySQL server can host multiple databases. Understanding the hierarchy is essential:

Database (Schema): A container for a collection of related tables and other objects (views, stored procedures).
Table: A structured set of data organized into vertical columns and horizontal rows.
Column (Field/Attribute): Defines the type of data that a particular property holds (e.g., first_name as a string, age as an integer).
Row (Record/Tuple): A single, distinct entry in a table representing a complete set of data corresponding to the table's columns.

Data Types

Choosing the correct data type is crucial for data integrity, performance, and storage optimization.

1. Numeric Types

TINYINT: Very small integer (-128 to 127). Often used for booleans (TINYINT(1)).
INT / INTEGER: Standard integer (-2 billion to +2 billion). Used for primary keys, counts, IDs.
BIGINT: Extremely large integers.
DECIMAL(M, D): Exact fixed-point number. Crucial for financial data to prevent floating-point rounding errors. M is the total number of digits, D is the number of digits after the decimal. (e.g., DECIMAL(10, 2) can store 99999999.99).
FLOAT / DOUBLE: Approximate floating-point numbers. Good for scientific calculations, bad for currency.

2. String Types

CHAR(N): Fixed-length string. Space is always allocated. Good for consistent length data (e.g., country codes, hashes).
VARCHAR(N): Variable-length string up to N characters. Space used depends on the actual string length + 1 or 2 bytes for length prefix. Good for names, emails.
TEXT: Long-form text data (up to 64KB).
LONGTEXT: Extremely long text data (up to 4GB). Used for articles, logs, large JSON blobs (prior to JSON type).

3. Date and Time Types

DATE: Date only (YYYY-MM-DD).
TIME: Time only (HH:MM:SS).
DATETIME: Date and time (YYYY-MM-DD HH:MM:SS). Does not include timezone info.
TIMESTAMP: Number of seconds since the Unix Epoch (1970-01-01). Automatically converts to current timezone for display. Excellent for created_at and updated_at fields.

4. Specialized Types

BOOLEAN / BOOL: Synonyms for TINYINT(1). 0 is false, non-zero is true.
ENUM('val1', 'val2'): String object that can have only one value chosen from a predefined list.
JSON: Native JSON support. Allows querying and indexing inside JSON documents.

3. Setup and Installation

Local Installation

Windows / macOS: The easiest way is to download the MySQL Installer from the official website. Alternatively, tools like XAMPP or MAMP bundle MySQL with Apache and PHP.

Linux (Ubuntu/Debian):

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation # Run this to secure the default installation

Docker Installation (Recommended for Development)

Docker provides an isolated, reproducible environment without cluttering your host OS.

# Pull and run MySQL 8.0
docker run --name my-mysql \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -e MYSQL_DATABASE=mydb \
  -p 3306:3306 \
  -d mysql:8.0

Client Tools

To interact with your MySQL server, you need a client:

CLI: The default mysql command-line tool.
```
mysql -u root -p
```
GUI Clients:
- MySQL Workbench: Official GUI, powerful for design and administration.
- DBeaver: Excellent, open-source universal database tool.
- TablePlus: Modern, lightweight native client (macOS/Windows).

4. SQL Basics (CRUD Operations)

CRUD stands for Create, Read, Update, and Delete. These are the four basic functions of persistent storage.

DDL: Data Definition Language (Creating the Schema)

Before manipulating data, you must define the structure.

-- 1. Create a database
CREATE DATABASE IF NOT EXISTS company_db;

-- 2. Select the database to use
USE company_db;

-- 3. Create a table
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. CREATE (Inserting Data)

Use the INSERT INTO statement to add new rows.

-- Insert a single row
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES ('John', 'Doe', 'john.doe@example.com', 'Engineering', 85000.00, '2023-01-15');

-- Insert multiple rows at once (Batch Insert)
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES 
    ('Jane', 'Smith', 'jane.smith@example.com', 'Marketing', 75000.00, '2023-02-01'),
    ('Alice', 'Johnson', 'alice.j@example.com', 'Engineering', 92000.00, '2022-11-10'),
    ('Bob', 'Brown', 'bob.b@example.com', 'Sales', 60000.00, '2023-03-20');

2. READ (Querying Data)

The SELECT statement is the most frequently used command in SQL.

-- Retrieve all columns for all rows
SELECT * FROM employees;

-- Retrieve specific columns
SELECT first_name, last_name, email FROM employees;

-- Filtering with WHERE
SELECT * FROM employees WHERE department = 'Engineering';

-- Filtering with operators (>, <, >=, <=, !=, <>)
SELECT * FROM employees WHERE salary > 80000;

-- Logical Operators (AND, OR, NOT)
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 85000;

-- Pattern Matching with LIKE (% matches any sequence of chars, _ matches a single char)
SELECT * FROM employees WHERE last_name LIKE 'Smi%'; -- Starts with Smi

-- IN Operator (shorthand for multiple ORs)
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

-- Handling NULL values (Use IS NULL, not = NULL)
SELECT * FROM employees WHERE department IS NULL;

-- Sorting Results (ORDER BY)
SELECT * FROM employees ORDER BY salary DESC; -- Highest to lowest
SELECT * FROM employees ORDER BY department ASC, last_name ASC;

-- Limiting Results (LIMIT and OFFSET for pagination)
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 5; -- Top 5 newest
SELECT * FROM employees LIMIT 10 OFFSET 20; -- Skip 20, take 10 (Page 3)

3. UPDATE (Modifying Data)

Use the UPDATE statement to change existing data.

⚠️ DANGER: ALWAYS use a WHERE clause with UPDATE unless you intend to modify every single row in the table.

-- Update a specific record
UPDATE employees 
SET salary = 88000.00, department = 'Senior Engineering'
WHERE id = 1;

-- Update multiple records based on a condition
UPDATE employees
SET salary = salary * 1.05 -- 5% raise
WHERE department = 'Engineering';

4. DELETE (Removing Data)

Use the DELETE statement to remove rows.

⚠️ DANGER: ALWAYS use a WHERE clause with DELETE. Without it, you will empty the table.

-- Delete a specific record
DELETE FROM employees WHERE id = 4;

-- Delete records matching a condition
DELETE FROM employees WHERE hire_date < '2020-01-01';

-- TRUNCATE: Empty an entire table rapidly and reset AUTO_INCREMENT
-- (Faster than DELETE FROM, cannot be rolled back easily)
TRUNCATE TABLE employees;

5. Constraints

Constraints are rules applied to columns to enforce data integrity and accuracy. They prevent invalid data from being entered into the database.

1. PRIMARY KEY

Uniquely identifies each record in a table. A table can have only one primary key, and its columns cannot contain NULL values.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (user_id)
);
-- Or defined inline: user_id INT AUTO_INCREMENT PRIMARY KEY

2. FOREIGN KEY

A field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. This establishes a relationship and enforces referential integrity.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- This will link to the users table
    total_amount DECIMAL(10,2),
    -- Define the relationship
    FOREIGN KEY (user_id) REFERENCES users(user_id) 
        ON DELETE CASCADE -- If the user is deleted, delete their orders
        ON UPDATE RESTRICT -- Prevent updating the user_id if orders exist
);

Foreign Key Actions:

CASCADE: Automatically update/delete dependent rows.
RESTRICT / NO ACTION: Prevent the update/delete if dependents exist.
SET NULL: Set the foreign key column to NULL when the parent is deleted.

3. UNIQUE

Ensures that all values in a column are distinct. Unlike a primary key, a table can have multiple unique constraints, and they can often accept a single NULL value.

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE -- No two accounts can have the same email
);

4. NOT NULL

Ensures that a column cannot have a NULL value. A value MUST be provided during an INSERT.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- Name is required
    description TEXT -- Description is optional (can be NULL)
);

5. CHECK

Ensures that the values in a column satisfy a specific boolean expression.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT,
    CONSTRAINT chk_age CHECK (age >= 18) -- Employee must be an adult
);

6. DEFAULT

Provides a default value for a column if none is specified during an INSERT.

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    status VARCHAR(20) DEFAULT 'PENDING'
);

6. Joins

In relational databases, data is often split across multiple tables to reduce redundancy (normalization). JOIN clauses are used to combine rows from two or more tables based on a related column between them.

Let's establish two tables for our examples:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT, -- Foreign key to authors
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors (author_id, name) VALUES (1, 'J.K. Rowling'), (2, 'George R.R. Martin'), (3, 'Tolkien'), (4, 'Stephen King');
INSERT INTO books (book_id, title, author_id) VALUES (101, 'Harry Potter', 1), (102, 'Game of Thrones', 2), (103, 'The Hobbit', 3), (104, 'The Silmarillion', 3), (105, 'Unnamed Book', NULL);

1. INNER JOIN

Returns records that have matching values in both tables. This is the most common join.

-- Get books and their authors. Books without authors, and authors without books are excluded.
SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

2. LEFT (OUTER) JOIN

Returns all records from the left table, and the matched records from the right table. The result is NULL from the right side if there is no match.

-- Get ALL books, and their authors if they have one.
-- 'Unnamed Book' will show up, but its author 'name' will be NULL.
SELECT books.title, authors.name
FROM books
LEFT JOIN authors ON books.author_id = authors.author_id;

3. RIGHT (OUTER) JOIN

Returns all records from the right table, and the matched records from the left table. Result is NULL from the left side when there is no match.

-- Get ALL authors, and the books they wrote.
-- 'Stephen King' will show up, but 'title' will be NULL because he has no books in our DB.
SELECT books.title, authors.name
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id;

4. FULL OUTER JOIN

Returns all records when there is a match in either left or right table. Note: MySQL does not have a native FULL OUTER JOIN syntax. You simulate it using a UNION of a LEFT JOIN and a RIGHT JOIN.

SELECT books.title, authors.name
FROM books
LEFT JOIN authors ON books.author_id = authors.author_id
UNION
SELECT books.title, authors.name
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id;

5. Using Table Aliases

When joining tables, column names can get long and ambiguous. Use aliases (AS keyword, or just space) for cleaner queries.

SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id;

7. Indexing (Basic Intro)

What is an Index?

Imagine a database table as a thick textbook. If you want to find a specific topic, scanning page by page (a "Full Table Scan") is extremely slow. An index is like the textbook's index at the back—it provides a fast lookup mechanism pointing directly to the data's location.

Internally, MySQL typically uses B-Tree (Balanced Tree) structures for indexes.

Why use Indexes?

Massively speeds up SELECT queries (especially filtering with WHERE, sorting with ORDER BY, and JOIN conditions).
Enforces uniqueness (Unique Indexes).

The Trade-off

Write Performance Penalty: Every time you INSERT, UPDATE, or DELETE a row, the index must also be updated. Over-indexing slows down database writes.
Storage Cost: Indexes consume additional disk space.

Creating and Dropping Indexes

-- Primary Keys and Unique constraints automatically create indexes.

-- Create a standard index on a column frequently used in WHERE clauses
CREATE INDEX idx_last_name ON employees (last_name);

-- Create a composite index (indexing multiple columns together)
-- Useful if you frequently query BOTH columns together: WHERE department = 'Sales' AND salary > 50000
CREATE INDEX idx_dept_salary ON employees (department, salary);

-- Show existing indexes on a table
SHOW INDEX FROM employees;

-- Drop an index
DROP INDEX idx_last_name ON employees;

Best Practices for Indexing

Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
Don't index tables with very few rows (a full table scan is faster than reading the index and then the table).
Don't index columns with low "cardinality" (few unique values, like a boolean is_active column).
Regularly review unused indexes and drop them.

8. Transactions (Basic Intro)

A transaction is a sequence of SQL operations treated as a single logical unit of work. Transactions ensure that either all operations within the unit succeed, or none of them do.

ACID Properties

Transactions guarantee database integrity through four principles:

Atomicity: All or nothing. If one step fails, the entire transaction is rolled back.
Consistency: The database transitions from one valid state to another. Constraints are enforced.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Once committed, changes are permanent, even in the event of a system crash.

Transaction Commands

START TRANSACTION or BEGIN: Starts a new transaction.
COMMIT: Saves all changes made during the transaction permanently.
ROLLBACK: Undoes all changes made during the transaction.

Example: A Bank Transfer

Imagine transferring $100 from Alice to Bob. This requires two updates. If the server crashes after deducting from Alice but before adding to Bob, the money is lost. Transactions solve this.

-- Assume table: accounts (id, balance)

START TRANSACTION;

-- Step 1: Deduct from Alice (id = 1)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- If something goes wrong here...
-- Step 2: Add to Bob (id = 2)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both succeeded, save the changes
COMMIT;

-- OR, if an error occurred in your application logic after step 1, 
-- you would execute ROLLBACK instead of COMMIT to undo step 1.
-- ROLLBACK;

9. Schema Design

Good database design is the foundation of a scalable application. Poor design leads to slow queries, data anomalies, and messy application code.

Database Normalization

Normalization is the process of organizing data to minimize redundancy.

1st Normal Form (1NF): Each column should contain atomic (indivisible) values. No comma-separated lists in a single column.
2nd Normal Form (2NF): Must be in 1NF. Every non-key column must be fully dependent on the primary key.
3rd Normal Form (3NF): Must be in 2NF. No transitive dependencies (a non-key column should not depend on another non-key column).

Rule of thumb: Don't store the same piece of information in multiple places. If a user's address changes, you should only have to update it in one row.

Relationships

1. One-to-One (1:1)

A record in Table A is related to only one record in Table B. Example: User and UserProfile. Implementation: Place a UNIQUE foreign key in one of the tables.

2. One-to-Many (1:N) - The most common

A record in Table A relates to multiple records in Table B. Example: Author has many Books. Implementation: Place the Foreign Key in the "Many" table (Books gets an author_id).

3. Many-to-Many (M:N)

Records in Table A relate to multiple records in Table B, and vice versa. Example: Students and Courses. A student takes many courses; a course has many students. Implementation: Requires a Join Table (or Junction Table) containing the primary keys of both tables as foreign keys.

-- Implementing Many-to-Many
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE courses (id INT PRIMARY KEY, title VARCHAR(50));

-- Join Table
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id), -- Composite Primary Key
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Naming Conventions

Tables: Use lowercase, plural nouns (users, products, orders). Use snake_case for multi-word tables (order_items).
Columns: Use lowercase, snake_case (first_name, created_at).
Primary Keys: Just use id (integer/UUID).
Foreign Keys: Singular table name followed by _id (user_id, product_id).

10. Performance Optimization

Database performance often becomes the bottleneck as applications grow.

1. The EXPLAIN Statement

The most important tool for optimization. Prefix any SELECT query with EXPLAIN to see how MySQL plans to execute it.

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

Look at the type column:

ALL: Full table scan (Bad for large tables).
index: Full index scan.
range: Index range scan (Good).
ref / eq_ref / const: Index lookups (Excellent).

If you see ALL on a large table, you likely need an index.

2. Avoid `SELECT *`

Only query the columns you need. SELECT * transfers unnecessary data over the network and prevents the database from using covering indexes (where all requested data is in the index itself).

3. The N+1 Query Problem

A common issue in ORMs. Occurs when you query a list of items (1 query), and then loop through them, executing a separate query to fetch related data for each item (N queries).

Bad:

SELECT * FROM authors; -- Returns 100 authors
-- Application loops 100 times:
SELECT * FROM books WHERE author_id = 1;
SELECT * FROM books WHERE author_id = 2; ...

Good (Use JOINs):

SELECT a.name, b.title 
FROM authors a 
LEFT JOIN books b ON a.author_id = b.author_id;

4. Efficient Pagination

Standard LIMIT OFFSET becomes extremely slow on deep pages (e.g., OFFSET 1000000 LIMIT 20) because MySQL must fetch 1,000,020 rows and discard the first million.

Solution: Cursor-based Pagination (Seek Method) Remember the last ID seen on the previous page and use a WHERE clause.

-- Much faster for deep pagination
SELECT * FROM transactions 
WHERE id > 1000000 
ORDER BY id ASC LIMIT 20;

11. Security Basics

Securing your database is critical to prevent data breaches.

1. User Management and Least Privilege

Never connect your application using the root user. Create specific users with only the permissions they need (Principle of Least Privilege).

-- Create a user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
-- Or allow connection from any host (less secure, needed for docker/cloud)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

-- Grant privileges (Only SELECT, INSERT, UPDATE, DELETE on a specific DB)
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON company_db.* FROM 'app_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

2. Preventing SQL Injection

SQL Injection occurs when user input is directly concatenated into an SQL string, allowing attackers to execute malicious commands.

Vulnerable Code (Do NOT do this):

const userId = req.body.id; // Imagine user inputs: "1 OR 1=1; DROP TABLE users;"
const query = "SELECT * FROM users WHERE id = " + userId;

Prevention: ALWAYS use Prepared Statements (Parameterized Queries). They separate the query structure from the data.

3. Network Security

Do not expose port 3306 to the public internet. Ensure your database is in a private subnet, accessible only by your application servers.
Require SSL/TLS for database connections to encrypt data in transit.

12. MySQL with Node.js

Integrating MySQL with a Node.js backend is commonly done using the mysql2 package. It provides support for Promises and Prepared Statements.

Installation

npm install mysql2

Basic Implementation with Connection Pools

Do not create a new connection for every request. Use a Connection Pool, which maintains a set of open connections to be reused, dramatically improving performance.

// db.js
const mysql = require('mysql2/promise');

// Create the connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  password: 'strong_password',
  database: 'company_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool;

Executing Queries (CRUD)

// userController.js
const pool = require('./db');

async function getUsers() {
    try {
        // Simple query
        const [rows, fields] = await pool.query('SELECT id, username, email FROM users');
        return rows;
    } catch (error) {
        console.error('Error fetching users:', error);
    }
}

async function getUserById(id) {
    try {
        // Prepared Statement (Prevents SQL Injection)
        // The '?' acts as a placeholder
        const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
        return rows[0]; // Return the single user object
    } catch (error) {
         console.error('Error fetching user:', error);
    }
}

async function createUser(username, email, passwordHash) {
    try {
        const query = 'INSERT INTO users (username, email, password) VALUES (?, ?, ?)';
        // Pass values in an array matching the order of the '?'
        const [result] = await pool.query(query, [username, email, passwordHash]);
        console.log('User created with ID:', result.insertId);
        return result.insertId;
    } catch (error) {
         console.error('Error creating user:', error);
    }
}

13. Best Practices

Use UTF-8: Set your database, table, and connection charset to utf8mb4 (which supports emojis and full Unicode), not utf8 (which is incomplete in MySQL).
Soft Deletes: Instead of permanently deleting records (DELETE), add a deleted_at timestamp or is_deleted boolean. This prevents accidental data loss and preserves history.
Keep Logic in Application: Avoid complex Triggers and Stored Procedures if possible. Keep business logic in your application code (Node.js, Python, etc.) where it's easier to version control, test, and scale.
Use Connection Pooling: Always use connection pools in your backend applications.
Always specify columns in INSERT: Don't use INSERT INTO table VALUES (...). Always explicitly state columns: INSERT INTO table (col1, col2) VALUES (...). This prevents code breakage if a column is added to the table later.
Store Dates in UTC: Configure your server and application to store all timestamps in UTC. Convert to local timezones only on the frontend/client side.
Backup Regularly: Implement automated, verified backups. Use mysqldump or volume snapshots.

14. Common Mistakes

Ignoring Indexes: Resulting in incredibly slow applications as data grows.
Using FLOAT for Currency: Leads to rounding errors. Always use DECIMAL.
Concatenating SQL Strings: The #1 cause of SQL injection vulnerabilities.
Not using Transactions for Multi-Step Writes: Leaving the database in an inconsistent state on application crash.
Storing Passwords in Plaintext: ALWAYS hash passwords using algorithms like bcrypt or Argon2 before storing them.
Over-Indexing: Creating an index for every column, severely degrading write performance.

15. Real-world Examples

Example: Relational E-Commerce Schema

Here is a practical script to generate a robust schema for a basic e-commerce application, demonstrating data types, constraints, and relationships.

-- 1. Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Products Table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL, -- Stock Keeping Unit
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL, -- DECIMAL for exact monetary values
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 3. Orders Table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 4. Order Items Table (Many-to-Many Join Table with extra data)
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL, -- Price at the time of purchase
    PRIMARY KEY (order_id, product_id), -- Composite PK
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

Example: E-Commerce Operations

1. Creating an Order (Transaction Required)

When a user places an order, we must create the order, add the items, and deduct inventory. This MUST be a transaction.

START TRANSACTION;

-- Variable to simulate input
SET @user_id = 1;
SET @product_id = 14;
SET @qty_bought = 2;
SET @current_price = 29.99;

-- Check inventory (Application logic usually does this, but illustrating DB side)
-- Deduct stock
UPDATE products 
SET stock_quantity = stock_quantity - @qty_bought 
WHERE id = @product_id AND stock_quantity >= @qty_bought;

-- If rows affected is 0, rollback (out of stock)

-- Create Order
INSERT INTO orders (user_id, total_amount, shipping_address)
VALUES (@user_id, (@qty_bought * @current_price), '123 Main St');

-- Get the ID of the order we just inserted
SET @new_order_id = LAST_INSERT_ID();

-- Add Order Item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@new_order_id, @product_id, @qty_bought, @current_price);

COMMIT;

2. Analytics Query: Top Spending Users

SELECT 
    u.id, 
    u.first_name, 
    u.last_name, 
    SUM(o.total_amount) as total_spent,
    COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'delivered'
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 10;

End of Guide. May your queries be fast and your data secure.

FilesExpand file tree

MySQL.md

Latest commit

History