Choose MySQL column types

Every column type is a promise about what the column stores and how MySQL compares it. Pick each one deliberately, because changing a large table later can be expensive.

Numbers

Use integer types for whole numbers and DECIMAL for exact decimal values. For example, DECIMAL(5,2) stores up to five digits with two after the point, so an estimate such as 12.50 comes back exactly as you stored it.

Avoid FLOAT and DOUBLE for money and quantities you sum. They store binary approximations, and small rounding errors accumulate across additions. DECIMAL exists precisely to avoid that.

Text

Use VARCHAR for bounded text and TEXT for longer content:

title VARCHAR(200) NOT NULL,
body TEXT

The VARCHAR length counts characters, not bytes, and it is a real constraint. In the default strict SQL mode, inserting a 300-character title into VARCHAR(200) fails:

ERROR 1406 (22001): Data too long for column 'title' at row 1

That error is a feature. It tells you at write time that the data does not match the model, instead of silently truncating it.

Dates and times

DATETIME stores the date and time you give it without time-zone conversion. It fits a local wall-clock value such as “2026-08-10 at 09:00 in the user’s chosen zone.” Store the zone separately.

TIMESTAMP converts between the session time zone and UTC. It fits created and updated instants when every connection uses a deliberate time-zone setting. In MySQL 8.4 it also has a smaller supported range than DATETIME: it ends in January 2038, while DATETIME reaches the year 9999.

Here is the pattern in one table:

CREATE TABLE notes (
  estimated_hours DECIMAL(5,2),
  remind_at DATETIME,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

created_at records an instant, so TIMESTAMP with a default fits. remind_at is a wall-clock appointment, so DATETIME fits. Check a column’s actual definition anytime with SHOW CREATE TABLE notes;.