MySQL Character Types: CHAR, VARCHAR, and TEXT

What Are Character Types in MySQL?

Character types are used to store textual data in MySQL tables. They are ideal for storing names, addresses, descriptions, or any string-based information. The three primary character types are:

1. CHAR – Fixed-length character strings
2. VARCHAR – Variable-length character strings
3. TEXT – Large variable-length character strings

1. CHAR: Fixed-Length Strings

CHAR stores fixed-length strings. If the string you store is shorter than the defined length, MySQL pads it with spaces to match the specified length. This makes CHAR efficient for storing strings of uniform length.

Syntax:

CHAR(length)

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name CHAR(10),
    last_name CHAR(10)
);

INSERT INTO employees (id, first_name, last_name)
VALUES (1, 'John', 'Doe');

SELECT id, first_name, last_name FROM employees;

Step-by-Step Explanation:

1. We create a table employees with first_name and last_name as CHAR(10).
2. We insert a record with first_name = 'John' and last_name = 'Doe'.
3. Even though 'John' is 4 characters, MySQL stores it as 'John ' (padded with 6 spaces).
4. Queries return the padded string, but trailing spaces are ignored in comparisons.

Use Cases for CHAR:

• Storing codes or identifiers of fixed length (e.g., country codes, postal codes).
• When consistent storage size is critical for performance.

2. VARCHAR: Variable-Length Strings

VARCHAR stores variable-length strings. Unlike CHAR, it does not pad with spaces. Instead, it stores only the characters you provide, plus 1 or 2 bytes for length information.

Syntax:

VARCHAR(length)

Example:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(50),
    phone VARCHAR(15)
);

INSERT INTO customers (id, email, phone)
VALUES (1, '[email protected]', '1234567890');

SELECT id, email, phone FROM customers;

Step-by-Step Explanation:

1. We create a table customers with email and phone as VARCHAR.
2. We insert a record with an email and phone number.
3. MySQL stores only the actual string length, making storage efficient.
4. Queries return the exact string without any padding.

Use Cases for VARCHAR:

• Storing names, emails, and addresses with varying lengths.
• When space efficiency is important but string length varies.

Important Note:

• The maximum length of a VARCHAR is 65535 bytes, but the effective limit is lower depending on row size and character set.

3. TEXT: Large Variable-Length Strings

TEXT is designed for storing very large strings, such as articles, descriptions, or logs. Unlike VARCHAR, TEXT columns do not allow a default value and are stored separately from the table row in most cases.

Syntax:

TEXT

MySQL provides several TEXT types based on length:

• TINYTEXT – up to 255 characters
• TEXT – up to 65,535 characters
• MEDIUMTEXT – up to 16,777,215 characters
• LONGTEXT – up to 4,294,967,295 characters

Example:

CREATE TABLE blog_posts (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    content TEXT
);

INSERT INTO blog_posts (id, title, content)
VALUES (1, 'MySQL Tutorial', 'This is a comprehensive tutorial on MySQL character types...');

SELECT title, content FROM blog_posts;

Step-by-Step Explanation:

1. We create a table blog_posts with content as a TEXT column.
2. We insert a long string into the content column.
3. MySQL handles storage efficiently for large text.
4. TEXT types are ideal for storing data that exceeds normal string lengths.

Use Cases for TEXT:

• Blog articles, comments, product descriptions.
• Storing large JSON objects or XML documents.

Key Differences Between CHAR, VARCHAR, and TEXT