Data Types and Schema

Numeric types#

INT can be declared UNSIGNED to double its positive range when negative values will never occur, which is common for auto-incrementing ids:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

DECIMAL for money, never FLOAT#

DECIMAL(10,2) stores an exact decimal value — 10 total digits, 2 after the decimal point — because it's stored as a fixed-point representation rather than binary floating-point. FLOAT/DOUBLE store an approximation of most decimal fractions, because computers represent floating-point numbers in binary, and many ordinary decimal fractions (0.1, 0.2, 0.3...) have no exact binary representation:

-- Illustrating the problem (not MySQL-specific — this affects any binary float)
SELECT 0.1 + 0.2;  -- FLOAT arithmetic: not guaranteed to be exactly 0.3

For a single calculation the error is tiny — a fraction of a cent — but it compounds across millions of transactions, and worse, it can cause = comparisons to fail unexpectedly (price = 19.99 might not match a value that's actually stored as 19.990000000000002). DECIMAL has no such issue because it stores digits directly rather than a binary approximation:

price DECIMAL(10,2) NOT NULL   -- exact: up to 99999999.99

Always use DECIMAL for money, prices, account balances, or any value where exact arithmetic matters. Reserve FLOAT/DOUBLE for scientific measurements or anything where small approximation error is genuinely acceptable.

Text types#

CREATE TABLE articles (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200) NOT NULL,
    slug    VARCHAR(200) NOT NULL UNIQUE,
    body    TEXT NOT NULL
);

Pick VARCHAR with a realistic length cap for anything short and structured (names, emails, slugs); reach for TEXT only once content can genuinely run long and unbounded, since TEXT/BLOB columns are stored somewhat differently on disk and can't be fully indexed the same way a VARCHAR can.

ENUM#

ENUM restricts a column to a fixed list of string values, stored internally as a compact integer — useful for a small, stable set of states:

CREATE TABLE orders (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('pending', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('shipped');
UPDATE orders SET status = 'refunded' WHERE id = 1;  -- error: not a valid ENUM value

The trade-off: adding a new status later requires an ALTER TABLE to extend the enum's allowed list, which some teams find more friction than it's worth — a plain VARCHAR with an application-level or CHECK-constraint validation is a common alternative when the set of values is expected to change.

TIMESTAMP vs DATETIME#

Both store a date and time down to the second (optionally with fractional seconds), but they differ in range and timezone handling:

CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ON UPDATE CURRENT_TIMESTAMP is a MySQL-specific convenience that automatically refreshes a column every time the row is updated — a common pattern for updated_at audit columns, avoiding the need for application code (or a Laravel model, outside this raw-SQL context) to set it manually. The 2038 ceiling on TIMESTAMP (a 32-bit signed integer counting seconds since 1970, the same underlying limit as the old Unix "Year 2038 problem") is a real reason to prefer DATETIME for far-future dates.

ALTER TABLE#

ALTER TABLE changes an existing table's structure without recreating it:

-- Add a column
ALTER TABLE products ADD COLUMN description TEXT;

-- Modify a column's type
ALTER TABLE products MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Rename a column (and optionally change its type in the same statement)
ALTER TABLE products CHANGE COLUMN name product_name VARCHAR(200) NOT NULL;

-- Drop a column
ALTER TABLE products DROP COLUMN description;

-- Add an index
ALTER TABLE products ADD INDEX idx_products_category (category);

-- Add a foreign key to an existing table
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id);

MODIFY changes a column's definition but keeps its name; CHANGE can rename it and change its definition in one statement. On a very large table, ALTER TABLE can lock the table (or run as an online, non-blocking operation, depending on the specific change and MySQL version) — worth checking ALGORITHM=INPLACE support and testing on a copy before running a schema change against a large production table.

Common mistakes#

• Storing money as FLOAT because it "looks like a number" — this is the single most common MySQL data-type mistake, and it silently produces wrong totals that only surface once amounts are summed across many rows.
• Using TIMESTAMP for a date meant to display the same everywhere regardless of the reader's timezone (a flight departure time, a historical event date) — its automatic UTC conversion actively works against that goal.
• Over-using ENUM for values that change frequently, then fighting ALTER TABLE every time the business adds a new status.
• Defaulting every text column to VARCHAR(255) out of habit rather than sizing it to the data it actually holds.