Lesson 05 beginner

MySQL Data Types

Pick the right type for every column: integers and their ranges, DECIMAL for money versus FLOAT and DOUBLE, CHAR, VARCHAR and TEXT, BINARY and BLOB, DATE, TIME, DATETIME and TIMESTAMP with time zones and the 2038 limit, BOOLEAN, ENUM and SET, BIT, JSON, UUIDs stored compactly, generated columns, and how strict SQL mode protects you from silent data loss.

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

TypeBytesSigned rangeUnsigned range
TINYINT1-128 to 1270 to 255
SMALLINT2-32,768 to 32,7670 to 65,535
MEDIUMINT3about ±8.4 million0 to about 16.7 million
INT4about ±2.1 billion0 to about 4.29 billion
BIGINT8about ±9.2 quintillion0 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

TypeMax lengthNotes
CHAR(n)255 charactersFixed length, padded with spaces; good for codes of known length such as country codes
VARCHAR(n)65,535 bytes per row in totalVariable length; the everyday choice for names, emails, titles
TEXT64 KBLong text such as note bodies
MEDIUMTEXT16 MBArticles, documents
LONGTEXT4 GBRarely 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

TypeExampleRangeNotes
DATE2026-11-11years 1000 to 9999A calendar day
TIME14:30:00±838 hoursTime of day or a duration
DATETIME2026-11-11 14:30:00years 1000 to 9999Stored as written, no time zone
TIMESTAMP2026-11-11 14:30:001970 to 2038-01-19Stored as UTC, converted to the session time zone
YEAR20261901 to 2155Just 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:

  • TIMESTAMP ends on 19 January 2038. Anything that may go beyond (expiry dates, schedules, birthdays of future users) must be DATETIME.
  • A common and robust convention is DATETIME columns that always hold UTC, with the application converting for display. Orbit follows it: its servers run with time_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

ColumnTypeWhy
users.id, notes.idINT UNSIGNED AUTO_INCREMENTCompact, ordered, fits billions of rows
users.emailVARCHAR(255)Indexed and unique, bounded length
users.planENUM('free','pro')Two values that rarely change
notes.titleVARCHAR(200)Shown in lists and searched
notes.bodyTEXTFree-form, can be long
notes.pinnedBOOLEANYes/no flag
*_atDATETIME in UTCNo 2038 limit, no hidden conversion
users.settingsJSONPreferences whose keys change over time

Quick reference

NeedType
Ids, countersINT UNSIGNED / BIGINT UNSIGNED
MoneyDECIMAL(12, 2)
MeasurementsDOUBLE
Short textVARCHAR(n)
Long textTEXT / MEDIUMTEXT
Point in timeDATETIME(3) in UTC, or TIMESTAMP before 2038
Yes/noBOOLEAN
Fixed small listENUM(...) or a lookup table
Flexible documentJSON
UUIDBINARY(16) with UUID_TO_BIN(..., 1)

Exercise

Which column type should store order totals such as 19.99?

  1. FLOAT
  2. DOUBLE
  3. VARCHAR(20)
  4. DECIMAL(12, 2)
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?

  1. DATETIME
  2. TIMESTAMP
  3. TIME
  4. YEAR
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?

  1. The value is truncated to 200 characters with a warning
  2. The column grows to fit the value
  3. The insert fails with error 1406, Data too long
  4. The value is stored in an overflow page
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, UNSIGNED for ids, and forget display widths like INT(11).
  • Money goes in DECIMAL; FLOAT and DOUBLE are approximate.
  • VARCHAR(n) counts characters; use TEXT for long free-form content.
  • Prefer DATETIME in UTC; TIMESTAMP converts time zones and stops in 2038.
  • BOOLEAN is TINYINT(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.

↑ ↓ navigate Enter open Esc close