MySQL Data Types Explained: Choosing the Right Column Type

Choosing the right MySQL data type matters more than most developers realize. The wrong choice wastes storage, slows down queries, and can even cause incorrect results. For strings, use VARCHAR for variable-length data and CHAR for fixed-length codes. For numbers, use INT for most cases and BIGINT for IDs that might exceed 4.2 billion rows. For dates, use DATETIME unless you need timezone awareness, then use TIMESTAMP.

Consider a status column that only holds three possible values ('active', 'inactive', 'pending'). Using VARCHAR(10) for this column takes about 11 bytes per row (1 byte length prefix + up to 10 bytes data). Using TEXT would add unnecessary overhead with a 2-byte length prefix and different storage behavior. On a table with 10 million rows, choosing the right data type saves significant storage. Data types matter.

Data Type Quick Reference

String Types: VARCHAR vs CHAR vs TEXT

MySQL has three main string types. Each serves a different purpose. Choosing between them affects storage size, query performance, and index capabilities. The wrong choice for a high-volume table can waste gigabytes of disk space and slow down every query that touches those columns.

VARCHAR

VARCHAR stores variable-length strings. You specify a maximum length, and MySQL only uses as much space as needed. Use VARCHAR for most text fields.

-- Good: Email addresses vary in length
CREATE TABLE users (
  email VARCHAR(255)  -- Max 255 characters, uses only what's needed
);

-- Good: Names vary in length
CREATE TABLE users (
  name VARCHAR(100)   -- Max 100 characters
);

VARCHAR uses 1 byte for length if the max length is under 255 characters, or 2 bytes for longer. The actual storage is the string length plus the length prefix.

CHAR

CHAR stores fixed-length strings. MySQL always pads the string to the specified length. Use CHAR for data that always has the same length.

-- Good: Country codes are always 2 characters
CREATE TABLE users (
  country_code CHAR(2)  -- Always 'US', 'PH', 'GB', etc.
);

-- Good: State abbreviations are always 2 characters
CREATE TABLE users (
  state CHAR(2)  -- Always 'CA', 'NY', etc.
);

-- Bad: Names are not fixed length
CREATE TABLE users (
  name CHAR(100)  -- Always uses 100 bytes, even for 'Bob'
);

CHAR is faster than VARCHAR for fixed-length data because MySQL does not need to check the length. But the difference is negligible for most applications.

TEXT

TEXT stores up to 65,535 bytes (not characters, with multi-byte charsets like utf8mb4 the effective character count is lower). Use TEXT for large text fields that might exceed VARCHAR's limits.

-- Good: Blog post content can be very long
CREATE TABLE posts (
  content TEXT  -- Up to 65,535 bytes
);

-- Good: Comments can be long
CREATE TABLE comments (
  body TEXT
);

-- Bad: Status is never that long
CREATE TABLE orders (
  status TEXT  -- Wasteful, use ENUM or VARCHAR(20)
);

TEXT has limitations: you cannot use it in indexes directly (you need to specify a prefix length), and sorting TEXT columns is slower than sorting VARCHAR columns. If you need to search the full content of a TEXT column, consider using a full-text index instead of a regular B-tree index. For columns that need to be indexed and are always under a certain length, VARCHAR is the better choice.

Integer Types: TINYINT to BIGINT

MySQL has five integer types. The difference is the range of values they can store and the space they use.

TINYINT    -- 1 byte,  -128 to 127 (or 0 to 255 unsigned)
SMALLINT   -- 2 bytes, -32768 to 32767
MEDIUMINT  -- 3 bytes, -8388608 to 8388607
INT        -- 4 bytes, -2147483648 to 2147483647
BIGINT     -- 8 bytes, -9223372036854775808 to 9223372036854775807

The storage difference between these types adds up fast on large tables. A table with 100 million rows using INT for the primary key consumes about 400MB just for that column. Switching to BIGINT doubles it to 800MB. Using TINYINT for a boolean flag saves 3 bytes per row compared to INT, which is 300MB across 100 million rows. Think about your data volume when picking integer sizes.

Here is my rule of thumb:

• Status flags, booleans - TINYINT (1 byte)
• Small counters, years - SMALLINT (2 bytes)
• Most IDs, counts - INT (4 bytes)
• Large IDs, big counters - BIGINT (8 bytes)

-- Good: Status flags
CREATE TABLE users (
  is_active TINYINT  -- 0 or 1, uses 1 byte
);

-- Good: Most IDs
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY  -- Up to 4.2 billion
);

-- Good: Order IDs that might grow very large
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY  -- Up to 18.4 quintillion
);

A common mistake is using BIGINT for everything "just in case." BIGINT uses 8 bytes per row. On a table with 100 million rows, that is 800MB just for the ID column. INT uses 4 bytes, saving 400MB. Use BIGINT only when you genuinely need it.

DECIMAL vs FLOAT vs DOUBLE

This is critical for financial data. Do not use FLOAT or DOUBLE for money.

-- Good: Financial data
CREATE TABLE orders (
  total DECIMAL(10, 2)  -- Exact precision, up to 99999999.99
);

-- Bad: Financial data with floating point
CREATE TABLE orders (
  total FLOAT  -- Imprecise, will lose cents
);

-- Bad: Financial data with double precision
CREATE TABLE orders (
  total DOUBLE  -- Better than FLOAT, still imprecise
);

FLOAT and DOUBLE use binary floating point, which cannot exactly represent decimal fractions. 0.1 + 0.2 does not equal 0.3 in floating point. DECIMAL stores exact decimal values. For money, always use DECIMAL. The precision loss in FLOAT is not just theoretical. Over thousands of transactions, rounding errors accumulate and you end up with accounts that do not reconcile. Use FLOAT only for scientific measurements where approximate values are acceptable.

Date and Time Types

MySQL has three date/time types: DATE, DATETIME, and TIMESTAMP.

-- DATE: Date only, no time
CREATE TABLE events (
  event_date DATE  -- '2026-07-15'
);

-- DATETIME: Date and time, no timezone
CREATE TABLE events (
  created_at DATETIME  -- '2026-07-15 14:30:00'
);

-- TIMESTAMP: Date and time, stored as UTC, converted to session timezone
CREATE TABLE events (
  created_at TIMESTAMP  -- '2026-07-15 14:30:00'
);

DATETIME stores the literal date and time. If you store '2026-07-15 14:30:00', that is what you get back, regardless of timezone. This makes it predictable and easy to debug, since the stored value always matches what you see in queries.

TIMESTAMP stores UTC internally and converts to the session timezone when displayed. This is useful for applications that need to handle multiple timezones.

My recommendation: use DATETIME unless you specifically need timezone conversion. DATETIME is simpler and more predictable.

BOOLEAN: Use TINYINT(1)

MySQL does not have a native BOOLEAN type. BOOLEAN is an alias for TINYINT(1).

-- These are equivalent
CREATE TABLE users (
  is_active BOOLEAN
);

CREATE TABLE users (
  is_active TINYINT(1)
);

-- Insert values
INSERT INTO users (is_active) VALUES (1);  -- true
INSERT INTO users (is_active) VALUES (0);  -- false
INSERT INTO users (is_active) VALUES (TRUE);   -- same as 1
INSERT INTO users (is_active) VALUES (FALSE);  -- same as 0

Use BOOLEAN for readability. It makes your schema clearer about the intent.

ENUM: Fixed Set of Values

ENUM stores one value from a predefined list. It is space-efficient and ensures data integrity.

-- Good: Status has a fixed set of values
CREATE TABLE orders (
  status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

-- Insert values
INSERT INTO orders (status) VALUES ('pending');  -- Works
INSERT INTO orders (status) VALUES ('unknown');  -- Error: invalid enum value

ENUM is great for data integrity but annoying for changes. If you need to add a new status, you have to ALTER the table. For values that change frequently, use VARCHAR instead. One practical tip: if you use ENUM, order your values by likelihood of use rather than alphabetically. MySQL stores ENUM values as integers internally, and the order you define them determines the numeric mapping. This matters if you ever query by the numeric value.

Common Mistakes with Data Types

Mistake 1: Using TEXT for short status values. A status column that holds 'active', 'inactive', or 'pending' should use ENUM or VARCHAR(20), not TEXT. TEXT adds unnecessary overhead and cannot be used directly in indexes.

-- Bad: Wasteful and slow
CREATE TABLE users (
  status TEXT
);

-- Good: Compact and indexable
CREATE TABLE users (
  status ENUM('active', 'inactive', 'pending')
);

Mistake 2: Using FLOAT for monetary values. Floating point arithmetic introduces rounding errors that accumulate over thousands of transactions. A payment system using FLOAT could end up with mismatched balances that are impossible to reconcile.

-- Bad: Loses cents over time
CREATE TABLE payments (
  amount FLOAT
);

-- Good: Exact decimal arithmetic
CREATE TABLE payments (
  amount DECIMAL(10, 2)
);

Mistake 3: Using BIGINT for every ID column. While BIGINT prevents future capacity issues, it doubles storage compared to INT. On a table with 100 million rows, that is an extra 400MB just for the primary key. If your application will never exceed 4.2 billion rows, INT UNSIGNED is sufficient.

Key Takeaways

• VARCHAR is variable-length with a 1-byte length prefix for strings under 255 characters. CHAR is fixed-length and padded with spaces.
• TEXT cannot be indexed directly (use prefix indexes). Use VARCHAR(255) for indexed string columns.
• DECIMAL for money, FLOAT/DOUBLE for scientific calculations. Never use FLOAT for currency.
• INT UNSIGNED goes up to 4.2 billion. Use BIGINT only when you expect more rows than that.
• TIMESTAMP is 4 bytes with timezone conversion. DATETIME is 5 bytes (+ 0-6 bytes for fractional seconds) with no timezone handling. Choose based on whether you need timezone awareness.

FAQ

Should I always use INT for IDs?

For most applications, yes. INT UNSIGNED supports up to 4.2 billion rows. If your table will never exceed that, use INT. If there is any chance it will exceed 4.2 billion rows, use BIGINT UNSIGNED.

Is VARCHAR(255) always better than VARCHAR(100)?

No. Both VARCHAR(100) and VARCHAR(255) use 1 byte for the length prefix when the max length is 255 or less. The difference is maximum capacity, not storage overhead. Use the smallest size that makes sense for the data. VARCHAR(255) for a status column that only holds 'active' or 'inactive' is misleading.

When should I use JSON instead of separate columns?

Use JSON when the structure varies between rows or when you need to store flexible key-value data. Use separate columns when the structure is fixed and you need to query individual fields efficiently. MySQL can index JSON columns, but separate columns are always faster to query.