Skip to main content
MySQL Basics
CHAPTER 05 Beginner

MySQL Data Types Explained

Updated: May 16, 2026
7 min read

# CHAPTER 5

MySQL Data Types Explained

1. Introduction

If you try to pour a gallon of water into a shot glass, it will spill everywhere. If you build a massive swimming pool just to hold a shot of water, it is an incredible waste of space. Database columns work exactly the same way. When creating a table, you must tell MySQL exactly what kind of data (and how much of it) a column is allowed to hold. In this chapter, we will master Data Types, the security guards that guarantee data integrity and optimize server storage.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Explain why strict Data Types are necessary.
  • Differentiate between various Numeric types (INT, DECIMAL).
  • Differentiate between String types (VARCHAR, TEXT).
  • Store temporal data using DATE and DATETIME.
  • Restrict inputs using ENUM and BOOLEAN.

3. Why Data Types Matter

Imagine you have an age column, and you do not restrict the data type.
  • User 1 enters: 25
  • User 2 enters: Twenty-five
  • User 3 enters: Old enough

If you try to run a mathematical query later: SELECT users WHERE age > 18, the database will crash because it cannot do math on the text "Old enough". By forcing the age column to be an INT (Integer), MySQL will block User 2 and User 3 from ever saving their data. Data Types guarantee that your data remains clean and mathematically predictable.

4. Numeric Data Types

Used for math, counting, and ID numbers.
  • INT (Integer): Stores whole numbers (no decimals) from roughly -2 billion to +2 billion.
  • *Best for:* IDs (userid), age, counters (number of likes).
  • TINYINT: A super small integer from -128 to 127.
  • *Best for:* Boolean flags (0 or 1), or tiny categories.
  • DECIMAL(M, D): Stores exact decimal values. M is the total number of digits, D is the number of digits after the decimal point. DECIMAL(10, 2) means 10 total digits, 2 after the dot (e.g., 12345678.99).
  • *Best for:* Financial data (Money, Prices). *Never use FLOAT or DOUBLE for money, as they introduce microscopic rounding errors!*

5. String (Text) Data Types

Used for names, emails, and paragraphs.
  • VARCHAR(length): Variable-length character string. The length specifies the maximum number of characters allowed. If you specify VARCHAR(50), and a user types a 51-character name, MySQL will throw an error.
  • *Best for:* First names, emails, passwords, short titles.
  • CHAR(length): Fixed-length character string. If you specify CHAR(2) and type "A", it pads the rest with blank spaces.
  • *Best for:* 2-letter Country Codes (US, UK), or fixed-length hashes.
  • TEXT: Stores massive strings up to 65,535 characters long.
  • *Best for:* Blog post bodies, long user comments, product descriptions.

6. Date and Time Data Types

Used for tracking when events happen.
  • DATE: Stores a date in the format YYYY-MM-DD (e.g., 2024-12-25).
  • *Best for:* Birthdays, expiration dates.
  • TIME: Stores a time in the format HH:MM:SS.
  • DATETIME: Combines both into YYYY-MM-DD HH:MM:SS.
  • *Best for:* Tracking exact timestamps (e.g., accountcreatedat, lastlogin).

7. ENUM and BOOLEAN

Used for strict, limited choices.
  • BOOLEAN (or BOOL): In MySQL, BOOLEAN is actually just a synonym for TINYINT(1). It stores either 1 (True) or 0 (False).
  • *Best for:* isactive, isadmin, haspaid.
  • ENUM('value1', 'value2'): A string object that can have only one value, chosen from the list of values you define.
  • *Best for:* shirtsize ENUM('S', 'M', 'L', 'XL') or order_status ENUM('Pending', 'Shipped', 'Delivered').

8. Mini Project: A Complete User Table

Let's build a highly optimized table utilizing everything we just learned.
sql
123456789 
CREATE TABLE employees (
    emp_id INT,                                  -- Standard whole number
    first_name VARCHAR(50),                      -- Max 50 chars
    email VARCHAR(150),                          -- Max 150 chars
    salary DECIMAL(10, 2),                       -- Money! Max 99,999,999.99
    hire_date DATE,                              -- Just the day (YYYY-MM-DD)
    is_manager BOOLEAN,                          -- 1 for True, 0 for False
    department ENUM('IT', 'Sales', 'HR', 'Exec') -- Strict restriction!
);

9. Common Mistakes

  • Using VARCHAR(255) for everything: Beginners often use VARCHAR(255) for every text column because they are lazy. While it works, it makes the database engine work harder during sorting and indexing. Always restrict VARCHAR to the realistic maximum you expect.
  • Using INT for Phone Numbers: Do not use INT to store phone numbers (555-1234). Integers do not allow dashes, and if a phone number starts with a 0 (e.g., 0123456789), the INT data type will automatically delete the leading zero! Always use VARCHAR for phone numbers.

10. Best Practices

  • Think about Storage Constraints: Every column you create consumes hard drive space. Using an INT (4 bytes) to store a person's age is a waste of space when a TINYINT (1 byte) can hold numbers up to 127. In a table with a billion rows, choosing the right data type saves gigabytes of space.

11. Exercises

  1. 1. What data type should you use to store a user's account balance, and why shouldn't you use a standard floating-point number?
  1. 2. Write the SQL syntax to create a column named status that only accepts the words 'Active', 'Suspended', or 'Banned'.

12. MCQ Quiz with Answers

Question 1

If you attempt to insert the text string "Thirty" into a column defined as an INT, what will MySQL do?

Question 2

Why is the VARCHAR data type preferred over INT for storing phone numbers or zip codes?

13. Interview Questions

  • Q: Explain the difference between VARCHAR and CHAR data types in MySQL, and provide a use-case where CHAR would be superior.
  • Q: Describe the behavior of the ENUM data type. In what scenario would an ENUM be a poor architectural choice? (Hint: Think about lists of options that change frequently).

14. FAQs

Q: What happens if I insert a 100-character string into a VARCHAR(50) column? A: In modern MySQL configurations (Strict Mode), the database will immediately throw a "Data too long" error and reject the entry. In older versions, it would silently truncate (cut off) the final 50 characters and save the first half!

15. Summary

Data Types act as the gatekeepers of your database. By strictly defining whether a column accepts numbers, text, dates, or boolean flags, you guarantee that your application's data remains clean, mathematically viable, and perfectly optimized for long-term storage.

16. Next Chapter Recommendation

Our tables are built and strictly enforced. It is finally time to put data into them! In Chapter 6: INSERT, UPDATE, and DELETE Queries, we will learn how to add new rows, modify existing data, and safely erase records using Data Manipulation Language (DML).

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Creating Databases and Tables
Next Chapter INSERT, UPDATE, and DELETE Queries

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6