MySQL Data Types

MySQL Tutorial

MySQL Data Types – Complete Guide with Examples

In MySQL, data types define what kind of data a column can store. Choosing the correct data type is critical for performance, storage efficiency, and data accuracy.

MySQL data types are broadly divided into:

  •  Numeric Data Types
  •  String (Character) Data Types
  • Date & Time Data Types
  •  Boolean Data Type
  •  Binary & Special Data Types

 Why Data Types Are Important in MySQL?

  •  Reduce storage usage
  •  Improve query performance
  •  Prevent invalid data
  •  Ensure accurate calculations
  •  Essential for interviews & production databases

 1. Numeric Data Types

Used to store numbers.

 Integer Data Types

Data TypeSizeRange
TINYINT1 byte-128 to 127
SMALLINT2 bytes-32,768 to 32,767
MEDIUMINT3 bytes-8M to 8M
INT4 bytes-2B to 2B
BIGINT8 bytesVery large

Example


 

 Decimal & Floating Types

Data TypeDescription
FLOATApproximate decimal
DOUBLEHigher precision
DECIMAL(p,s)Exact decimal (recommended for money)

Example


 

  • Use DECIMAL for financial data.

 2. String (Character) Data Types

Used to store text.

 Character Types

Data TypeDescription
CHAR(n)Fixed-length string
VARCHAR(n)Variable-length string

Example


 

  •  Most commonly used
  •  Efficient storage

 Text Types

Data TypeMax Size
TINYTEXT255 bytes
TEXT65 KB
MEDIUMTEXT16 MB
LONGTEXT4 GB

Example


 

  •  Used for long content

 3. Date & Time Data Types

Used to store date and time values.

Data TypeFormat
DATEYYYY-MM-DD
TIMEHH:MM:SS
DATETIMEYYYY-MM-DD HH:MM:SS
TIMESTAMPAuto-updated date & time
YEARYYYY

Example


 

  • TIMESTAMP auto-updates on row modification.

 4. Boolean Data Type

MySQL does not have a real BOOLEAN type.

  • BOOLEAN = TINYINT(1)
  •  0 → false
  • 1 → true

Example


 

 5. Binary Data Types

Used to store binary data (images, files).

Data TypeDescription
BINARYFixed-length binary
VARBINARYVariable-length
BLOBBinary large object

Example


 

 6. ENUM & SET Data Types

 ENUM

Stores one value from a predefined list.


 

  •  Saves space
  •  Limited choices

 SET

Stores multiple values from a list.


 

 Choosing the Right Data Type (Quick Guide)

DataBest Type
IDINT / BIGINT
NameVARCHAR
PriceDECIMAL
DescriptionTEXT
DateDATE
Date + TimeDATETIME
StatusBOOLEAN
FileBLOB

 Common Mistakes

  •  Using VARCHAR for numbers
  •  Using FLOAT for money
  •  Overusing TEXT
  •  Wrong date type selection
  •  Not setting size limits

 Best Practices

  •  Choose smallest suitable data type
  •  Use VARCHAR instead of CHAR 
  •  Use DECIMAL for currency
  •  Use TIMESTAMP for audit fields
  •  Avoid unnecessary large fields

 Interview Questions: MySQL Data Types

1. Difference between CHAR and VARCHAR?
CHAR is fixed-length, VARCHAR is variable-length.

2. Best data type for money?
DECIMAL

3. Does MySQL support BOOLEAN?
Internally uses TINYINT(1).

4. Difference between DATETIME and TIMESTAMP?
TIMESTAMP auto-updates and is timezone-aware.

5. What data type for long text?
TEXT / LONGTEXT

 Summary

  •  MySQL data types control storage and performance
  •  Numeric, string, date types are core
  •  Correct data types prevent bugs
  •  Critical for backend & interviews
  •  Foundation of database design

Mastering MySQL Data Types will make your SQL schemas efficient, scalable, and professional

You may also like...