DEV Community

Cover image for 🌙 Late Night Chronicles: #4 Understanding MySQL Data Types
Shreyash Ogale
Shreyash Ogale

Posted on

🌙 Late Night Chronicles: #4 Understanding MySQL Data Types

#sql #beginners #database #mysql

In the previous post, we explored SQL basics and how we interact with databases.

But here’s 👇

👉 What kind of data can we actually store in MySQL?

That’s where Data Types come in.

🔹 What are Data Types?

Data types define what kind of value a column can store.

Think of it like rules:

  • Name → text
  • Age → number
  • Date → date

Without data types, data would become messy and inconsistent.

🔹 Types of Data in MySQL

MySQL mainly supports:

  • 📝 Character (String) types
  • 🔢 Numeric types
  • 📅 Date & Time types
  • 🖼️ Binary / Large Object types

🔹 1. Character (String) Data Types

Used to store text values 👇

  • CHAR(n)

    • Fixed length
    • Faster retrieval
    • Wastes space

  • VARCHAR(n)

    • Variable length
    • Saves space
    • Slightly slower than CHAR

📌 Example use cases:

  • Name
  • Address
  • City

🔹 2. TEXT Data Types

Used for large text data

  • TINYTEXT → up to 255 characters
  • TEXT → up to 65,535 characters
  • MEDIUMTEXT → up to 16 MB
  • LONGTEXT → up to 4 GB

💡 Stored outside the table (uses pointer internally)

👉 Used for:

  • Comments
  • Reviews
  • Resume
  • Feedback

🔹 3. Numeric Data Types

Used for numbers 👇

Integer Types

  • TINYINT (1 byte)
  • SMALLINT (2 bytes)
  • MEDIUMINT (3 bytes)
  • INT (4 bytes)
  • BIGINT (8 bytes)

👉 Used for: age, count, IDs

Floating Point Types

  • FLOAT → up to 7 decimal precision
  • DOUBLE → up to 15 decimal precision
  • DECIMAL → exact precision (used for money 💰)

Boolean

  • TRUE → 1
  • FALSE → 0

🔹 4. Date and Time Data Types

Used for storing date & time 👇

  • DATE → 'YYYY-MM-DD'
  • TIME → 'HH:MM:SS'
  • DATETIME → date + time
  • YEAR → year only

👉 Used for:

  • DOB
  • Login time
  • Transactions

🔹 5. Binary & Large Objects (BLOB)

Used for storing multimedia data 👇

  • TINYBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

👉 Used for:

  • Images
  • Videos
  • Audio
  • Files

💡 Stored outside the table (like TEXT)

🔹 Important Concept

👉 Large data types (TEXT, BLOB) are:

  • Stored outside the table
  • Accessed using pointers
  • Not ideal for searching

💡Learning

  • Use CHAR → when length is fixed
  • Use VARCHAR → when length varies
  • Use DECIMAL → for money
  • Use TEXT/BLOB → for large data

👉 In the next post, we’ll start writing real SQL queries (hands-on 🚀)

Top comments (0)