Understanding LONG and LONG VARCHAR in MariaDB

• LONG and LONG VARCHAR (referring to MEDIUMTEXT) are data types designed to store larger text content compared to VARCHAR. MEDIUMTEXT can hold text data up to 2^23 or around 8,388,608 bytes.

• VARCHAR is a variable-length string data type. This means it can store strings of different sizes, up to a maximum limit you specify when defining the column.

When to use MEDIUMTEXT

• Use MEDIUMTEXT when you expect to store text data that might be longer than what a VARCHAR with a reasonable size limit can handle. This could be things like product descriptions, blog posts, or article content.

Things to keep in mind

• There are other text data types in MariaDB besides MEDIUMTEXT, like TEXT and BLOB, each with different storage capacities and indexing capabilities. Choose the data type that best suits the size and usage patterns of your text data.
• MEDIUMTEXT (and LONG/LONG VARCHAR) cannot be used in conjunction with regular indexes. If you need to search the text data efficiently, consider using the TEXT data type (which allows even larger text storage) along with a FULLTEXT index.

---

Creating a Table with MEDIUMTEXT Column

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

This code creates a table named "articles" with three columns:

• content: A MEDIUMTEXT column to store the article content, allowing longer text entries.
• title: A VARCHAR column with a maximum length of 255 characters to store the article title.
• id: An auto-incrementing integer as the primary key.

Inserting Data with MEDIUMTEXT

INSERT INTO articles (title, content)
VALUES ("My Awesome Blog Post", "This is a very long and informative blog post about [insert topic here]. It can contain detailed explanations, code snippets, or even images (although storing images directly in a MEDIUMTEXT column is not recommended).");

This code inserts a new record into the "articles" table. The title is set to "My Awesome Blog Post" and the content is a sample text demonstrating how MEDIUMTEXT can hold longer content.

Selecting Data with MEDIUMTEXT

SELECT title, SUBSTRING(content, 1, 100) AS excerpt
FROM articles;

This code retrieves all records from the "articles" table. It selects the title column and uses the SUBSTRING function to display the first 100 characters of the content column as an "excerpt". This demonstrates that you can still work with parts of the MEDIUMTEXT data even though it allows larger storage.

---

VARCHAR

• Use VARCHAR if you know the maximum length of your text data will be relatively short (typically less than 255 characters). It's more efficient for smaller strings and allows regular indexing.

TEXT and BLOB Types

• BLOB (Binary Large Object)
  This is a generic data type for storing binary data, including large text. BLOBs offer more flexibility as they can store any kind of data, not just text. However, working with BLOBs often requires additional processing compared to text-specific data types.
• TEXT
  This can store even larger text data than MEDIUMTEXT, with a maximum capacity of 64KB (around 65,535 characters). It's a good choice for content like long descriptions or articles where you expect some length but not massive amounts of text. However, TEXT columns also cannot be used with regular indexes.

TEXT with FULLTEXT Index

• If you need to search the text data efficiently, consider using the TEXT data type along with a FULLTEXT index. This allows full-text searches within the text content. While TEXT itself can't have regular indexes, a FULLTEXT index helps with searching specific keywords or phrases within the text data.

• For very large text or any kind of binary data
  BLOB (but be aware of additional processing needs).
• For larger text (up to 64KB) with search functionality
  TEXT with a FULLTEXT index.
• For medium-sized text (up to 8MB)
  MEDIUMTEXT (if indexing isn't crucial).
• For short text (less than 255 characters)
  VARCHAR with a reasonable size limit.