String types (VARCHAR, CHAR, TEXT) in MySQL - Deep Dive

Overview - String types (VARCHAR, CHAR, TEXT)

What is it?

String types in MySQL are ways to store text data. VARCHAR stores variable-length strings, CHAR stores fixed-length strings, and TEXT stores large amounts of text. Each type has different storage and performance characteristics. They help organize and manage text data efficiently in databases.

Why it matters

Without understanding string types, you might waste space or slow down your database. For example, using a fixed-length type for variable text wastes space, while using a large text type for small strings can slow queries. Proper use improves speed, storage, and data integrity in real applications.

Where it fits

Before learning string types, you should understand basic data types and how databases store data. After mastering string types, you can learn about indexing, text searching, and optimizing database performance.

Mental Model

Core Idea

String types define how text is stored and managed in a database, balancing space, speed, and size limits.

Think of it like...

Think of string types like different sized boxes for storing letters: CHAR is a fixed-size box always filled to the brim, VARCHAR is a flexible box that fits the letters exactly, and TEXT is a big storage trunk for lots of letters.

┌───────────────┐
│   String Types │
├───────────────┤
│ CHAR          │ Fixed length, pads spaces
│ VARCHAR       │ Variable length, stores exact
│ TEXT          │ Large text, stored separately
└───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding CHAR type basics

Concept: CHAR stores fixed-length strings padded with spaces.

CHAR(n) reserves space for exactly n characters. If you store fewer characters, MySQL adds spaces to fill the length. For example, CHAR(5) storing 'cat' will save 'cat ' with two spaces.

Result

Data always uses the same space, which can be wasteful but fast for fixed-size data.

Knowing CHAR pads data helps you predict storage size and understand why trailing spaces appear.

2

Foundation

Understanding VARCHAR type basics

Concept: VARCHAR stores variable-length strings with length prefix.

VARCHAR(n) stores strings up to n characters. It only uses space for the actual string plus 1 or 2 bytes to record length. For example, VARCHAR(10) storing 'cat' uses 3 bytes plus length info.

Result

Storage is efficient for varying string sizes, saving space compared to CHAR.

Understanding VARCHAR saves space when string lengths vary, improving storage efficiency.

3

Intermediate

Exploring TEXT type for large strings

Concept: TEXT stores large strings separately from the main table row.

TEXT can store up to 65,535 characters. Unlike CHAR and VARCHAR, TEXT data is stored outside the main table row with a pointer inside. This allows very large text but can slow access.

Result

You can store long text like descriptions or comments, but queries may be slower.

Knowing TEXT stores data separately explains why large text fields affect performance differently.

4

Intermediate

Comparing storage and performance trade-offs

🤔Before reading on: do you think CHAR or VARCHAR is faster for short strings? Commit to your answer.

Concept: Different string types affect speed and storage differently.

CHAR is faster for fixed-length data because it avoids length checks. VARCHAR saves space for variable data but adds overhead. TEXT is slowest due to separate storage. Choosing the right type depends on your data patterns.

Result

You can optimize your database by matching string types to your data needs.

Understanding trade-offs helps you balance speed and storage for better database design.

5

Advanced

Impact of character sets and collation

🤔Before reading on: does VARCHAR(10) always mean 10 bytes? Commit to your answer.

Concept: Character sets affect how many bytes each character uses.

UTF-8 characters can use 1 to 4 bytes each. VARCHAR(10) means 10 characters, but storage bytes vary. This affects max storage size and indexing. Choosing character sets impacts storage and compatibility.

Result

You must consider character sets to avoid storage errors and support languages.

Knowing character sets influence storage prevents bugs and supports international text.

6

Expert

Internal storage and row size limits

🤔Before reading on: can you store a 70,000 character string in VARCHAR? Commit to your answer.

Concept: MySQL limits row size and stores large TEXT differently internally.

VARCHAR max size is 65,535 bytes minus overhead. TEXT types store data off-row with pointers. Large strings can cause row size errors. Understanding internal limits helps design schemas that avoid errors and optimize performance.

Result

You can design tables that handle large text without hitting MySQL limits.

Knowing internal storage and limits helps prevent common errors and optimize large text handling.

Under the Hood

MySQL stores CHAR as fixed-length fields padded with spaces, making access predictable and fast. VARCHAR stores length-prefixed variable-length strings inline, saving space but requiring length checks. TEXT types store large data outside the main row with a pointer, allowing big text but slower access. Character sets determine byte size per character, affecting storage and indexing. MySQL enforces a maximum row size, limiting combined field sizes.

Why designed this way?

These types balance storage efficiency, speed, and flexibility. CHAR suits fixed-size data for speed. VARCHAR saves space for variable data. TEXT handles large text beyond row limits. This design evolved to support diverse applications and languages while managing storage constraints.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│    CHAR       │──────▶│ Fixed size,   │       │ Stored inline │
│ (fixed length)│       │ padded spaces │       │ in row        │
└───────────────┘       └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   VARCHAR     │──────▶│ Variable size │       │ Stored inline │
│ (variable)    │       │ length + len  │       │ in row        │
└───────────────┘       └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│    TEXT       │──────▶│ Large text    │──────▶│ Stored outside │
│ (large text)  │       │ pointer in row│       │ main row      │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does VARCHAR(n) always use n bytes of storage? Commit to yes or no.

Common Belief: VARCHAR(n) always uses n bytes of storage regardless of string length.

Reality: VARCHAR uses only as many bytes as the string length plus 1 or 2 bytes for length info.

Why it matters: Thinking VARCHAR wastes fixed space leads to inefficient schema design and wasted storage.

Quick: Is CHAR always faster than VARCHAR? Commit to yes or no.

Common Belief: CHAR is always faster than VARCHAR because it has fixed length.

Reality: CHAR can be faster for fixed-length data, but VARCHAR is often faster for variable-length data due to less wasted space and better cache use.

Why it matters: Assuming CHAR is always faster can cause poor performance if data varies in length.

Quick: Can TEXT fields be indexed like VARCHAR? Commit to yes or no.

Common Belief: TEXT fields can be fully indexed just like VARCHAR fields.

Reality: TEXT fields require prefix indexing and cannot be fully indexed due to size and storage differences.

Why it matters: Misunderstanding this leads to inefficient queries and unexpected slowdowns.

Quick: Does VARCHAR(10) mean 10 bytes of storage in all character sets? Commit to yes or no.

Common Belief: VARCHAR(10) means 10 bytes of storage regardless of character set.

Reality: VARCHAR(10) means 10 characters, but byte size depends on character set (e.g., UTF-8 can use multiple bytes per character).

Why it matters: Ignoring character sets can cause storage errors and data truncation.

Expert Zone

1

VARCHAR columns with large max lengths can cause row size limits even if actual data is small.

2

TEXT fields stored off-row can cause extra disk I/O, impacting performance in large queries.

3

Trailing spaces in CHAR fields are ignored in comparisons but preserved in storage, which can cause subtle bugs.

When NOT to use

Avoid CHAR for highly variable-length data; use VARCHAR instead. Avoid TEXT when you need fast indexing or frequent updates; consider VARCHAR with appropriate length. For very large documents, consider external file storage or specialized full-text search engines.

Production Patterns

Use CHAR for fixed-length codes like country codes. Use VARCHAR for names, emails, and variable text. Use TEXT for comments, descriptions, or logs. Combine with proper indexing and character sets for internationalization and performance.

Connections

Data Types

String types are a subset of data types in databases.

Understanding string types deepens your grasp of how databases organize and optimize different kinds of data.

Character Encoding

String types depend on character encoding to determine storage size.

Knowing encoding helps avoid storage errors and supports multilingual data.

Memory Management in Operating Systems

Like string types, memory management balances fixed and variable size allocations.

Understanding memory allocation strategies clarifies why fixed and variable length storage differ in databases.

Common Pitfalls

#1 Using CHAR for variable-length strings wastes space.

Wrong approach:

CREATE TABLE users (name CHAR(50)); -- storing names of varying length

Correct approach:

CREATE TABLE users (name VARCHAR(50)); -- variable length storage

Root cause:

Misunderstanding that CHAR always reserves full length regardless of actual data size.

#2 Assuming TEXT fields can be indexed fully like VARCHAR.

Wrong approach:

CREATE INDEX idx_text ON articles(content); -- full index on TEXT

Correct approach:

CREATE INDEX idx_text ON articles(content(255)); -- prefix index on TEXT

Root cause:

Not knowing TEXT fields require prefix indexing due to size and storage.

#3 Ignoring character set byte size in VARCHAR length limits.

Wrong approach:

CREATE TABLE messages (msg VARCHAR(10) CHARACTER SET utf8mb4); -- expecting 10 bytes max

Correct approach:

CREATE TABLE messages (msg VARCHAR(10) CHARACTER SET utf8mb4); -- but aware max bytes can be up to 40

Root cause:

Confusing character count with byte count in multi-byte encodings.

Key Takeaways

CHAR stores fixed-length strings padded with spaces, suitable for fixed-size data but can waste space.

VARCHAR stores variable-length strings efficiently with length prefix, ideal for varying text sizes.

TEXT stores large text separately from the main row, allowing big data but slower access and limited indexing.

Character sets affect storage size per character, so VARCHAR length is in characters, not bytes.

Choosing the right string type balances storage, speed, and data size for optimal database performance.

Practice

1. Which MySQL string type is best for storing names that can vary in length but are usually short?

easy

A. VARCHAR

B. CHAR

C. TEXT

D. BLOB

Solution

1. Step 1: Understand string types for short variable-length text

   VARCHAR stores variable-length strings efficiently, saving space for shorter values.

2. Step 2: Compare with other types

   CHAR is fixed-length and wastes space if data varies; TEXT is for large text; BLOB is for binary data.

3. Final Answer:

   VARCHAR -> Option A

4. Quick Check:

   Variable-length short text = VARCHAR [OK]

Hint: Use VARCHAR for short, varying length text [OK]

Common Mistakes:

• Choosing CHAR wastes space for variable length
• Using TEXT for small strings is inefficient
• Confusing BLOB with text types

2. Which of the following is the correct syntax to declare a column named description that can hold up to 255 characters using VARCHAR in MySQL?

easy

A. description VARCHAR(255)

B. description CHAR(255)

C. description TEXT(255)

D. description VARCHAR

Solution

1. Step 1: Recall VARCHAR syntax

   VARCHAR requires a length in parentheses, e.g., VARCHAR(255).

2. Step 2: Check options for correct syntax

   description VARCHAR(255) uses correct syntax; description TEXT(255) uses wrong type; description CHAR(255) uses fixed-length CHAR; description VARCHAR misses length.

3. Final Answer:

   description VARCHAR(255) -> Option A

4. Quick Check:

   VARCHAR needs length in parentheses [OK]

Hint: Always specify length in parentheses for VARCHAR [OK]

Common Mistakes:

• Omitting length for VARCHAR
• Adding length to TEXT type
• Confusing CHAR and VARCHAR syntax

3. Given the table users with a username CHAR(10) column, what will be the result of this query?

INSERT INTO users (username) VALUES ('bob');
SELECT username, LENGTH(username) FROM users WHERE username = 'bob';

medium

A. username = 'bob', LENGTH(username) = 3

B. username = 'bob ', LENGTH(username) = 3

C. username = 'bob', LENGTH(username) = 10

D. username = 'bob ', LENGTH(username) = 10

Solution

1. Step 1: Understand CHAR behavior on insert

   CHAR(10) stores fixed length strings, padding with spaces to length 10.

2. Step 2: Check LENGTH function on padded string

   LENGTH counts all characters including spaces, so LENGTH('bob ') = 10.

3. Final Answer:

   username = 'bob ', LENGTH(username) = 10 -> Option D

4. Quick Check:

   CHAR pads with spaces, LENGTH counts all chars [OK]

Hint: CHAR pads with spaces; LENGTH counts all characters [OK]

Common Mistakes:

• Assuming LENGTH returns 3 for 'bob'
• Ignoring space padding in CHAR
• Confusing CHAR with VARCHAR behavior

4. You have a column defined as notes TEXT. You try to insert a string longer than 65,535 characters and get an error. What is the likely cause and how to fix it?

medium

A. VARCHAR max length is 65,535; use TEXT instead

B. TEXT type has a max length of 65,535 bytes; use MEDIUMTEXT for longer text

C. CHAR cannot store large text; use VARCHAR(65535)

D. TEXT type has no length limit; error is from syntax

Solution

1. Step 1: Recall TEXT max size limits

   TEXT can store up to 65,535 bytes; longer text causes error.

2. Step 2: Choose correct larger text type

   MEDIUMTEXT supports up to 16 million bytes, suitable for longer text.

3. Final Answer:

   TEXT type has a max length of 65,535 bytes; use MEDIUMTEXT for longer text -> Option B

4. Quick Check:

   TEXT max 65,535 bytes; MEDIUMTEXT for more [OK]

Hint: TEXT max 65k chars; use MEDIUMTEXT for bigger text [OK]

Common Mistakes:

• Thinking TEXT has unlimited length
• Using VARCHAR for very large text
• Confusing CHAR with TEXT size limits

5. You want to store user comments that can be very long but also want to optimize storage for short comments. Which MySQL string type should you choose and why?

hard

A. Use VARCHAR(500) to save space for short comments and allow long ones

B. Use CHAR(500) to fix length and speed up queries

C. Use TEXT because it stores large text without fixed length

D. Use VARCHAR(65535) to cover all comment lengths

Solution

1. Step 1: Consider storage needs for very long text

   VARCHAR max length is limited (usually 65,535 bytes total row size), so very long comments may exceed it.

2. Step 2: Evaluate TEXT type for large variable text

   TEXT stores large text efficiently without fixed length and supports very long strings.

3. Final Answer:

   Use TEXT because it stores large text without fixed length -> Option C

4. Quick Check:

   Large variable text = TEXT type [OK]

Hint: Use TEXT for very long text, VARCHAR for shorter variable text [OK]

Common Mistakes:

• Choosing fixed-length CHAR wastes space
• Using VARCHAR too large causes errors
• Assuming VARCHAR can store unlimited text