Differences Between CHAR, VARCHAR, and TEXT in MySQL

When designing tables in MySQL, one of the important decisions is choosing the right data type for text columns. MySQL provides several data types for storing strings: CHAR, VARCHAR, and TEXT (along with their variants). Choosing the wrong type can affect query performance, storage usage, and even application functionality. This article covers the detailed differences between the three.

CHAR – Fixed Length

CHAR is a string data type with a fixed length. If you define CHAR(10) and store the string "Hello" (5 characters), MySQL will pad the remaining space with spaces until the length becomes 10 characters. When the data is read, the trailing spaces are automatically removed.

• Maximum length: 255 characters.
• Storage: Always uses the amount of space defined.
• Best for: Data with a fixed and uniform length.

-- Example of using CHAR
CREATE TABLE country_codes (
    code CHAR(2) NOT NULL PRIMARY KEY,  -- ID, US, MY, SG
    country_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE membership_cards (
    card_number CHAR(16) NOT NULL PRIMARY KEY,  -- always 16 digits
    holder_name VARCHAR(100) NOT NULL,
    type CHAR(1) NOT NULL   -- 'G' = Gold, 'S' = Silver, 'P' = Platinum
) ENGINE=InnoDB;

VARCHAR – Variable Length

VARCHAR is a string data type with a variable length. MySQL only stores the characters that actually exist, plus 1 or 2 bytes as a length prefix. If you define VARCHAR(255) and store "Hello" (5 characters), only 6 bytes are used (5 characters + 1 prefix byte).

• Maximum length: 65,535 characters (depending on the row size limit and charset).
• Storage: Matches the actual data length + 1-2 bytes of overhead.
• Best for: Text data with a variable length such as names, emails, or titles.

-- Example of using VARCHAR
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    full_name VARCHAR(200) NOT NULL,
    phone_number VARCHAR(15),
    city VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

TEXT and Its Variants

The TEXT type is used to store very long text, such as article content, long product descriptions, or comments. TEXT has several variants with different capacities:

• TINYTEXT: Maximum 255 bytes.
• TEXT: Maximum 65,535 bytes (~64 KB).
• MEDIUMTEXT: Maximum 16,777,215 bytes (~16 MB).
• LONGTEXT: Maximum 4,294,967,295 bytes (~4 GB).

-- Example of using TEXT
CREATE TABLE blog_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    summary VARCHAR(500),
    article_body MEDIUMTEXT NOT NULL,  -- for long article content
    meta_description VARCHAR(160),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_title_body (title, article_body)
) ENGINE=InnoDB;

Key Differences in a Table

-- Demonstrating the storage differences
CREATE TABLE string_type_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    char_column    CHAR(20),       -- always 20 bytes (if latin1)
    varchar_column VARCHAR(20),    -- variable, max 20 characters
    text_column    TEXT            -- max 65,535 bytes, stored off-row
) ENGINE=InnoDB;

INSERT INTO string_type_demo (char_column, varchar_column, text_column)
VALUES ('Halo', 'Halo', 'Halo');

-- Check the stored length
SELECT
    LENGTH(char_column)    AS char_length,     -- 4 (spaces trimmed on read)
    LENGTH(varchar_column) AS varchar_length,  -- 4
    LENGTH(text_column)    AS text_length      -- 4
FROM string_type_demo;

Differences in Space Behavior

-- CHAR trims trailing spaces during comparison
CREATE TABLE space_test (
    c CHAR(5),
    v VARCHAR(5)
) ENGINE=InnoDB;

INSERT INTO space_test VALUES ('abc  ', 'abc  ');

-- For CHAR, 'abc  ' is considered equal to 'abc'
-- For VARCHAR, 'abc  ' is NOT equal to 'abc'
SELECT
    c = 'abc' AS char_equal,      -- 1 (TRUE)
    v = 'abc' AS varchar_equal    -- 0 (FALSE)
FROM space_test;

Limitations of TEXT

Although flexible, the TEXT type has several important limitations:

• TEXT columns cannot have a DEFAULT value.
• TEXT columns cannot be fully indexed — only a partial index with a prefix length is possible.
• TEXT data is stored outside the main row, so access can be slower.
• TEXT columns cannot be used directly in GROUP BY without a prefix.

-- Creating a partial index on a TEXT column
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    INDEX idx_content_prefix (content(100))  -- index the first 100 characters
) ENGINE=InnoDB;

Guide to Choosing a Data Type

• Use CHAR for: postal codes, country codes, license plate numbers, status (1 character), or data that always has the same length.
• Use VARCHAR for: names, emails, usernames, titles, URLs, phone numbers, or short-to-medium text with a variable length.
• Use TEXT for: article content, long descriptions, HTML content, or any text that may exceed 255 characters.

Conclusion

CHAR, VARCHAR, and TEXT are each designed for different needs. CHAR is efficient for fixed-length data, VARCHAR is flexible for variable text, and TEXT is for very long content. Choosing the right data type not only affects storage efficiency but also query performance and indexing capabilities. Always consider the maximum length of the data you will store and whether the column needs to be indexed when choosing between the three.