Data types

Every column has a data type that decides what it can hold, how much space it takes and how it compares and sorts. Picking good types is one of the cheapest ways to keep data correct and queries fast. This lesson covers the types you'll use 95% of the time in MySQL 8.

Integers#

Add UNSIGNED when negatives make no sense (ids, quantities) to double the positive range. INT is fine for most ids. Use BIGINT for tables that could pass 2 billion rows, or for ids generated by other systems.

With the default strict SQL mode, values that don't fit are rejected, not silently changed:

CREATE TABLE ints (small TINYINT, qty SMALLINT UNSIGNED);
INSERT INTO ints VALUES (200, 5);

ERROR 1264 (22003): Out of range value for column 'small' at row 1

You may see old schemas with INT(11). The number in brackets is only a display width. It never limited the range, and MySQL 8 has deprecated it. Just write INT.

Exact vs approximate numbers#

DECIMAL(p, s) stores exact numbers with p total digits, s of them after the decimal point. DECIMAL(10,2) holds up to 99,999,999.99. FLOAT and DOUBLE are approximate binary floating-point numbers, fast and compact, but they can't represent most decimal fractions exactly:

CREATE TABLE prices (d DECIMAL(10,2), f FLOAT, g DOUBLE);
INSERT INTO prices VALUES (0.1, 0.1, 0.1), (0.2, 0.2, 0.2);
SELECT SUM(d), SUM(f), SUM(g), SUM(g) = 0.3 AS double_is_exact FROM prices;

+--------+---------------------+---------------------+-----------------+
| SUM(d) | SUM(f)              | SUM(g)              | double_is_exact |
+--------+---------------------+---------------------+-----------------+
|   0.30 | 0.30000000447034836 | 0.30000000000000004 |               0 |
+--------+---------------------+---------------------+-----------------+

The DECIMAL sum is exactly 0.30; the floating-point sums are not, and 0.30000000000000004 = 0.3 is false (0). Use DECIMAL for money and anything people add up by hand. Use DOUBLE for scientific or measured values where tiny errors don't matter.

Text: CHAR, VARCHAR and TEXT#

n counts characters, not bytes, so VARCHAR(50) holds 50 emoji in utf8mb4 even though each emoji takes 4 bytes. Strict mode also protects text lengths:

CREATE TABLE people (country CHAR(2), name VARCHAR(10));
INSERT INTO people VALUES ('IN', 'Aditya');
INSERT INTO people VALUES ('GB', 'Bartholomew Montgomery');
SELECT country, name, CHAR_LENGTH(name) AS chars FROM people;

ERROR 1406 (22001): Data too long for column 'name' at row 1
+---------+--------+-------+
| country | name   | chars |
+---------+--------+-------+
| IN      | Aditya |     6 |
+---------+--------+-------+

Prefer VARCHAR over TEXT when you can: VARCHAR columns can have ordinary indexes and default values, while TEXT columns need prefix indexes. For binary data (files, hashes) use BINARY(n), VARBINARY(n) or BLOB, though big files usually belong in object storage with only their path in the database.

Dates and times#

Always write dates as 'YYYY-MM-DD' and datetimes as 'YYYY-MM-DD hh:mm:ss'. Add fractional seconds with DATETIME(3) (milliseconds) or DATETIME(6) (microseconds).

The big difference is time zones. A TIMESTAMP is converted from your session's time zone to UTC when stored, and back when read. A DATETIME is never converted:

CREATE TABLE events (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO events VALUES ('2026-09-30 12:00:00', '2026-09-30 12:00:00');
SET time_zone = '+05:30';
SELECT dt, ts FROM events;

+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2026-09-30 12:00:00 | 2026-09-30 17:30:00 |
+---------------------+---------------------+

The same stored ts now reads as 17:30 because we're viewing it from UTC+5:30. Many teams store everything as UTC in DATETIME and convert in the application, which also avoids TIMESTAMP's year-2038 limit.

ENUM, SET and BOOLEAN#

ENUM restricts a column to a fixed list of strings:

CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('open', 'in_progress', 'closed') NOT NULL DEFAULT 'open',
    urgent BOOLEAN NOT NULL DEFAULT FALSE
);
INSERT INTO tickets (status, urgent) VALUES ('closed', TRUE), (DEFAULT, DEFAULT);
INSERT INTO tickets (status) VALUES ('archived');
SELECT * FROM tickets;

ERROR 1265 (01000): Data truncated for column 'status' at row 1
+----+--------+--------+
| id | status | urgent |
+----+--------+--------+
|  1 | closed |      1 |
|  2 | open   |      0 |
+----+--------+--------+

'archived' isn't in the list, so strict mode rejects the row. The message says Data truncated, which is MySQL's slightly odd way of saying "this value doesn't fit the column". The second row used DEFAULT for both columns.

BOOLEAN is just an alias for TINYINT(1): TRUE is 1 and FALSE is 0. ENUM is compact and self-documenting, but adding a value means an ALTER TABLE, and values sort by their position in the list rather than alphabetically. For lists that change often, use a small lookup table with a foreign key (see Constraints). SET is like ENUM but allows several values at once. It's rarely a good idea, because a separate table is easier to query.

JSON#

MySQL 8 has a native JSON type that validates documents on insert and stores them in a fast binary format:

CREATE TABLE settings (user_id INT PRIMARY KEY, prefs JSON);
INSERT INTO settings VALUES (1, '{"theme": "dark", "langs": ["en", "hi"]}');
SELECT prefs->>'$.theme' AS theme FROM settings;

+-------+
| theme |
+-------+
| dark  |
+-------+

Invalid JSON is rejected with an error. You'll learn JSON properly in JSON columns.

Choosing types: a cheat sheet#

Common mistakes#

• Storing numbers or dates as text. '10' < '9' is true for strings! Store numbers in numeric types and dates in date types so sorting, comparisons and date maths work.
• Phone numbers and postcodes as integers. They aren't quantities, and they can have leading zeros or +. Use VARCHAR.
• FLOAT for money. Rounding errors add up, so use DECIMAL.
• Oversized types everywhere. VARCHAR(255) for a two-letter code is legal, but it hides intent and makes in-memory temporary tables larger.

What's next#

With tables and types in place, it's time to put data in, change it and take it out again: INSERT, UPDATE and DELETE.