Every column declares what it can hold and how much space that costs. Push values past the edge of a type, watch FLOAT lose money that DECIMAL keeps, and see why the right type is a correctness decision, not a formality.
Choosing a type decides what values are legal, how much disk they consume, and whether your arithmetic is exact.
A datatype declares what a column may contain. The database uses it to reject invalid data, to decide how many bytes each value occupies, and to choose how comparisons and arithmetic behave. Get it wrong and you pay in storage, in speed, or — worst — in silently incorrect numbers.
Integer types differ only in width, and width sets the range. TINYINT holds −128 to 127 in one byte; INT covers roughly ±2.1 billion in four; BIGINT uses eight.
The classic production incident is an INT primary key on a fast-growing table. At 2,147,483,647 rows the next insert fails outright — and migrating a hot table's key type is painful. Type 3000000000 into the value box with INT selected and watch the overflow.
FLOAT and DOUBLE are binary floating point. They cannot represent 0.1 exactly, for the same reason base 10 cannot write 1/3 exactly. Errors are tiny individually and accumulate over millions of rows.
The demo above adds 0.10 + 0.20 in both types. FLOAT does not produce 0.30 — it produces something imperceptibly different, and = 0.30 is then false. DECIMAL(10,2) stores the digits themselves and is exact.
Rule: money, quantities and anything a human will audit go in DECIMAL / NUMERIC. Reserve FLOAT for measurements where a rounding error in the fifteenth digit genuinely does not matter.
The n in VARCHAR(n) is mostly a constraint, not an allocation — declaring VARCHAR(255) everywhere does not waste space, but it also fails to document what the column really holds.
Store dates in DATE / TIMESTAMP, never in a string. Only a real date type gives you correct sorting, date arithmetic, and rejection of 2024-02-31. For anything spanning time zones, prefer TIMESTAMP WITH TIME ZONE.
Finally, every type also permits NULL unless you say NOT NULL. NULL means unknown, which is genuinely different from 0 or an empty string — and it propagates through arithmetic and comparisons in ways that surprise people.
Pick the narrowest type that will hold every legitimate value, use DECIMAL for anything monetary, use real date types for dates, and add NOT NULL wherever a missing value would be meaningless. These choices are hard to change later and quietly govern correctness forever.
Every column declares what it can hold and how much space that costs. Push values past the edge of a type, watch FLOAT lose money that DECIMAL keeps, and see why the right type is a correctness decision, not a formality.