Learning Focus

Use this lesson to understand Text Types with practical syntax and examples.

MySQL TEXT Types: Complete Guide

---

What are TEXT Types?

TEXT data types store large blocks of variable-length text (up to 4 GB). Unlike VARCHAR, they are designed for content exceeding standard string limits and do not count toward the row size limit.

---

TEXT Type Categories

Type	Max Characters*	Storage	Use Case
TINYTEXT	255	1 byte (length) + data	Short notes, summaries
TEXT	65,535 (~16KB)	2 bytes + data	Articles, comments
MEDIUMTEXT	16,777,215 (~16MB)	3 bytes + data	Large documents, JSON data
LONGTEXT	4,294,967,295 (~4GB)	4 bytes + data	Books, logs, backups

• Assuming utf8mb4 (4 bytes per character). Divide by 4 for actual character limits.

---

Declaration Syntax

column_name TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT
    [CHARACTER SET charset]
    [COLLATE collation]

Example:

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    full_book LONGTEXT
);

---

When to Use TEXT vs VARCHAR

Factor	VARCHAR	TEXT
Max Length	65,535 bytes (row limit)	4 GB
Storage	In-row	Off-page (efficient for large data)
Indexing	Full indexing	Prefix indexing only
Performance	Faster for small data	Optimized for large content

Use TEXT when:

• Content exceeds 16KB
• Storing large JSON/XML documents
• Avoiding row size limits

---

Practical Examples

Example 1: Blog Post Content

CREATE TABLE blog_posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    excerpt TINYTEXT,
    body TEXT
);

Example 2: Storing JSON Data

CREATE TABLE api_logs (
    log_id INT PRIMARY KEY,
    request_body MEDIUMTEXT  -- Large JSON payloads
);

Example 3: Full-Text Search

ALTER TABLE books ADD FULLTEXT INDEX (longtext_content);

---

Key Limitations

1. No Default Values:

   -- Invalid:
   description TEXT DEFAULT 'No content'
   

2. Index Restrictions:

   CREATE INDEX idx ON table (text_col(100));  -- Prefix index only
   

3. Sorting Limits:

   • Only first max_sort_length bytes (default 1024) are used for sorting.

---

Storage Overhead

Type	Length Prefix
TINYTEXT	1 byte
TEXT	2 bytes
MEDIUMTEXT	3 bytes
LONGTEXT	4 bytes

Example:

• TEXT storing 10,000 characters in utf8mb4:2 bytes (length) + (10,000 4 bytes) = 40,002 bytes

---

Best Practices

1. Use the smallest type possible:

   • TINYTEXT for tweets/short notes
   • TEXT for most articles

2. Avoid SELECT * with TEXT columns to reduce memory usage.

3. Compress large data: Use functions like COMPRESS() for logs/backups.

4. Use VARCHAR for searchable fields:

   -- Prefer:
   summary VARCHAR(500)
   -- Over:
   summary TINYTEXT
   

---

Common Issues

1. Truncation Warnings

INSERT INTO table (tinytext_col) VALUES (REPEAT('a', 300)); -- Fails for TINYTEXT

2. Performance Bottlenecks

• Large TEXT columns slow down queries. Use pagination:

  SELECT LEFT(longtext_col, 100) AS preview FROM table;
  

3. Collation Conflicts

-- Specify charset for special characters:
content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

---

Summary

• TINYTEXT: Tiny strings (<255 chars)
• TEXT: Standard long text (~16KB)
• MEDIUMTEXT: Large documents (~16MB)
• LONGTEXT: Massive content (~4GB)

---

Next Topics

1. BLOB Types (binary large objects)
2. Full-Text Search
3. String Compression

Concept Map

flowchart LR
    A[Schema Context] --> B[Text Types]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_text_types (id INT PRIMARY KEY, value TEXT_TYPES);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: Enum, Set - Review the previous lesson to reinforce context.
• Next: Binary, Var Binary, Blob - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.