MySQL Data Types
MySQL Data Types
In MySQL, data types define the kind of data a column can store. Choosing the correct data type is crucial for data integrity, storage efficiency, and query performance.
MySQL data types are broadly classified into Numeric, Date & Time, and String (Character) types.
🔹 1. Numeric Data Types
| Type | Description | Example Values |
|---|---|---|
TINYINT |
Very small integer (-128 to 127) | 100, -50 |
SMALLINT |
Small integer (-32,768 to 32,767) | 3000 |
MEDIUMINT |
Medium integer (-8,388,608 to 8,388,607) | 5000000 |
INT / INTEGER |
Standard integer (-2,147,483,648 to 2,147,483,647) | 100000 |
BIGINT |
Large integer (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807) | 1000000000 |
DECIMAL(p,s) / NUMERIC(p,s) |
Fixed-point number (p=precision, s=scale) | 123.45 |
FLOAT |
Single-precision floating-point number | 12.34 |
DOUBLE |
Double-precision floating-point number | 123.456789 |
BIT |
Stores bit values (0 or 1) | 0,1 |
🔹 2. Date & Time Data Types
| Type | Format | Description |
|---|---|---|
DATE |
'YYYY-MM-DD' |
Stores date only |
DATETIME |
'YYYY-MM-DD HH:MM:SS' |
Stores date and time |
TIMESTAMP |
'YYYY-MM-DD HH:MM:SS' |
Stores UTC timestamp, auto-updates |
TIME |
'HH:MM:SS' |
Stores time only |
YEAR |
'YYYY' |
Stores year only |
🔹 3. String (Character) Data Types
| Type | Description | Example Values |
|---|---|---|
CHAR(n) |
Fixed-length string of length n | ‘A’, ‘VIPUL’ |
VARCHAR(n) |
Variable-length string (up to n characters) | ‘Hello World’ |
TEXT |
Large text data | Paragraphs |
TINYTEXT |
Very small text | Short strings |
MEDIUMTEXT |
Medium-sized text | Long text |
LONGTEXT |
Very long text | Articles |
ENUM |
Set of predefined values | ‘Male’,’Female’ |
SET |
Multiple predefined values | ‘A’,’B’,’C’ |
🔹 Choosing the Right Data Type
-
Use numeric types for arithmetic operations.
-
Use DATE/DATETIME/TIMESTAMP for time-sensitive data.
-
Use VARCHAR for variable-length strings to save space.
-
Use ENUM/SET for predefined values.
-
Avoid unnecessarily large types to optimize storage and performance.
🔹 Example Table Using Various Data Types
-
emp_id→ Integer primary key -
name→ Variable-length string -
gender→ Enum with two values -
dob→ Date of birth -
salary→ Decimal for precise values -
joining_time→ Date and time of joining -
status→ Tiny integer for active/inactive
