7️⃣ 🔣 MySQL Data Types – Understand and Choose the Right Column Types

MySQL offers a variety of data types to match real-world data storage needs. Whether you’re storing numbers, dates, text, or geographic coordinates, choosing the right type affects performance, storage efficiency, and data integrity.

---

🧲 Introduction – Why Data Types Matter in MySQL?

In MySQL, data types define what kind of data each column can hold. The right choice ensures:

• 🔹 Efficient storage
• 🔹 Fast queries
• 🔹 Accurate computation
• 🔹 Validated input

Using the wrong data type leads to data loss, wasted space, or logic errors. Mastering MySQL’s data types is a key step in designing solid, scalable schemas.

---

📘 Topics Covered

🔢 Topic	📄 Description
🧱 MySQL Numeric Types	Store integers, decimals, floating points, and binary numbers
🔤 MySQL String Types	Store fixed/variable-length text, blobs, and encoded characters
⏱️ MySQL Date & Time Types	Store dates, times, timestamps, and durations
🧩 MySQL JSON & Spatial Data Types	Store structured and geographic/location data
📏 MySQL Data Type Limits	Maximum sizes, byte ranges, and use case boundaries
✅ Best Practices for Data Types	Tips for choosing accurate and efficient types
🚀 Real-World Use Cases	Practical examples of data type selection in production apps

---

🔢 1. Numeric Data Types

Type	Description	Use Case
TINYINT	1 byte integer (–128 to 127)	Boolean flags, status
SMALLINT	2 byte integer	Age, short IDs
INT	Standard 4 byte integer	IDs, counters
BIGINT	8 byte large integer	Financials, timestamps
DECIMAL	Exact decimal values	Currency, precision math
FLOAT	Approximate 4-byte float	Scientific use
DOUBLE	Approximate 8-byte float	Rates, calculations
BIT(n)	Bitfield of n bits	Flags, switches

🧪 Example:

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  balance DECIMAL(10,2),
  is_active TINYINT(1)
);

---

🔤 2. String Data Types

Type	Description	Use Case
CHAR(n)	Fixed-length string	Country codes, statuses
VARCHAR(n)	Variable-length string	Names, emails, titles
TEXT	Long string (up to 64KB)	Descriptions, comments
TINYTEXT	Up to 255 characters	Notes
MEDIUMTEXT	Up to 16MB	Articles
LONGTEXT	Up to 4GB	Logs, documents
ENUM	One from defined values	Status, category
SET	Multiple from defined values	Tags, user roles

🧪 Example:

CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
  bio TEXT,
  status ENUM('active', 'inactive', 'banned') DEFAULT 'active'
);

---

⏱️ 3. Date & Time Data Types

Type	Description	Format
DATE	Date only	'YYYY-MM-DD'
DATETIME	Date + Time	'YYYY-MM-DD HH:MM:SS'
TIMESTAMP	Date + Time with time zone support	Auto UTC conversion
TIME	Time only	'HH:MM:SS'
YEAR	4-digit year	'YYYY'

🧪 Example:

CREATE TABLE events (
  event_name VARCHAR(100),
  event_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

🧩 4. JSON & Spatial Data Types

🔸 JSON

Used to store structured key-value data.

CREATE TABLE configs (
  id INT,
  settings JSON
);

🔸 Spatial (GIS)

Type	Description
GEOMETRY	Base spatial type
POINT	Latitude & longitude
POLYGON	Region areas
LINESTRING	Routes and paths

---

📏 Data Type Limits & Sizes

Category	Type	Max Size / Range
Numeric	INT	±2,147,483,647
Numeric	BIGINT	±9 quintillion
String	VARCHAR(n)	Up to 65,535 bytes
Text	TEXT	Up to 64 KB
Date/Time	DATETIME	Years 1000 to 9999
JSON	JSON	Up to 1 GB (recommended < 64 MB)

---

📘 Best Practices for Choosing Data Types

✅ Tip	💡 Why It Matters
Use smallest fitting numeric type	Saves storage and boosts performance
Prefer VARCHAR over TEXT	Indexing and performance benefits
Use ENUM for small fixed values	Clean data and validation
Use DECIMAL for currency	Prevents float rounding errors
Use TIMESTAMP for audit logs	Auto-set/update timestamps
Match charset with expected content	Prevent encoding issues with emojis, etc.

---

🚀 Real-World Use Cases

Use Case	Data Type
Username	VARCHAR(50)
Currency	DECIMAL(10,2)
Boolean flag	TINYINT(1)
Blog post	TEXT, MEDIUMTEXT
Event date	DATE, TIMESTAMP
Country code	CHAR(2)
User preferences	JSON
GPS coordinates	POINT, GEOMETRY

---

📌 Summary – Recap & Next Steps

Choosing the correct MySQL data types ensures optimal performance, storage savings, and data integrity. By selecting the right type for each use case—whether it’s for text, time, or numerical values—you prevent issues that often arise from poor database design.

🔍 Key Takeaways:

• Use INT, DECIMAL, and FLOAT for numbers appropriately
• Use VARCHAR for strings, TEXT for large content
• Apply DATE, DATETIME, TIMESTAMP for time tracking
• Consider JSON and spatial types for modern applications

⚙️ Real-World Relevance:
Right data types power fast, scalable, and accurate systems—from banking and inventory apps to real-time analytics and map-based services.

---

❓ FAQ – MySQL Data Types

❓ What’s the difference between VARCHAR and TEXT?

✅ VARCHAR(n) is inline-stored, faster, and fully indexable.
⚠️ TEXT is stored off-page and has indexing limitations.

---

❓ When should I use DECIMAL instead of FLOAT?

✅ Use DECIMAL for currency to avoid rounding issues.
❌ FLOAT/DOUBLE are imprecise and not ideal for financials.

---

❓ How large can a VARCHAR be?

It can store up to 65,535 bytes, shared across all columns in a row. Encoding (e.g., utf8mb4) affects byte usage.

---

❓ What’s the difference between DATE, DATETIME, and TIMESTAMP?

• DATE: Date only
• DATETIME: Full date & time, no timezone
• TIMESTAMP: Stores in UTC, converts to local time

---

❓ Can I index TEXT or BLOB columns?

Partially. You must specify a prefix length:

CREATE INDEX idx_bio ON users (bio(255));

---

Share Now :