Data Types

Types are a contract with the storage engine#

The type you choose decides how many bytes each value takes, how it sorts, and what MySQL will reject. Pick too wide and you waste memory in every index. Pick too narrow and inserts fail at 2 AM.

Integers#

Add UNSIGNED for IDs and counts, which doubles the positive range. The number in INT(11) is only a display width hint and does not limit values. It's a historical leftover; ignore it.

Exact versus approximate numbers#

SELECT 0.1E0 + 0.2E0 = 0.3E0;   -- 0, floating point error
SELECT 0.1 + 0.2 = 0.3;          -- 1, DECIMAL arithmetic is exact

FLOAT and DOUBLE are binary approximations. Never use them for money. Use DECIMAL(10,2): ten total digits, two after the point, stored exactly.

Strings#

• CHAR(n): fixed length, padded. Good for codes like country 'IN'.
• VARCHAR(n): variable length with a 1 or 2 byte length prefix. The default choice.
• TEXT: large text, stored partly off-page. Indexing needs a prefix length.
• ENUM('new','paid','shipped'): stored as a small integer. Compact, but adding a value means an ALTER, and sorting follows definition order, not alphabet.

With utf8mb4, MySQL reserves up to 4 bytes per character when it builds temporary tables and index keys. Oversized VARCHAR(1000) columns you never fill still cost you.

Dates and times#

created_at  DATETIME  DEFAULT CURRENT_TIMESTAMP,
updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
birth_date  DATE

DATETIME stores the literal value you gave it. TIMESTAMP stores UTC internally and converts to the session time zone on read, but it ends in 2038. For a "when did this happen" column, I usually pick DATETIME and store UTC from the application.

DATETIME  : 2025-03-01 10:00:00  ──▶ stored as-is, returned as-is
TIMESTAMP : 2025-03-01 10:00:00 (session +05:30)
            ──▶ stored 04:30:00 UTC ──▶ returned in reader's zone

Other types worth knowing#

BOOLEAN is an alias for TINYINT(1). JSON validates and stores documents. BINARY(16) is the compact way to store a UUID. All of them have trade-offs, but nothing here is magic.

Common mistakes#

• Using FLOAT for prices.
• Storing dates as VARCHAR like '01/03/2025'. You lose range queries and date functions.
• Using utf8 when you meant utf8mb4.
• Choosing ENUM for values that change often.

Practice#

Design the column types for a payments table: id, order reference, amount, currency code, status (four possible values), paid_at, and a raw gateway response. Write the CREATE TABLE, and for each column write one sentence on why you picked that type over its nearest alternative.