Data Types
Every column in a MySQL table has a data type — a declaration of exactly what kind of value it's allowed to hold and how MySQL should store, compare, and validate it. Picking the right type up front saves real pain later: the wrong choice can silently waste storage, round numbers incorrectly, or make perfectly reasonable data impossible to insert. This lesson covers the categories you'll actually use while building StreamHub's tables in the next lesson.
Numeric Types
| Type | Use it for |
|---|---|
INT | Whole numbers of typical size — ids, counts, a release year, a duration in minutes |
BIGINT | Whole numbers too large for INT — rarely needed until a table has billions of rows |
DECIMAL(10,2) | Exact fixed-point numbers — the right choice whenever money or precise fractional values are involved |
DECIMAL deserves a specific callout. It's tempting to reach for FLOAT
or DOUBLE for anything with a decimal point, but both store numbers as
floating-point approximations — perfectly fine for scientific measurements, genuinely dangerous
for currency. Adding 0.10 to a running total repeatedly with floating-point math
can drift away from the mathematically correct answer by tiny amounts that compound over time.
DECIMAL(10,2) stores the exact value instead — 10 total digits, 2 of them after
the decimal point — with no rounding surprises. If StreamHub ever charged for a premium
subscription, that price column would be a DECIMAL, never a FLOAT.
String Types
| Type | Use it for |
|---|---|
VARCHAR(n) | Variable-length text up to a maximum of n characters — MySQL only stores the actual length used, plus a small overhead |
CHAR(n) | Fixed-length text, always padded to exactly n characters — suited to values that are always the same length, like a two-letter country code |
TEXT | Long-form text with no practical length limit worth declaring up front — article bodies, free-form comments |
VARCHAR vs. TEXT: Choosing for reviews.comment
A good way to internalize the difference is deciding what type StreamHub's
reviews.comment column should be. A VARCHAR(n) forces you to commit
to a maximum length up front, and MySQL enforces it — insert more characters than the declared
limit and the extra text gets truncated or rejected, depending on the server's strict mode
setting. A movie review could reasonably be a one-word "Amazing!" or several rambling
paragraphs, and there's no natural, safe maximum to guess at. TEXT is the better
fit here specifically because it doesn't ask you to guess a cap — it's built for exactly this
"could be short, could be long, don't want to think about a limit" situation. A
username, by contrast, has an obvious sensible ceiling (nobody needs a 10,000
character username), so VARCHAR(50) is the right call there instead.
Date and Time Types
| Type | Use it for |
|---|---|
DATE | A calendar date with no time component — a signup date, a review date |
DATETIME | A specific date and time, stored exactly as given, with no timezone conversion applied by MySQL |
TIMESTAMP | A specific date and time, stored internally in UTC and converted on the way in and out based on the connection's timezone setting |
The DATETIME vs. TIMESTAMP distinction is genuinely important and a
common source of confusion. Insert the same literal value into a DATETIME column
and MySQL stores exactly that value, full stop — no conversion, no adjustment, ever. Insert the
same value into a TIMESTAMP column and MySQL converts it to UTC for storage based
on whatever timezone the current connection is set to, then converts it back when you read it —
which means the value you read back can differ if you query it from a connection set to a
different timezone than the one that inserted it. For StreamHub, straightforward columns like
signup_date and review_date are fine as plain DATE, since
time-of-day and timezone precision genuinely don't matter for them.
Boolean Values
MySQL doesn't have a true, dedicated boolean type. BOOLEAN and its shorthand
BOOL exist as keywords you can type in a CREATE TABLE statement, but
MySQL silently treats them as nothing more than an alias for TINYINT(1) — a tiny
integer column that conventionally stores 0 for false and 1 for true.
There's no dedicated true/false storage happening under the hood; it's an integer column with a
friendlier name.
ENUM: A Fixed Set of Allowed Strings
ENUM lets a column accept only one value from a fixed list you declare up front.
If StreamHub tracked a viewing status on the watchlist, it might look like this:
status ENUM('want_to_watch', 'watching', 'watched')
That's convenient, but worth using with your eyes open: ENUM has real limitations
once the allowed set of values needs to change. Adding or renaming a value later means altering
the table's structure directly, which isn't always as simple as changing application code would
be — a plain VARCHAR with validation handled in application code is sometimes the
more flexible long-term choice for values you expect to evolve.
A Practical Guide for the StreamHub Schema
With all of that in mind, here's what the next lesson's tables will actually use:
| Column | Type | Why |
|---|---|---|
users.signup_date | DATE | Only the calendar date matters, no time-of-day precision needed |
movies.duration_minutes | INT | A whole number of minutes — no fractional minutes to worry about |
reviews.rating | INT | A simple whole-number score (for example 1–5), not a currency-style value needing DECIMAL |
reviews.comment | TEXT | Length varies too much to force a sensible VARCHAR cap, as covered above |