SQL Data Types in MySQL: INT, VARCHAR, DATE, BOOLEAN, ENUM Explained

Before you create a single table, you need to decide what kind of value each column will hold — a number, a name, a date, or a yes/no flag. That decision is made through SQL data types, and getting it right is one of the most underrated skills in database design. Choosing INT instead of BIGINT, or VARCHAR instead of TEXT, directly affects storage size, query speed, and how reliably your data behaves later. This lesson walks through every major MySQL data types category so your future CREATE TABLE statements are built on a solid foundation.

What Is a SQL Data Type?

A SQL data type defines the kind of value a column can store and how MySQL physically stores it on disk — a fixed number of bytes for an INT, a variable length for VARCHAR, or a special encoded format for DATE and DATETIME. Choosing the right data type isn't just a syntax requirement; it determines how much disk space a table uses, how fast comparisons and sorts run, and which values MySQL will accept or reject at insert time.

What You'll Learn

• Classify MySQL data types into numeric, string, date/time, and special categories.
• Choose between similar types like CHAR vs VARCHAR or FLOAT vs DECIMAL with confidence.
• Understand ENUM and SET for fixed-choice columns.
• Avoid common data type mistakes that waste storage or cause precision bugs.

Key Terms to Know

• Numeric type: A data type storing whole or decimal numbers, such as INT, BIGINT, FLOAT, or DECIMAL.
• String type: A data type storing text, such as CHAR, VARCHAR, or TEXT.
• Fixed-length type: A type that always reserves the same storage regardless of actual value length, like CHAR.
• Variable-length type: A type that stores only as much space as the value needs, like VARCHAR.
• Precision: The total number of digits a DECIMAL type can store, set explicitly to avoid rounding errors.

Numeric Data Types: INT, BIGINT, FLOAT, and DECIMAL

INT stores whole numbers up to about 2.1 billion and is the default choice for IDs, counts, and quantities. BIGINT extends that range for very large numbers, such as a global transaction ID at a payments company. Both reserve a fixed number of bytes (4 for INT, 8 for BIGINT) regardless of how small the stored value is.

FLOAT and DOUBLE store approximate decimal numbers using binary floating-point representation, which makes them fast but occasionally imprecise — 0.1 + 0.2 may not equal exactly 0.3. DECIMAL(p, s) stores exact decimal values by keeping precision (p) and scale (s) explicit, which is why every financial application uses DECIMAL for prices and balances instead of FLOAT.

String Data Types: CHAR, VARCHAR, and TEXT

CHAR(n) always reserves exactly n characters of storage, padding shorter values with spaces — it suits fixed-length codes like a 2-letter country code or a fixed-format ID. VARCHAR(n) stores only as many characters as the value actually contains, plus a small length prefix, making it the right default for names, emails, and addresses where length varies.

TEXT is used when content can exceed VARCHAR's practical limits, such as a blog post body or a product description running into thousands of characters. Unlike VARCHAR, TEXT columns can't be fully indexed for sorting in the same way and are stored slightly differently, so VARCHAR remains the better choice whenever a reasonable maximum length is known in advance.

Date/Time, Boolean, and Choice-Based Types: DATE, DATETIME, BOOLEAN, ENUM, SET

DATE stores only a calendar date (YYYY-MM-DD), while DATETIME stores both date and time (YYYY-MM-DD HH:MM:SS) — use DATE for a birthday, DATETIME for an event timestamp like an order placement. MySQL also offers TIMESTAMP, which behaves similarly to DATETIME but auto-converts to UTC and has a smaller range, making it common for created_at/updated_at audit columns.

BOOLEAN in MySQL is actually stored as TINYINT(1) under the hood, holding 0 (false) or 1 (true) — useful for flags like is_active or is_verified. ENUM restricts a column to one value from a predefined list, like ENUM('pending','shipped','delivered') for an order status, while SET allows zero or more values to be selected from a predefined list at once, such as tagging a user with multiple roles.

Visual Summary

Picture data types as containers of different shapes: INT and BIGINT are small fixed boxes for numbers, DECIMAL is a precise box that never rounds incorrectly, VARCHAR is a stretchy bag that resizes to fit text, DATE/DATETIME are calendar-shaped slots, and ENUM is a dropdown menu that only accepts one of a few pre-approved labels.

Common MySQL Data Types at a Glance

Type	Stores	Typical Use Case
INT	Whole numbers, ~4 bytes	IDs, quantities, counts
BIGINT	Very large whole numbers, 8 bytes	Global transaction or event IDs
DECIMAL(p,s)	Exact decimal numbers	Prices, account balances
VARCHAR(n)	Variable-length text up to n chars	Names, emails, titles
TEXT	Large variable-length text	Articles, descriptions, comments
DATE / DATETIME	Calendar date / date and time	Birthdays / order timestamps
ENUM	One value from a fixed list	Order status, gender, plan tier

SQL Example

-- A products table demonstrating multiple data type choices
CREATE TABLE products (
  product_id    INT            PRIMARY KEY AUTO_INCREMENT,
  product_name  VARCHAR(150)   NOT NULL,
  description   TEXT,
  price         DECIMAL(10, 2) NOT NULL,
  stock_qty     INT            NOT NULL DEFAULT 0,
  is_active     BOOLEAN        NOT NULL DEFAULT TRUE,
  category      ENUM('electronics', 'clothing', 'home', 'books') NOT NULL,
  created_at    DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Inserting a row using the correct types for each column
INSERT INTO products (product_name, description, price, stock_qty, category)
VALUES ('Wireless Mouse', 'Ergonomic 2.4GHz wireless mouse', 1299.00, 150, 'electronics');

Notice how each column uses the data type that matches its real-world meaning: DECIMAL(10, 2) for price guarantees exact currency math, ENUM restricts category to valid values only, and DATETIME with CURRENT_TIMESTAMP automatically records when the row was created without extra application code.

Real-World Examples

• Amazon stores product prices using exact decimal types, never floating-point, to avoid rounding errors across millions of daily transactions.
• Banking apps use DECIMAL for every monetary column because a FLOAT rounding error of even a fraction of a cent is unacceptable at scale.
• Food delivery apps like Zomato use ENUM-style status columns (placed, preparing, out_for_delivery, delivered) to track order state.
• Social media platforms use BOOLEAN flags like is_verified or is_private on millions of user rows for fast filtering.
• Airlines store flight departure and arrival as DATETIME values to correctly calculate duration across time zones.

Best Practices and Pro Tips

• Always use DECIMAL, never FLOAT or DOUBLE, for any column involving money — the tiny rounding errors in floating-point math compound badly over many transactions.
• Pick VARCHAR over CHAR unless every value truly has the exact same length, since CHAR's space-padding wastes storage on shorter values.
• When a column will only ever hold a handful of known values, ENUM is faster and self-documenting compared to storing the same values as free-text VARCHAR.

Common Mistakes to Avoid

• Using FLOAT for prices or financial calculations, leading to subtle rounding bugs that only appear after thousands of transactions.
• Defaulting every text column to VARCHAR(255) without thinking about the actual maximum length needed.
• Storing dates as VARCHAR strings instead of DATE/DATETIME, which breaks sorting, filtering, and date arithmetic.
• Forgetting that BOOLEAN in MySQL is really TINYINT(1), which can sometimes cause confusion when comparing values directly to true/false in application code.

Interview Questions

Q1. What is the difference between CHAR and VARCHAR?

CHAR(n) always reserves a fixed n characters of storage and pads shorter values with spaces. VARCHAR(n) stores only as many characters as the actual value contains, plus a small length prefix, making it more storage-efficient for variable-length text.

Q2. Why should DECIMAL be used instead of FLOAT for currency?

DECIMAL stores exact decimal values based on defined precision and scale, while FLOAT uses binary floating-point approximation that can introduce small rounding errors. Currency requires exact values, so DECIMAL is the standard choice.

Q3. What is the difference between DATE, DATETIME, and TIMESTAMP in MySQL?

DATE stores only a calendar date. DATETIME stores both date and time without time zone conversion. TIMESTAMP also stores date and time but automatically converts to and from UTC and has a smaller supported date range.

Q4. How does ENUM differ from SET in MySQL?

ENUM allows a column to hold exactly one value from a predefined list. SET allows a column to hold zero or more values from a predefined list simultaneously, stored internally as a bitmask.

Practice MCQs

1. Which data type is most appropriate for storing a product price?

1. FLOAT
2. DECIMAL(10,2)
3. VARCHAR(10)
4. INT

Answer: B. DECIMAL(10,2)

Explanation: DECIMAL stores exact decimal values, which is essential for currency to avoid floating-point rounding errors.

2. Which type pads shorter text values with spaces to a fixed length?

1. VARCHAR
2. TEXT
3. CHAR
4. ENUM

Answer: C. CHAR

Explanation: CHAR(n) always reserves exactly n characters, padding shorter values with spaces.

3. A column that should only ever hold 'small', 'medium', or 'large' is best modeled as:

1. VARCHAR(20)
2. ENUM('small','medium','large')
3. INT
4. TEXT

Answer: B. ENUM('small','medium','large')

Explanation: ENUM restricts the column to a predefined fixed set of values, which is exactly this scenario.

Quick Revision Points

• INT is 4 bytes; BIGINT is 8 bytes — choose based on the maximum expected value.
• Always use DECIMAL(p,s) for money, never FLOAT or DOUBLE.
• CHAR is fixed-length and padded; VARCHAR is variable-length and more space-efficient for most text.
• ENUM = exactly one value from a list; SET = zero or more values from a list.

Conclusion

• Picking the right data type prevents storage waste, precision bugs, and slow queries down the line.
• DECIMAL is non-negotiable for any monetary value in a production schema.
• ENUM and SET make schemas self-documenting for fixed-choice columns.

MySQL data types are the foundation every table is built on. Numeric types like INT, BIGINT, and DECIMAL handle different magnitudes and precision needs, string types like CHAR, VARCHAR, and TEXT handle text of varying length, and date/time types like DATE, DATETIME, and TIMESTAMP handle temporal data correctly. ENUM and SET add fixed-choice constraints directly at the schema level. Mastering these sql data types in mysql now means every CREATE TABLE statement in the rest of this module will be built correctly from the start.