The Basics Of MySQL TEXT Data Type

The Basics of MySQL TEXT Data Type

In MySQL, the TEXT data type is used to store large amounts of text-based data efficiently. It is commonly used for storing descriptions, blog posts, comments, logs, and JSON-like data.

1. Understanding MySQL TEXT Data Type

TEXT Type	Storage Size	Maximum Length
TINYTEXT	1 byte overhead	255 characters (255 B)
TEXT	2 bytes overhead	65,535 characters (64 KB)
MEDIUMTEXT	3 bytes overhead	16,777,215 characters (16 MB)
LONGTEXT	4 bytes overhead	4,294,967,295 characters (4 GB)

✔ Unlike VARCHAR, TEXT does not require a fixed length and is stored separately from the table row.

2. Creating a Table with a TEXT Column

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

✔ The content column can store up to 65,535 characters.

3. Inserting Data into a TEXT Column

INSERT INTO articles (title, content) 
VALUES ('Introduction to MySQL', 'MySQL is an open-source relational database management system...');

✔ No need to specify a length when defining a TEXT column.

4. Retrieving and Formatting TEXT Data

Basic Selection

SELECT title, content FROM articles;

Limiting the Output

To get only the first 100 characters:

SELECT title, LEFT(content, 100) AS preview FROM articles;

✔ This helps display previews for large text fields.

5. Updating TEXT Data

UPDATE articles 
SET content = 'MySQL is widely used for web applications...'
WHERE id = 1;

✔ You can update a TEXT column like any other data type.

6. Searching in TEXT Columns

Using LIKE for Partial Matching

SELECT * FROM articles 
WHERE content LIKE '%MySQL%';

✔ Searches for the word "MySQL" in the content column.

Using FULLTEXT Index for Faster Searches

For large text searches, use FULLTEXT indexing (only supported in TEXT, VARCHAR, and CHAR columns).

ALTER TABLE articles ADD FULLTEXT(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

✔ More efficient than LIKE queries.

7. Converting and Manipulating TEXT Data

Changing Case

Convert to uppercase:

SELECT UPPER(content) FROM articles;

Convert to lowercase:

SELECT LOWER(content) FROM articles;

Replacing Text in a Column

Replace "MySQL" with "MariaDB":

SELECT REPLACE(content, 'MySQL', 'MariaDB') FROM articles;

Finding Text Length

SELECT LENGTH(content) FROM articles;

✔ Returns the length in bytes.

SELECT CHAR_LENGTH(content) FROM articles;

✔ Returns the length in characters.

8. Best Practices for Using TEXT Data Type

✅ Use VARCHAR if the text is small (≤ 255 characters) for better indexing performance.
✅ Use FULLTEXT index for efficient text searches.
✅ Avoid storing frequently updated data in TEXT to reduce performance overhead.
✅ Use LEFT() or SUBSTRING() when displaying previews instead of retrieving the entire column.

9. Summary

TEXT stores large amounts of text (up to 4GB for LONGTEXT).
It is stored outside the table row for efficient space management.
FULLTEXT indexing is recommended for fast searches.
Use VARCHAR instead of TEXT for short strings to improve performance.

Would you like additional real-world examples, such as storing JSON, logs, or blog posts? 🚀

Souy Soeng