MySQL Data Types Explained: Choose the Right Column Types for Performance and Scalability

1. Introduction: Why You Should Understand the MySQL Data Type List

When you design tables or integrate applications with MySQL, one of the most common questions is: “Which data type should I use for this column?”
Should it be INT? Do you really need BIGINT? Is VARCHAR enough for strings, or is TEXT better? These choices may seem minor, but they form the foundation that affects your system later.

If you underestimate how to choose data types, you’ll often run into issues like the following:

• As your data grows beyond expectations, you waste storage space
• Indexes bloat and query performance gradually degrades
• Out-of-range values or overflow cause unexpected bugs or exceptions
• You’re forced to change column types later, triggering a large‑scale migration

In other words, systematically understanding MySQL data types and choosing the right one for each use case is the fastest way to improve both performance and maintainability.

This page is mainly intended for readers such as:

• Engineers who are about to start serious system development with MySQL
• Backend / infrastructure‑oriented engineers who want to review existing table designs
• Web developers and programmers who want “recommended types” by use case

We’ll begin by organizing the major MySQL data types as a categorized “list.” Then we’ll explain key types—numeric, string, date/time, JSON, ENUM/SET—covering their characteristics, typical use cases, and selection tips. Finally, we’ll summarize common design mistakes and how to avoid them, along with an FAQ.

This is not just a “list of terms.” It’s structured as decision‑making guidance so you won’t get stuck when designing tables in real projects. In the next section, let’s dive into how to think about data types and review the categorized list.

2. What Is a Data Type, and Why Does “Choosing the Right Type” Matter?

In a database, a “data type” is a rule that defines what kind of values a column can store.
MySQL provides many data types—integers, decimals, strings, dates, binaries, JSON, and more—so you need to choose appropriately depending on your purpose.

The Role of Data Types

A data type is not just a “format category.” It plays multiple roles at the same time:

• Restrict the kind of data (numeric vs. string, boolean, etc.)
• Define the allowed range and number of digits
• Determine required memory (storage size)
• Influence index structures and search performance
• Affect implicit conversions and comparison rules (e.g., string collation)

In short, data types are not merely “containers”—they are fundamental design choices that affect the entire data management lifecycle.

What Happens If You Choose the Wrong Type?

If you pick the wrong data type, problems like these happen frequently in real work:

• Wasting Storage

For example, if BIGINT (8 bytes) is enough but you mistakenly use DECIMAL or LONGTEXT, you may consume far more disk space than expected.

• Slower Queries

If you overuse LIKE searches on huge TEXT columns, or if indexes bloat because you chose excessively large numeric types, SQL execution tends to slow down.

• Inconsistent Values or Overflow

You may store values that don’t fit in INT, causing unexpected negative values, rounding, or other issues.

• High‑Cost Table Changes

Especially on large tables, changing types with ALTER TABLE can lead to:

• Long lock times
• Service impact
• Data migration work and other risks.

Key Categories You Should Understand Up Front

MySQL data types are broadly categorized as follows:

• Numeric types (integers, decimals)
• String types
• Date and time types
• Binary types
• JSON type
• ENUM / SET types
• Spatial data types

Each category has different trade-offs—strengths/weaknesses, “light vs. heavy,” and “easy vs. hard to search.” That’s why you need the optimal type selection for each use case.

3. MySQL Data Type Categories (Overview List)

In this section, we’ll summarize the data types available in MySQL as a categorized overview list. In practice, the first things you want to confirm are “What types exist?” and “Which one should I use?”
So here we’ll clearly show use cases, key characteristics, and representative type names, and then explain each category in more detail in the following sections.

3.1 Numeric Types

Numeric types are the foundation for all numeric processing, including integers, decimals, and floating-point values.
This category is used most frequently for IDs, quantities, prices, flag checks, and many other purposes.

Integer Types

Notes

• Adding UNSIGNED doubles the positive range.
• INT is used in many cases, but using BIGINT casually can make indexes heavier.

Decimal / Floating-Point Types

Notes

• For money, DECIMAL is the only reasonable choice . Floating-point types ( FLOAT / DOUBLE ) can introduce errors.
• In cases involving many calculations, DOUBLE is sometimes chosen for speed/precision trade-offs.

Other Numeric Types

3.2 Date and Time Types

Date/time handling is used very frequently in application development.
Depending on the purpose, factors like “time zone behavior,” “automatic updates,” and “second-level precision” differ, so choosing the right type matters.

Notes

• If you want to manage “updated at” automatically, TIMESTAMP is convenient.
• For “logs” or “history” where you want to store accurate timestamps, DATETIME is commonly used.

3.3 String / Binary Types

Usernames, emails, passwords, descriptions—strings are often the most complex area in table design.

Fixed-Length Strings

Variable-Length Strings

Large Text (TEXT Family)

Notes

• Use TEXT when you need to store very large strings such as article bodies or long descriptions.
• Be careful with search and index design.

Binary Data (BINARY / BLOB)

※ In general, large files are not stored in the database; a common design is to store them in external storage instead.

ENUM / SET Types (Enumerations)

Notes

• If you need to change the type definition later, maintenance becomes heavy
• It can be effective for small-scale, static candidates

3.4 JSON Type

• You get NoSQL-like flexibility while still being able to use JSON-specific functions.
• However, it’s not recommended to pack frequently searched data into JSON . If it can be normalized, it should be modeled as structured tables.

3.5 Spatial Types

These are used when working with geographic and location data.

In typical web applications, these aren’t used often, but they are important for map apps and GPS integrations.

4. How to Choose and Use Each Data Type (Key Decision Points)

In this section, we delve deeper into the categories introduced above, focusing on “how to choose in real-world scenarios.”
Simply knowing the names is not enough—selecting the best-fit data type has a major impact on maintainability, performance, and future scalability.

4.1 How to Choose Numeric Types

Criteria for Choosing Integer Types

When selecting integer types, focus on these three points:

1. Understand the Maximum Required Range

• Small counters → TINYINT
• Product quantities / flags → SMALLINT / INT
• Large-scale IDs or logs → BIGINT

Some projects overuse BIGINT for primary keys, but this can easily lead to larger index sizes and may negatively impact performance.

2. Actively Consider UNSIGNED

If you only handle positive values (e.g., numeric IDs, inventory counts)
→ using UNSIGNED doubles the range and may allow you to use a smaller type.

3. For Money or Precision‑Critical Values, Use DECIMAL

For values where “errors are not acceptable,” avoid FLOAT/DOUBLE and always use DECIMAL.

4.2 How to Choose Date and Time Types

The appropriate type depends on your use case.

Differences Between DATETIME and TIMESTAMP

General Rules for Choosing

• Application logs or event records → DATETIME (avoid time zone conversion effects)
• When you want to record updated timestamps automatically → TIMESTAMP (convenient auto‑update behavior)

Where YEAR Is Useful

• Fiscal‑year categories, release years, founding years, etc. → Manage compactly (1 byte)

4.3 How to Choose String Types

How to Decide Between CHAR and VARCHAR

When You Should Use CHAR

• Length is always constant: prefecture codes, country codes, fixed‑length identifiers, etc.
• Data that needs fast access and is updated infrequently

When You Should Use VARCHAR

• Length varies: names, email addresses, titles, etc.
• The best default choice in most situations

Should You Use TEXT?

Advantages of TEXT

• Supports large text (descriptions, article bodies, etc.)

Cautions for TEXT

• Indexing is limited (prefix indexes are required)
• Performance can degrade when used in JOINs or WHERE clauses
• Search and sorting can be heavy

Recommendation:
Use TEXT for “large strings” such as bodies or notes,
and use VARCHAR as much as possible for everything else.

4.4 How to Choose the JSON Type

The JSON type is very useful, but you need to use it “correctly.”

When JSON Works Well

• Flexible settings or data with a variable number of fields
• Limited lookup use cases, or when the application expands/parses it
• Storing lightweight data that doesn’t require a master table

When JSON Is a Bad Fit

• Data you search frequently
• Data used for filtered searches, aggregation, or JOINs
• Structured data that should be normalized

Rule of thumb:
Data you need to search should be normalized and treated as structure.
Don’t forget that JSON is “flexible but weak for search.”

4.5 How to Choose ENUM / SET Types

When ENUM Is Appropriate

• States are fixed: e.g., status (draft / published / archived)
• A small set of options that rarely changes

Cautions for ENUM

• Adding or changing values requires ALTER TABLE
• Risk of losing consistency with application‑side definitions

When SET Is Appropriate

• Small‑scale data that requires multiple selection (e.g., available weekdays, or when there are only a few tag options)

Cautions for SET

• Combinations of values can become complex
• Managing multi‑value states can become difficult

4.6 How to Choose Binary / BLOB Types

BINARY / VARBINARY

• Tokens, IDs, hash values, etc.
• Fixed‑length binary (e.g., 16‑byte UUIDs)

Typical Use Cases for BLOB Types

• Small files, thumbnail images, encrypted data

But Note

• Storing large files in the database makes backups heavier
• Read/write load also increases → In real systems, external storage + path management is recommended

5. Practice: How to Use the “Data Type Reference List” When Designing Tables

In this section, we’ll explain how to use the MySQL data type list in a practical workflow when designing tables.
Rather than just memorizing types, organizing “why you choose that type” through logic and steps leads to higher‑quality database design.

5.1 Step 1: Clarify the Column’s “Purpose” and “Nature”

First, clearly define what will be stored in the column.

Checklist

• Is it numeric, string, date, or a flag?
• Is it variable-length or fixed-length?
• What is the maximum value or maximum length?
• Should NULL be allowed?
• Is it likely to grow in the future?

If you proceed with vague assumptions here, type selection becomes unnecessarily complicated later.

5.2 Step 2: Estimate the Required Range and Format

Next, estimate the upper/lower bounds, character length, and required precision of the values you will store.

Example: ID Column

• Will the maximum record count reach tens of millions? Hundreds of millions? → This helps you determine whether INT is sufficient or if BIGINT is necessary.

Example: Product Name

• Average 15–25 characters, maximum around 50? → VARCHAR(50) is sufficient. TEXT is unnecessary.

Example: Price

• Is precision required? → You should select something like DECIMAL(10,2) .

5.3 Step 3: Consider Data Volume and Performance

Choosing MySQL data types directly impacts performance.

Key Considerations

• Types that are too large → consume storage and make indexes heavier
• TEXT / BLOB → degrade search performance
• JSON → flexible but weak for searching
• TIMESTAMP → efficient for automatic updates and comparisons

In particular, columns with indexes should use the lightest possible data type.

5.4 Step 4: Validate Types with Sample Data

After designing the table, insert dozens to hundreds of rows of test data and verify behavior.

What to Check

• Are there unintended rounding or truncation issues?
• Is VARCHAR sufficient, or should it be TEXT ?
• Does datetime sorting and searching behave as expected?
• JSON read/write performance

Testing with realistic data reduces “theoretical misjudgments.”

5.5 Step 5: Consider Scalability and Maintainability

In the final stage of table design, check whether future changes will be easy.

Examples of Heavy Type Changes

• ENUM (requires ALTER TABLE when adding values)
• TEXT → VARCHAR (expanding is easy, shrinking is risky)
• FLOAT → DECIMAL (can change semantics)

Choose types by judging whether future expansion is likely.

5.6 Step 6: CREATE TABLE Example (Practical Sample)

Below is an example table reflecting common data type selection standards in a typical web application.

CREATE TABLE products (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2) NOT NULL,
    stock        INT UNSIGNED NOT NULL DEFAULT 0,
    status       ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
    description  TEXT,
    created_at   DATETIME NOT NULL,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Key Points in This Example

• id : Uses BIGINT UNSIGNED considering future scale
• name : Medium-length variable string → VARCHAR
• price : Monetary value → precise DECIMAL
• stock : Inventory count → INT UNSIGNED
• status : Fixed set of values → ENUM
• description : Potentially long text → TEXT
• updated_at : Convenient auto-updating TIMESTAMP

As shown, every type choice should have a clear rationale, and being able to articulate that reasoning is a hallmark of good design.

6. Common Mistakes and How to Avoid Them

Because MySQL offers many data types, several common mistakes frequently appear in real-world projects.
This section highlights typical pitfalls and provides practical countermeasures.

6.1 Using Excessively Large Data Types

Common Examples

• Making all IDs BIGINT
• Setting every string to VARCHAR(255)
• Using TEXT for non-textual content

Problems

• Wasted storage
• Bloated indexes harming query performance
• Wasted network bandwidth (larger data transfers)

How to Avoid

• Estimate the maximum and minimum values in advance
• Set VARCHAR lengths based on evidence
• Use TEXT only when truly necessary

6.2 Using FLOAT/DOUBLE for Decimal Values (Precision Issues)

Common Examples

• Storing prices as FLOAT
• Numeric comparisons fail due to rounding errors

How to Avoid

• Use DECIMAL for money and precision‑critical values
• Avoid FLOAT / DOUBLE except for scientific or temporary calculations

6.3 Oversized VARCHAR Columns

Common Examples

• Using VARCHAR(255) as a default
• Setting large lengths for columns that only store about 30 characters

Problems

• Wasted memory and storage
• Indexes often become heavier

How to Avoid

• Estimate average and maximum lengths from actual data
• Avoid unnecessarily large sizes (e.g., email addresses → VARCHAR(100) )

6.4 Overusing TEXT Types

Common Examples

• Assuming “string = TEXT”
• Using TEXT for data that needs filtering or searching

Problems

• Many indexing limitations
• Heavy LIKE searches
• Slower processing in JOINs

How to Avoid

• Use TEXT only for long‑form content such as descriptions or bodies
• Store searchable strings in VARCHAR whenever possible

6.5 Choosing Date/Time Types Without Understanding Their Properties

Common Examples

• Using DATETIME for everything
• Using DATETIME for “updated at,” so it does not auto‑update
• Timestamps shift due to time zone differences

How to Avoid

• Need auto‑update: TIMESTAMP
• Want to avoid time zone shifts: DATETIME
• Only need the year: YEAR

6.6 Using ENUM / SET Too Casually

Common Examples

• Using ENUM even though the options may change in the future
• Using SET like a comma‑separated list

Problems

• Changes require ALTER TABLE
• Inconsistency with application‑side definitions is more likely

How to Avoid

• If values may increase, manage them in a separate master table
• Use ENUM only when the set is small and truly fixed

6.7 Packing Too Much into JSON “Because It’s Convenient”

Common Examples

• Packing structures that should be normalized into JSON
• Storing frequently searched data inside JSON

Problems

• Search performance degrades
• Indexes are less effective / harder to use
• More parsing work on the application side
• Schema‑less design makes consistency easier to break

How to Avoid

• Use JSON only for data you do not search
• Limit it to “small, variable data” such as settings
• Normalize information used for JOINs and searching

6.8 Underestimating the Cost of Type Changes

Problems

• ALTER TABLE can be extremely heavy on large tables
• Service downtime or long lock times may occur
• In some cases, data migration is required

How to Avoid

• Plan for future growth during the design stage
• Use ENUM carefully
• Leave room in DECIMAL precision/scale (but don’t overdo it)

7. Summary

MySQL provides a wide variety of data types, and the type you choose can significantly affect performance, scalability, and maintainability.
Especially in environments like web applications where data tends to grow, early design decisions directly impact long‑term quality.

Key Takeaways in This Article

• Data types are critical factors that affect “value kind, range, precision, storage, and performance.”
• Numeric, string, date/time, JSON, ENUM/SET, and BLOB types each have strengths and weaknesses.
• Integers, strings, and date/time types are used most often, so choosing correctly matters.
• JSON and ENUM are convenient, but misuse can make maintenance difficult.
• Overusing TEXT and BLOB can degrade performance.
• A rational design approach is: “Purpose → Range → Performance → Scalability.”

A Design Checklist You Can Use Starting Today

• Is the column’s purpose clearly defined?
• Do you understand the maximum and minimum possible values?
• Is there evidence behind the chosen VARCHAR length?
• Are you using DECIMAL for money?
• Are “updated at” timestamps managed with TIMESTAMP and auto-update where appropriate?
• Before using ENUM , did you consider whether values may increase in the future?
• Are you avoiding designs where searching becomes slow due to overusing JSON?
• Did you design to avoid heavy ALTER TABLE operations on large datasets?

Finally

There isn’t always a single “correct answer” for choosing MySQL data types.
However, if you choose with purpose and future growth in mind, you can prevent major issues and keep your data structure clean.

Ultimately, the best choice depends on your project’s nature, data volume, and application requirements. But if you use the criteria introduced in this article as your foundation, you should be able to make better data type choices in any situation.

Next, we’ll introduce an FAQ section that summarizes questions frequently asked in real work.
When you’re unsure about type selection, checking this FAQ first will help you decide more smoothly.

8. FAQ

Choosing MySQL data types is an area where decisions can vary significantly depending on real‑world experience.
Here we compile common questions from development teams and provide short, clear answers.

Q1. Should I use INT or BIGINT?

A: Use INT by default. Use BIGINT only if it may exceed 2.1 billion in the future.

INT (4 bytes) supports up to about 2.1 billion and is sufficient for most applications.
Consider BIGINT for logs, high‑traffic services, or systems that may issue extremely large numbers of IDs.

Q2. Should I use VARCHAR or TEXT?

A: Use VARCHAR if you need searching. Use TEXT for long‑form content.

• VARCHAR : Index‑friendly and better for searches
• TEXT : Good for long content but not ideal for searching or JOINs

※ Overusing TEXT can lead to performance degradation.

Q3. Is it okay to store money as DOUBLE?

A: No. Always use DECIMAL.

DOUBLE and FLOAT can introduce rounding errors, so they’re not suitable for money or precision‑critical values.
In business systems, DECIMAL(10,2) or DECIMAL(12,2) is a common standard.

Q4. I don’t understand the difference between DATETIME and TIMESTAMP. How should I choose?

A: Use TIMESTAMP if you need auto‑updates. Use DATETIME if you need to store a precise timestamp without time zone conversion.

• TIMESTAMP : Auto‑update and time zone conversion
• DATETIME : Not affected by time zones; stores the date/time “as‑is”

DATETIME is often used for logs and history tables.

Q5. Is it safe to use ENUM?

A: It’s useful only when values are “guaranteed not to change.”

ENUM is lightweight and convenient, but adding or changing values requires ALTER TABLE.
For fields that may grow, master management in a separate table is recommended.

Q6. Can I just use JSON for now?

A: Use JSON only for data you don’t search. Data you search should be normalized.

JSON is flexible, but it has performance weaknesses.

• Weak for searching
• Index structures become more complex
• Harder to maintain data consistency

It works well for settings and options—attributes that are not used as search conditions.

Q7. How should I decide the maximum length for VARCHAR?

A: Estimate the maximum based on real data and add a small buffer.

Example: Email address → VARCHAR(100) is sufficient
Example: Username → VARCHAR(50) is often fine

“255 just because” is not recommended.

Q8. If I chose the wrong type, can I change it later?

A: Yes, but it can be very heavy on large tables.

ALTER TABLE often involves full scans and rebuilds,
which may cause downtime or long lock times.

→ Careful planning at the initial design stage is the most important.