Data Types in MySQL

Choosing the right data type for each column is one of the most impactful decisions you make when designing a MySQL schema. The right type saves storage, enforces validity, and improves query performance.

Numeric Types

MySQL provides a range of integer types that differ in their storage size and value range:

Type	Storage	Signed range
TINYINT	1 byte	-128 to 127
SMALLINT	2 bytes	-32,768 to 32,767
INT	4 bytes	-2,147,483,648 to 2,147,483,647
BIGINT	8 bytes	-9.2 × 10^18 to 9.2 × 10^18

For fractional numbers use DECIMAL(precision, scale) for exact arithmetic (ideal for money) or FLOAT / DOUBLE for approximate floating-point values.

price     DECIMAL(10, 2),   -- e.g. 99999999.99
latitude  DOUBLE,
score     TINYINT UNSIGNED

String Types

VARCHAR(n) stores variable-length strings up to n characters and is the most common choice for names, emails, and short text. CHAR(n) stores fixed-length strings and is slightly faster for values that are always the same length, such as two-letter country codes.

For longer text use TEXT (up to 65,535 bytes), MEDIUMTEXT (16 MB), or LONGTEXT (4 GB). Binary equivalents BLOB, MEDIUMBLOB, and LONGBLOB store raw binary data.

Always use the utf8mb4 character set for string columns to correctly handle all Unicode characters including emoji:

name    VARCHAR(100)  NOT NULL,
bio     TEXT,
code    CHAR(2)       NOT NULL

Date and Time Types

Type	Format	Use
DATE	YYYY-MM-DD	Calendar dates
TIME	HH:MM:SS	Time of day or duration
DATETIME	YYYY-MM-DD HH:MM:SS	Combined date and time, no timezone
TIMESTAMP	YYYY-MM-DD HH:MM:SS	Stored as UTC, displayed in session timezone
YEAR	YYYY	Year values

TIMESTAMP columns can be given DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to track creation and modification times automatically.

created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Boolean and JSON

MySQL does not have a native boolean type. The convention is TINYINT(1) where 0 represents false and 1 represents true. Many ORMs map this automatically to a boolean in application code.