String types (VARCHAR, CHAR, TEXT) in MySQL - Step-by-Step Execution

Concept Flow - String types (VARCHAR, CHAR, TEXT)

Start: Define column with string type

↓

Choose type: CHAR, VARCHAR, or TEXT?

↓

CHAR

↓

Fixed length

↓

Store data

↓

End

When creating a string column, you pick CHAR for fixed length, VARCHAR for variable length, or TEXT for large text storage.

Execution Sample

MySQL

CREATE TABLE example (
  col_char CHAR(5),
  col_varchar VARCHAR(5),
  col_text TEXT
);

This creates a table with three string columns: fixed length CHAR, variable length VARCHAR, and large TEXT.

Execution Table

Step	Action	Column	Type	Storage Behavior	Result
1	Define column	col_char	CHAR(5)	Fixed length, pads with spaces if shorter	Column created
2	Define column	col_varchar	VARCHAR(5)	Variable length, stores only actual chars	Column created
3	Define column	col_text	TEXT	Stores large text, up to 65,535 bytes	Column created
4	Insert 'abc' into col_char	col_char	CHAR(5)	Stores 'abc ' (padded with 2 spaces)	Value stored as 'abc '
5	Insert 'abc' into col_varchar	col_varchar	VARCHAR(5)	Stores 'abc' exactly	Value stored as 'abc'
6	Insert 'abc' into col_text	col_text	TEXT	Stores 'abc' exactly	Value stored as 'abc'
7	Retrieve col_char	col_char	CHAR(5)	Returns 'abc' (trailing spaces removed)	Output: 'abc'
8	Retrieve col_varchar	col_varchar	VARCHAR(5)	Returns 'abc' exactly	Output: 'abc'
9	Retrieve col_text	col_text	TEXT	Returns 'abc' exactly	Output: 'abc'
10	Insert 'abcdef' into col_char	col_char	CHAR(5)	Truncates to 5 chars 'abcde'	Value stored as 'abcde'
11	Insert 'abcdef' into col_varchar	col_varchar	VARCHAR(5)	Error: Data too long, insert fails	Insert fails
12	Insert large text into col_text	col_text	TEXT	Stores large text up to limit	Value stored
13	Insert text > 65,535 bytes into col_text	col_text	TEXT	Error: data too long	Insert fails
14	End				Table ready with string columns

💡 Execution stops after table creation and data insertion steps complete or error occurs.

Variable Tracker

Variable	Start	After Step 4	After Step 5	After Step 6	After Step 10	After Step 11	After Step 12	After Step 13	Final
col_char	NULL	'abc '	'abc '	'abc '	'abcde'	'abcde'	'abcde'	'abcde'	'abcde'
col_varchar	NULL	NULL	'abc'	'abc'	'abc'	NULL	NULL	NULL	NULL
col_text	NULL	NULL	NULL	'abc'	'abc'	'abc'	'large text'	'large text'	'large text'

Key Moments - 3 Insights

Why does CHAR(5) store 'abc' as 'abc ' with spaces?

CHAR is fixed length, so shorter strings are padded with spaces to fill the defined length, as shown in execution_table step 4.

Why does VARCHAR(5) store 'abc' without extra spaces?

VARCHAR stores only the actual characters without padding, so 'abc' is stored exactly as is, as seen in step 5.

What happens if you insert more characters than the defined length in CHAR or VARCHAR?

For CHAR, the string is truncated to the maximum length allowed, as shown in step 10 where 'abcdef' becomes 'abcde'. For VARCHAR, inserting more characters than the defined length causes an error and the insert fails, as shown in step 11.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What is stored in col_char after inserting 'abc'?

A 'abcde' (truncated)

B 'abc' (no padding)

C 'abc ' (padded with spaces)

D NULL

💡 Hint

Check the Storage Behavior column in step 4 of execution_table.

At which step does inserting too large text into col_text cause an error?

A Step 12

B Step 13

C Step 10

D Step 5

💡 Hint

Look for the row mentioning 'Error: data too long' in execution_table.

If you insert 'abcdef' into col_varchar(5), what will be stored according to the execution_table?

A NULL

B 'abcde' (truncated to 5 chars)

C 'abc ' (padded with spaces)

D 'abcdef' (full string)

💡 Hint

See step 11 in execution_table for VARCHAR behavior on longer input.

Concept Snapshot

String types in MySQL:
- CHAR(n): fixed length, pads with spaces
- VARCHAR(n): variable length, stores exact chars
- TEXT: large text storage, up to 65,535 bytes
Inserts longer than defined length truncate for CHAR
Inserts longer than defined length for VARCHAR cause error and fail
TEXT cannot exceed its max size or insert fails

Full Transcript

This visual execution shows how MySQL string types CHAR, VARCHAR, and TEXT behave when defining columns and inserting data. CHAR stores fixed length strings padded with spaces. VARCHAR stores variable length strings exactly. TEXT stores large text up to a limit. When inserting shorter strings into CHAR, spaces are added to fill length. VARCHAR stores only actual characters. Inserting longer strings than allowed truncates them for CHAR. For VARCHAR, inserting longer strings than allowed causes an error and the insert fails. TEXT can store large text but errors if too large. The execution table traces each step of defining columns, inserting values, and retrieving them, showing how data is stored and returned. The variable tracker shows how column values change after each insert. Key moments clarify common confusions about padding, truncation, and errors. The quiz tests understanding of these behaviors referencing the execution steps.

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