MySQL Data Types: A Practical Overview for Beginners

Every column has a data type. It tells MySQL what kind of value the column holds, how much space to reserve and what rules to apply.

query.sql

SQL

CREATE TABLE type_tour (
    id          INT,
    price       DECIMAL(8,2),
    name        VARCHAR(50),
    notes       TEXT,
    is_active   BOOLEAN,
    born_on     DATE,
    created_at  DATETIME,
    settings    JSON,
    file_hash   BINARY(16)
);
DESCRIBE type_tour;

Output

OUTPUT

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| price      | decimal(8,2) | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| notes      | text         | YES  |     | NULL    |       |
| is_active  | tinyint(1)   | YES  |     | NULL    |       |
| born_on    | date         | YES  |     | NULL    |       |
| created_at | datetime     | YES  |     | NULL    |       |
| settings   | json         | YES  |     | NULL    |       |
| file_hash  | binary(16)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
9 rows in set

Notice that BOOLEAN was stored as tinyint(1): MySQL has no separate boolean type.

The families

Family	Main types	Use for
Integers	`TINYINT`, `SMALLINT`, `INT`, `BIGINT`	Counts, IDs, quantities
Exact decimals	`DECIMAL(p,s)`	Money, anything that must be exact
Approximate numbers	`FLOAT`, `DOUBLE`	Measurements, science
Short text	`CHAR`, `VARCHAR`	Names, emails, codes
Long text	`TEXT`, `MEDIUMTEXT`, `LONGTEXT`	Articles, comments
Fixed choices	`ENUM`, `SET`	A small, stable list of values
Date and time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`, `YEAR`	Moments and days
JSON	`JSON`	Flexible, nested data
Binary	`BINARY`, `VARBINARY`, `BLOB`	Raw bytes, hashes, UUIDs
Spatial	`POINT`, `POLYGON` and others	Maps and locations

Why the choice matters

Correctness. A DECIMAL keeps 0.10 exact. A FLOAT may not.
Validation. A DATE column rejects 2026-02-30. A VARCHAR accepts it.
Storage and speed. A smaller type means less disk, more rows per memory page, smaller indexes and faster queries. On a table with 100 million rows, BIGINT instead of INT for a column that never gets large costs about 400 MB, per indexed column.
Sorting and comparing. Numbers sort as numbers (2 before 10). The text '10' sorts before '2'.

query.sql

SQL

CREATE TABLE sorting_demo (as_text VARCHAR(5), as_number INT);
INSERT INTO sorting_demo VALUES ('2', 2), ('10', 10), ('9', 9);
SELECT as_text FROM sorting_demo ORDER BY as_text;
SELECT as_number FROM sorting_demo ORDER BY as_number;

Output

OUTPUT

+---------+
| as_text |
+---------+
| 10      |
| 2       |
| 9       |
+---------+
3 rows in set

+-----------+
| as_number |
+-----------+
|         2 |
|         9 |
|        10 |
+-----------+
3 rows in set

The rule of thumb

> Use the most specific, smallest type that safely holds every value the column will ever need.

The next lessons go through each family and show the trade-offs. The last lesson of the chapter gives you a quick checklist, Choosing the Right Data Type.

info

Types and standard SQL

INT, DECIMAL, VARCHAR, DATE and TIMESTAMP come from the SQL standard. TINYINT, MEDIUMINT, ENUM, SET, UNSIGNED, DATETIME and the TEXT variants are MySQL-specific.