🔡 MySQL Textual Types (VARCHAR, TEXT, ENUM, SET) – Complete Guide with Syntax & Examples

---

🧲 Introduction – Why Textual Data Types Matter in MySQL?

In MySQL, textual data types are essential for storing names, descriptions, user inputs, tags, statuses, labels, and much more. MySQL provides several string-based column types like VARCHAR, TEXT, ENUM, and SET, each optimized for different storage, size, and query scenarios.

Choosing the right one ensures your application’s data is:

• Stored efficiently
• Validated correctly
• Queried quickly

🎯 In this guide, you’ll learn:

• The syntax, use cases, and behaviors of VARCHAR, TEXT, ENUM, and SET
• How to choose the best type for your data
• Real-world examples and performance tips

---

🔤 1. VARCHAR(n) – Variable-Length Strings

🔹 Syntax

VARCHAR(n)

• n: Maximum number of characters (not bytes).
• Maximum total row length: 65,535 bytes (varies by charset and other columns).

🔹 Example

CREATE TABLE users (
  username VARCHAR(50),
  email VARCHAR(100)
);

Explanation:

• username: Can store up to 50 characters.
• email: Stores user email up to 100 characters.

✅ Best for short-to-medium length strings where the length varies per row.

---

📝 2. TEXT – Large Text Blocks

🔹 Variants

Type	Max Size	Description
TINYTEXT	255 bytes	For short notes
TEXT	64 KB	Standard long-form content
MEDIUMTEXT	16 MB	For full articles, logs
LONGTEXT	4 GB	Very large documents or dumps

🔹 Example

CREATE TABLE articles (
  title VARCHAR(255),
  body TEXT
);

Explanation:

• title: A short string title.
• body: A long-form article body (up to 64 KB).

⚠️ Limitations:

• Cannot be used with DEFAULT values.
• Requires prefix length for indexing (e.g., CREATE INDEX idx_body ON articles(body(255));).
• Stored outside the row, with a pointer in the main row.

---

📋 3. ENUM – Single-Choice Value from Predefined List

🔹 Syntax

ENUM('value1', 'value2', 'value3')

🔹 Example

CREATE TABLE orders (
  order_id INT,
  status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
);

Explanation:

• status: Only accepts one value from the ENUM list.
• Stored as a numeric index, which saves space.

✅ Great for limited, fixed-status fields like user roles, order states, or binary flags.

---

🧷 4. SET – Multi-Choice Value from Predefined List

🔹 Syntax

SET('value1', 'value2', 'value3')

🔹 Example

CREATE TABLE users (
  id INT,
  interests SET('php', 'mysql', 'css', 'javascript')
);

Explanation:

• interests: Allows multiple values from the set, such as 'php,javascript'.

✅ Useful for tag-like, multiple-choice fields.
⚠️ Hard to normalize, and not suited for relational joins.

---

📊 Comparison – VARCHAR vs TEXT vs ENUM vs SET

Feature	VARCHAR	TEXT	ENUM	SET
Max Length	65,535 bytes (total)	Up to 4 GB	65,535 values	64 values
Default Values	✅ Supported	❌ Not Supported	✅ Supported	✅ Supported
Indexable	✅ Fully	🔸 With prefix only	✅ Fully	✅ Bitmask-based
Multiple Values	❌	❌	❌	✅
Use Case	Names, emails	Articles, logs	Status, flags	Tags, roles, preferences
Storage	In-row	Off-row	Compact integer (1 byte)	Bitmask (1–8 bytes)

---

📘 Best Practices

✅ Tip	💡 Reason
Use VARCHAR for short strings	Efficient and indexable
Use TEXT only for long-form content	Avoids row size limits and truncation
Use ENUM for strict value enforcement	Prevents bad inputs for statuses or categories
Avoid SET in normalized schemas	Difficult to filter and join properly
Always define charset (utf8mb4)	Ensures multilingual and emoji compatibility
Don’t overuse large text fields	Impacts performance on joins and indexes

---

🚀 Real-World Use Cases

Scenario	Recommended Type	Reason
Usernames and Emails	VARCHAR	Variable length, searchable
Product Descriptions	TEXT	May exceed 255 characters
Order Status (pending, complete)	ENUM	Limits input and improves query performance
User Tags or Roles	SET	Allows storing multiple predefined values
Blog Post Title + Body	VARCHAR + TEXT	Title is indexed, body is long content

---

📌 Summary – Recap & Next Steps

MySQL’s textual types provide flexible storage options for character-based data. Use the right type based on length, indexing needs, allowed values, and application logic.

🔍 Key Takeaways

• VARCHAR: Best for short-to-medium text (fully indexable)
• TEXT: Use for long content (no default, partial indexing only)
• ENUM: Enforces predefined single-choice input
• SET: Stores multiple values from a fixed list (use with caution)

⚙️ Real-World Relevance

These types power real-world use cases like form fields, metadata, blog systems, tagging engines, status tracking, and more. Choosing the right type ensures better performance, structure, and safety.

---

❓ FAQ – MySQL Textual Types

---

❓ Can I set a default value for a TEXT column?

❌ No. MySQL does not allow default values on TEXT or BLOB types.

---

❓ Can I index a TEXT field?

✅ Partially. Use a prefix index:

CREATE INDEX idx_content ON articles(body(255));

---

❓ Can I store multiple values in an ENUM?

❌ No. ENUM supports only one value. Use SET if you need multiple.

---

❓ When should I avoid SET?

Avoid when:

• You need normalized tables
• You perform complex searches or joins
• You require dynamic lists

---

❓ How many values can ENUM and SET hold?

• ENUM: Up to 65,535 values
• SET: Up to 64 unique values

---

Share Now :