MySQL Data Types

Every column in a MySQL table has a data type, which decides what values it can hold and how much space they use.

Why Data Types Matter

They stop wrong data: a number column will not accept the word "hello"
They save space and make queries faster
They make sorting and calculations work correctly (10 comes after 9 as a number, but before 9 as text)

Number Types

Type	Range or use
`TINYINT`	-128 to 127, or 0 to 255 with `UNSIGNED`
`INT`	About -2.1 billion to 2.1 billion
`BIGINT`	Very large whole numbers
`DECIMAL(p, s)`	Exact numbers; `p` digits in total, `s` after the point
`FLOAT`, `DOUBLE`	Approximate numbers, for science or measurements

Example

sql

CREATE TABLE price_test (
  stock INT UNSIGNED,
  price DECIMAL(10, 2),
  rating FLOAT
);

DECIMAL(10, 2) holds values up to 99,999,999.99 with exactly two decimal places.

Always use DECIMAL for money. FLOAT stores an approximation, so a price of 0.1 + 0.2 might come out as 0.30000000000000004.

Text Types

Type	Use
`CHAR(n)`	Fixed length text, such as a 2-letter country code
`VARCHAR(n)`	Text of varying length, up to n characters (names, emails)
`TEXT`	Long text such as a product description (up to about 64 KB)
`LONGTEXT`	Very long text, up to 4 GB
`ENUM('a','b')`	One value from a fixed list

Example

sql

CREATE TABLE customers (
  name VARCHAR(100),
  phone VARCHAR(20),
  city_code CHAR(3),
  notes TEXT,
  status ENUM('active', 'blocked')
);

Phone numbers like 0300-1234567 are stored as VARCHAR, not as numbers, because they can start with 0 and contain dashes, and you never do maths with them. A short fixed code such as KHI for Karachi fits well in CHAR(3).

Date and Time Types

Type	Format	Example
`DATE`	`YYYY-MM-DD`	`2026-10-15`
`TIME`	`HH:MM:SS`	`14:30:00`
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	`2026-10-15 14:30:00`
`TIMESTAMP`	Like DATETIME, stored in UTC	Good for "created at" columns
`YEAR`	`YYYY`	`2026`

Example

sql

CREATE TABLE deliveries (
  order_date DATE,
  delivered_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO deliveries (order_date, delivered_at)
VALUES ('2026-10-15', '2026-10-16 18:45:00');

The created_at column fills itself with the current date and time.

True and False

MySQL has no separate true/false type. BOOLEAN is a short name for TINYINT(1), where 0 means false and 1 means true:

Example

sql

CREATE TABLE settings (
  is_open BOOLEAN DEFAULT TRUE
);

Example

sql

SELECT TRUE, FALSE;

Output

+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+
1 row in set (0.00 sec)

JSON

MySQL also has a JSON type that stores JSON documents and checks they are valid:

Example

sql

CREATE TABLE product_extra (
  details JSON
);

INSERT INTO product_extra
VALUES ('{"colour": "black", "warranty_months": 6}');

Choosing a Type: Quick Guide

Data	Good type
ID numbers	`INT UNSIGNED` or `BIGINT UNSIGNED`
Price in rupees	`DECIMAL(10, 2)`
Name, email	`VARCHAR(100)` or `VARCHAR(255)`
Description	`TEXT`
Birthday	`DATE`
When a row was created	`TIMESTAMP` or `DATETIME`
Yes/no flag	`BOOLEAN`

Exercise

Which data type is best for storing a product price such as 2450.50 rupees?

FLOATVARCHAR(10)DECIMAL(10, 2)INT