MySql - String data types

String Data Types (VARCHAR, TEXT, CHAR) in MySQL

MySQL supports a wide variety of string data types designed to store text, characters, and binary data. Choosing the correct string type is crucial for optimizing performance, storage, and application logic. This document provides an in-depth explanation of the most commonly used string types in MySQL: CHAR, VARCHAR, and TEXT, along with other auxiliary types and best practices.

1. Introduction to String Data Types

String data types in MySQL are used to store textual and binary data. These types can handle anything from a single character to long blocks of text and are essential for fields like names, descriptions, email addresses, and more. Each string type has unique features, storage methods, and performance implications.

Major string data types include:

• CHAR: Fixed-length string
• VARCHAR: Variable-length string
• TEXT: Large text data
• BINARY and VARBINARY: For binary (non-text) data
• ENUM: String object with a predefined set of values
• SET: A string object that can have zero or more values

2. CHAR – Fixed-Length String

2.1 Description

The CHAR type stores strings of a fixed length. If the string is shorter than the defined length, MySQL pads it with spaces.

2.2 Syntax

CHAR(n)

Where n defines the length in characters (maximum: 255 characters).

2.3 Example

CREATE TABLE users (
  country_code CHAR(2),
  gender CHAR(1)
);

In this example, country_code always stores exactly two characters, and gender stores one character ('M', 'F', etc.).

2.4 Storage and Performance

• Uses fixed space even for shorter values (padded with spaces).
• Faster access when data length is predictable.
• Good for fields like country codes, fixed-length codes, or flags.

2.5 Padding Behavior

Trailing spaces are removed when comparing CHAR values unless the BINARY keyword is used.

3. VARCHAR – Variable-Length String

3.1 Description

VARCHAR is used to store variable-length strings. It stores only the actual string and a small amount of overhead for storing the length of the string.

3.2 Syntax

VARCHAR(n)

Where n is the maximum number of characters (up to 65,535 bytes, depending on row size and character set).

3.3 Example

CREATE TABLE customers (
  name VARCHAR(100),
  email VARCHAR(255)
);

This allows names up to 100 characters and emails up to 255 characters in length.

3.4 Storage

Storage includes 1 or 2 bytes for length + the number of bytes in the string.

• 1 byte for strings ≤ 255 characters
• 2 bytes for longer strings

3.5 Pros and Cons

• Pros: Saves space compared to CHAR for variable-length data.
• Cons: Slightly more complex to access due to variable length.

3.6 When to Use VARCHAR

• Names, addresses, comments, and descriptions
• Any field where the length of text varies significantly

4. TEXT – Large Text Fields

4.1 Description

The TEXT type is used for storing large blocks of text, such as paragraphs, articles, or logs.

4.2 TEXT Types

There are four levels of TEXT types:

• TINYTEXT: Up to 255 bytes
• TEXT: Up to 65,535 bytes (64 KB)
• MEDIUMTEXT: Up to 16,777,215 bytes (16 MB)
• LONGTEXT: Up to 4,294,967,295 bytes (4 GB)

4.3 Example

CREATE TABLE posts (
  post_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  content TEXT
);

This table stores blog posts with large content using the TEXT type.

4.4 Storage and Indexing

• Stored separately from the main table row (except TINYTEXT in some cases).
• Cannot have default values.
• Indexed only with prefixes (e.g., INDEX(content(100))).

4.5 Best Use Cases

• Articles, comments, descriptions
• Storing large user input or logs

5. Comparison: CHAR vs VARCHAR vs TEXT

6. Other String Types

6.1 ENUM

ENUM is a string object with a predefined set of values. Internally stored as numeric indexes, it’s space-efficient and easy to use.

CREATE TABLE users (
  status ENUM('active', 'inactive', 'banned')
);

6.2 SET

SET is similar to ENUM but allows multiple values from a set.

CREATE TABLE products (
  tags SET('electronics', 'furniture', 'clothing', 'sports')
);

6.3 BINARY and VARBINARY

These types are for binary data rather than text, such as images or files.

CREATE TABLE files (
  file_id INT,
  file_data BLOB
);

7. Character Sets and Collations

String columns must be defined with character sets and collations, which determine encoding and sorting.

7.1 Common Character Sets

• utf8: Supports most characters (up to 3 bytes)
• utf8mb4: Full Unicode, supports emoji (up to 4 bytes)
• latin1: Western European

7.2 Setting Character Sets

CREATE TABLE names (
  full_name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

8. Indexing String Columns

Indexing long string columns can slow down performance and increase disk usage. MySQL allows prefix indexing for long strings:

CREATE INDEX idx_name ON customers(name(20));

9. Storage Considerations

• VARCHAR and TEXT types are length-sensitive – UTF8 characters may use 1–4 bytes
• Table row size limit: 65,535 bytes – affects how much VARCHAR you can store
• TEXT is stored off-row, impacting performance in some queries

10. Best Practices

• Use CHAR for fixed-size fields like ISO codes or gender
• Use VARCHAR for variable-length text such as names or emails
• Use TEXT for long paragraphs, logs, or articles
• Always choose the correct character set (prefer utf8mb4 for full Unicode support)
• Don’t forget to index wisely—especially on TEXT and large VARCHAR fields

MySQL string data types are diverse and highly customizable. Choosing the right one for each use case ensures better performance, efficient storage, and a robust schema design. Here's what we've covered:

• CHAR: Fixed-length, fast for predictable sizes
• VARCHAR: Flexible and efficient for most text fields
• TEXT: Ideal for large and unstructured text
• ENUM/SET: For controlled vocabularies
• BINARY/VARBINARY: For binary data
• Collation and encoding: Important for sorting and multi-language support

With this knowledge, database developers and designers can structure their tables to optimize data retrieval, storage, and accuracy across various applications and use cases.