MySQL Data Types: Complete Guide with Examples

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.

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.

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

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
`INT` / `INTEGER`	4 bytes	2B to 2B	age INT
BIGINT	8 bytes	Very large	population BIGINT
Difference: Size & range increase from `TINYINT` → `BIGINT`

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: `DECIMAL` → accurate (₹, $) \| `FLOAT/DOUBLE` → approximate (scientific)

Text Types

Data Type	Max Size	Example
CHAR(n)	255	country CHAR(2)
VARCHAR(n)	65,535	name VARCHAR(100)
TINYTEXT	255	Short text
TEXT	65 KB	Blog content
MEDIUMTEXT	16 MB	Articles
LONGTEXT	4 GB	Large content
Difference: `CHAR` → fixed length \| `VARCHAR` → variable length \| `TEXT` → large text data

Data Type	Description	Example
DATE	Date only	2026-02-20
TIME	Time only	14:30:00
DATETIME	Date + time	2026-02-20 14:30:00
TIMESTAMP	Auto-updated	Current time
YEAR	Year	2026
Difference: `TIMESTAMP` auto-updates when a record changes, `DATETIME` does not.

⚠️ MySQL does not have a real BOOLEAN

Data Type	Meaning
BOOLEAN	Alias of `TINYINT(1)`
is_active BOOLEAN -- stored as 0 or 1

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	65 KB	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 );

Data Type	Description	Example
ENUM	One value only	ENUM('Male','Female')
SET	Multiple values	SET('Java','Python')
Difference: `ENUM` → single choice \| `SET` → multiple choices

Data Type	Example
JSON	{"name":"Rahul","age":25}
user_data JSON

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
);

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;

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;

Leave a Reply Cancel reply

Tutorials

Examples

Projects

Information

Follow Us