The type of a column decides what values it accepts, how much space they take and how fast comparisons and indexes are. Choosing well up front saves painful migrations later. This lesson walks through the types you will actually use.
Integers
| Type | Bytes | Signed range | Unsigned range |
|---|---|---|---|
TINYINT | 1 | -128 to 127 | 0 to 255 |
SMALLINT | 2 | -32,768 to 32,767 | 0 to 65,535 |
MEDIUMINT | 3 | about ±8.4 million | 0 to about 16.7 million |
INT | 4 | about ±2.1 billion | 0 to about 4.29 billion |
BIGINT | 8 | about ±9.2 quintillion | 0 to about 18.4 quintillion |
Use UNSIGNED for values that can never be negative, such as ids and counters. The number in INT(11) that you see in old schemas is only a display width, has no effect on the range and is deprecated; just write INT.
What happens when a value does not fit?
CREATE TABLE t_demo (small TINYINT UNSIGNED);
INSERT INTO t_demo VALUES (300);
ERROR 1264 (22003): Out of range value for column 'small' at row 1
That error comes from strict SQL mode, the default since MySQL 5.7. Without it, MySQL would store 255 and print a warning, silently changing your data. Keep strict mode on; more on that at the end of the lesson.
Exact and approximate numbers
DECIMAL(p, s) stores exact decimal numbers: p digits in total, s of them after the point. DECIMAL(10, 2) holds up to 99,999,999.99. Use it for money and anything that must add up exactly.
FLOAT (4 bytes) and DOUBLE (8 bytes) are binary floating point: fast and compact, but they cannot represent most decimal fractions exactly.
SELECT 0.1 + 0.2 = 0.3 AS decimal_math,
0.1e0 + 0.2e0 = 0.3e0 AS double_math;
+--------------+-------------+
| decimal_math | double_math |
+--------------+-------------+
| 1 | 0 |
+--------------+-------------+
1 row in set (0.00 sec)
Literals like 0.1 are exact decimals in MySQL, so the first comparison is true. Written in scientific notation they become DOUBLE, and the classic rounding error appears. Use DOUBLE for measurements and scientific values, never for prices.
Text
| Type | Max length | Notes |
|---|---|---|
CHAR(n) | 255 characters | Fixed length, padded with spaces; good for codes of known length such as country codes |
VARCHAR(n) | 65,535 bytes per row in total | Variable length; the everyday choice for names, emails, titles |
TEXT | 64 KB | Long text such as note bodies |
MEDIUMTEXT | 16 MB | Articles, documents |
LONGTEXT | 4 GB | Rarely needed |
n counts characters, not bytes. With utf8mb4 a character takes up to 4 bytes, so VARCHAR(255) may use up to 1,020 bytes.
When should you choose TEXT over VARCHAR? Use VARCHAR with a sensible limit for anything you filter, sort or index, because it can be fully indexed and can have a normal default. Use TEXT for free-form content you mostly just store and display. TEXT columns can only be indexed with a prefix length or a full-text index, and need an expression default such as DEFAULT ('').
CREATE TABLE s_demo (title VARCHAR(200));
INSERT INTO s_demo VALUES (REPEAT('x', 300));
ERROR 1406 (22001): Data too long for column 'title' at row 1
Binary data
BINARY(n), VARBINARY(n) and the BLOB family mirror the text types but store raw bytes with no character set; comparisons are byte by byte. Use them for hashes, encrypted values and compact identifiers. Store files such as images in object storage and keep only their path or URL in the database.
Dates and times
| Type | Example | Range | Notes |
|---|---|---|---|
DATE | 2026-11-11 | years 1000 to 9999 | A calendar day |
TIME | 14:30:00 | ±838 hours | Time of day or a duration |
DATETIME | 2026-11-11 14:30:00 | years 1000 to 9999 | Stored as written, no time zone |
TIMESTAMP | 2026-11-11 14:30:00 | 1970 to 2038-01-19 | Stored as UTC, converted to the session time zone |
YEAR | 2026 | 1901 to 2155 | Just a year |
Add fractional seconds with a precision: DATETIME(3) keeps milliseconds, DATETIME(6) microseconds.
The difference between DATETIME and TIMESTAMP matters as soon as your users or servers are in different time zones:
CREATE TABLE tz_demo (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO tz_demo VALUES ('2026-11-11 12:00:00', '2026-11-11 12:00:00');
SET time_zone = '-03:00';
SELECT dt, ts FROM tz_demo;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 2026-11-11 12:00:00 | 2026-11-11 09:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
TIMESTAMP stored the instant in UTC and displays it in the session’s zone; DATETIME stored exactly what you gave it. Two practical rules:
TIMESTAMPends on 19 January 2038. Anything that may go beyond (expiry dates, schedules, birthdays of future users) must beDATETIME.- A common and robust convention is
DATETIMEcolumns that always hold UTC, with the application converting for display. Orbit follows it: its servers run withtime_zone = '+00:00'.
Booleans
MySQL has no true boolean type: BOOLEAN and BOOL are synonyms for TINYINT(1). TRUE is 1 and FALSE is 0, and any non-zero value is truthy in a condition. That is why pinned shows up as 1 and 0 in results.
ENUM and SET
ENUM('free', 'pro') stores one value from a fixed list in 1 or 2 bytes and rejects anything else:
INSERT INTO users (email, name, plan) VALUES ('test@orbit.dev', 'Test', 'gold');
ERROR 1265 (01000): Data truncated for column 'plan' at row 1
ENUM is compact and self-documenting, but adding a value means an ALTER TABLE (instant when appended at the end), and values sort by their position in the list, not alphabetically. If the list changes often or other tables need extra data about each value, use a lookup table with a foreign key instead. SET is like ENUM but allows several values at once; it is rarely a good idea, since a link table is easier to query.
JSON
A JSON column validates documents on insert and stores them in an efficient binary format, so MySQL can read one key without parsing the whole document:
ALTER TABLE users ADD COLUMN settings JSON;
Orbit will use this column for per-user preferences in lesson 17, which covers querying and indexing JSON. Use JSON for data whose shape varies; keep anything you join, filter often or constrain in regular columns.
UUIDs
If ids must be generated outside the database, UUIDs avoid collisions. Storing them as CHAR(36) text wastes space and, because random UUIDs are scattered, slows inserts into InnoDB’s ordered primary key. Store them in 16 bytes instead, with the time part moved to the front:
CREATE TABLE devices (
id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
name VARCHAR(100) NOT NULL
);
SELECT BIN_TO_UUID(id, 1) AS id, name FROM devices;
The second argument 1 swaps the time fields so that new UUIDs sort roughly in creation order.
Generated columns
A generated column is computed from other columns. A VIRTUAL one is calculated when read; a STORED one is saved on disk. Both can be indexed:
ALTER TABLE users
ADD COLUMN email_domain VARCHAR(255)
AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
Now WHERE email_domain = 'orbit.dev' reads naturally, and with an index on email_domain it is fast. You cannot write to a generated column; MySQL keeps it in sync.
Strict SQL mode
sql_mode controls how forgiving MySQL is. The default in 8.4 and 9.7 is:
SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
STRICT_TRANS_TABLES turns “store something close and warn” into an error for too-long strings, out-of-range numbers and invalid dates. NO_ZERO_DATE rejects the fake date 0000-00-00. Old applications sometimes ask for an empty sql_mode to hide their bugs; fix the data instead, because a lenient mode silently corrupts it.
Clean up the demo tables before moving on:
DROP TABLE IF EXISTS t_demo, s_demo, tz_demo, devices;
SET time_zone = '+00:00';
Choosing types for Orbit
| Column | Type | Why |
|---|---|---|
users.id, notes.id | INT UNSIGNED AUTO_INCREMENT | Compact, ordered, fits billions of rows |
users.email | VARCHAR(255) | Indexed and unique, bounded length |
users.plan | ENUM('free','pro') | Two values that rarely change |
notes.title | VARCHAR(200) | Shown in lists and searched |
notes.body | TEXT | Free-form, can be long |
notes.pinned | BOOLEAN | Yes/no flag |
*_at | DATETIME in UTC | No 2038 limit, no hidden conversion |
users.settings | JSON | Preferences whose keys change over time |
Quick reference
| Need | Type |
|---|---|
| Ids, counters | INT UNSIGNED / BIGINT UNSIGNED |
| Money | DECIMAL(12, 2) |
| Measurements | DOUBLE |
| Short text | VARCHAR(n) |
| Long text | TEXT / MEDIUMTEXT |
| Point in time | DATETIME(3) in UTC, or TIMESTAMP before 2038 |
| Yes/no | BOOLEAN |
| Fixed small list | ENUM(...) or a lookup table |
| Flexible document | JSON |
| UUID | BINARY(16) with UUID_TO_BIN(..., 1) |
Exercise
Which column type should store order totals such as 19.99?
Show answer
Answer: D. DECIMAL stores exact decimal values, so sums and comparisons never suffer binary rounding errors. Floating-point types cannot represent most cents exactly.
Exercise
A subscription table stores expires_at values up to 30 years in the future. Which type is safe?
Show answer
Answer: A. TIMESTAMP cannot store dates after 2038-01-19. DATETIME covers years 1000 to 9999.
Exercise
In strict mode, what happens when you insert a 300-character title into a VARCHAR(200) column?
Show answer
Answer: C. STRICT_TRANS_TABLES turns the truncation warning into an error, so bad data never reaches the table.
Key takeaways
- Use the smallest integer that safely fits,
UNSIGNEDfor ids, and forget display widths likeINT(11). - Money goes in
DECIMAL;FLOATandDOUBLEare approximate. VARCHAR(n)counts characters; useTEXTfor long free-form content.- Prefer
DATETIMEin UTC;TIMESTAMPconverts time zones and stops in 2038. BOOLEANisTINYINT(1). Keep strict SQL mode on.
FAQ
Is VARCHAR(255) slower than VARCHAR(50)?
Storage is the same for the same data, because VARCHAR only stores what you insert. But the declared size matters for in-memory temporary tables and index limits, so choose a realistic maximum rather than 255 everywhere.
How do I store an IP address?
Use VARBINARY(16) with INET6_ATON() and INET6_NTOA(), which handle both IPv4 and IPv6 compactly and make range comparisons possible.
Why does my table show TINYINT(1) when I created a BOOLEAN?
Because BOOLEAN is just an alias. Drivers usually map TINYINT(1) back to a boolean in your programming language, so it behaves as expected in application code.
What does the zero date 0000-00-00 mean and why do I see it in old data?
It was MySQL’s placeholder for “no date” in lenient modes. Modern defaults reject it. When migrating old data, convert zero dates to NULL and make the column nullable.