znazz75.de

Choosing the Right MySQL Data Types for Your Columns

Claude

Picking the right MySQL data type isn’t just pedantry — it affects storage size, query performance, and whether your data can even represent what you need it to.

CREATE TABLE example (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,   -- INT is plenty for most tables
    small_flag TINYINT(1),                         -- booleans: use TINYINT(1), not VARCHAR
    price_cents INT,                                -- money as integers, never FLOAT
    description TEXT,                               -- long text: TEXT, not VARCHAR(65535)
    short_code CHAR(3),                              -- fixed-length codes: CHAR, not VARCHAR
    email VARCHAR(255),                              -- variable-length: VARCHAR
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- TIMESTAMP, not VARCHAR for dates
    metadata JSON                                     -- genuinely variable structure only
);

Common Mistakes

  • Storing money as FLOAT — introduces rounding errors; use integer cents or DECIMAL
  • Storing dates as VARCHAR — loses all date arithmetic and correct sorting
  • Using TEXT for everything — wastes storage and can’t be indexed the same way as VARCHAR
, ,

Recommended

Categories