Turning Learners into Developers
- What is a Data Type?
A data type defines what kind of data can be stored in a database column, such as numbers, text, dates, or files. It helps the database store data efficiently, validate input, and improve performance.
- Data Types in SQL
In SQL, data types specify the type and size of values a column can hold, like INT for numbers, VARCHAR for text, and DATE for dates. They ensure data accuracy, consistency, and proper storage across databases.
- Data Types in MySQL
In MySQL, data types define how data is stored and managed in tables. Examples include INT (numbers), VARCHAR (text), DECIMAL (money), DATE (dates), and BOOLEAN (true/false as TINYINT).
SQL → General concept used by all databases
MySQL → Specific implementation of SQL data types
- Numeric Data Types (MySQL)
Integer Types
Data Type | Storage | Range (Signed) | Example |
|---|---|---|---|
TINYINT | 1 byte | -128 to 127 | is_active TINYINT |
SMALLINT | 2 bytes | -32,768 to 32,767 | marks SMALLINT |
MEDIUMINT | 3 bytes | -8M to 8M | views MEDIUMINT |
| 4 bytes | 2B to 2B | age INT |
BIGINT | 8 bytes | Very large | population BIGINT |
Difference: Size & range increase from | |||
- Decimal & Floating Types
Data Type | Description | Example | |
|---|---|---|---|
DECIMAL(p,s) | Exact values (money) | salary DECIMAL(10,2) | |
FLOAT | Approximate (4 bytes) | temperature FLOAT | |
DOUBLE | Approximate (8 bytes) | distance DOUBLE | |
Difference: | |||
- String (Character) Data Types
Text Types
Data Type | Max Size | Example |
|---|---|---|
CHAR(n) | 255 | country CHAR(2) |
VARCHAR(n) | 65,535 | name VARCHAR(100) |
TINYTEXT |
| Short text |
TEXT | 65 KB | Blog content |
MEDIUMTEXT | 16 MB | Articles |
LONGTEXT |
| Large content |
Difference: | ||
- Date & Time Data Types (MySQL)
Data Type | Description | Example |
|---|---|---|
DATE | Date only | 2026-02-20 |
TIME |
| 14:30:00 |
DATETIME | Date + time | 2026-02-20 14:30:00 |
TIMESTAMP | Auto-updated | Current time |
YEAR | Year | 2026 |
Difference: | ||
- Boolean Data Type (MySQL)
⚠️ MySQL does not have a real BOOLEAN
Data Type | Meaning |
|---|---|
BOOLEAN | Alias of |
is_active BOOLEAN -- stored as 0 or 1 | |
- MySQL Binary Data Types
| Data Type | Max Size | Example Column | Example Use |
|---|---|---|---|
BINARY(n) | 255 bytes | hash BINARY(16) | Fixed-length binary data (MD5 hash) |
VARBINARY(n) | 65,535 bytes | file_name VARBINARY(100) | Variable-length binary text |
TINYBLOB | 255 bytes | icon TINYBLOB | Small image or icon |
BLOB |
| profile_pic BLOB | Profile photo |
MEDIUMBLOB | 16 MB | video_clip MEDIUMBLOB | Video files |
LONGBLOB | 4 GB | document LONGBLOB | Large PDFs or videos |
CREATE TABLE files ( id INT AUTO_INCREMENT PRIMARY KEY, profile_pic BLOB, video MEDIUMBLOB, document LONGBLOB ); | |||
- ENUM & SET (MySQL-Specific ⭐)
Data Type | Description | Example |
|---|---|---|
ENUM | One value only | ENUM('Male','Female') |
SET | Multiple values | SET('Java','Python') |
Difference: | ||
- JSON Data Type (MySQL 5.7+)
Data Type | Example |
|---|---|
JSON | {"name":"Rahul","age":25} |
user_data JSON | |
- Complete MySQL Example Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age TINYINT,
salary DECIMAL(10,2),
gender ENUM('Male','Female','Other'),
skills SET('Java','Python','SQL'),
is_active BOOLEAN,
profile_pic BLOB,
created_at TIMESTAMP
);- Image Example in MySQL
Table for Image Storage
CREATE TABLE images (
id INT AUTO_INCREMENT PRIMARY KEY,
image_name VARCHAR(100),
image_data BLOB
);Insert Image into Table
INSERT INTO images (image_name, image_data)
VALUES (
'profile.jpg',
LOAD_FILE('C:/images/profile.jpg')
);⚠️ Note: File path must be allowed | secure_file_priv should be enabled in MySQL
Retrieve Image from Database
SELECT image_name, image_data
FROM images
WHERE id = 1;- Video Example in MySQL
Table for Video Storage
CREATE TABLE videos (
id INT AUTO_INCREMENT PRIMARY KEY,
video_name VARCHAR(100),
video_data MEDIUMBLOB
);Insert Video into Table
INSERT INTO videos (video_name, video_data)
VALUES (
'intro.mp4',
LOAD_FILE('C:/videos/intro.mp4')
);Retrieve Video from Database
SELECT video_name, video_data
FROM videos
WHERE id = 1;
