MySQL VARCHAR Guide: Max Length, Storage Efficiency, Examples

1. Introduction

• Basic specifications and uses of the VARCHAR type
• Technical details regarding the maximum length of the VARCHAR type
• Best practices for efficient database design

2. Basics of VARCHAR

What is VARCHAR?

Differences from CHAR

CREATE TABLE example (
    char_column CHAR(10),
    varchar_column VARCHAR(10)
);

Use Cases and Proper Selection

• CHAR: Data with a fixed length or nearly constant length (e.g., country codes or postal codes).
• VARCHAR: Data with variable length where storage efficiency is important (e.g., usernames or email addresses).

3. Maximum Value of MySQL VARCHAR Type

What Is the Maximum Length of a VARCHAR Type?

Specific Constraint Conditions

1. Impact of Character Set

• In MySQL, the number of bytes per character varies depending on the character set.
• Example:
  • utf8 (1 character = up to 3 bytes)
  • utf8mb4 (1 character = up to 4 bytes)
  Therefore, when using utf8mb4, the maximum length of a VARCHAR type is limited to 16,383 characters (4 bytes × 16,383 = 65,532 bytes).

1. Overall Table Row Size

• In MySQL’s InnoDB storage engine, the maximum data size per row is 65,535 bytes. This includes the data sizes of all columns in the table, so the maximum length of a VARCHAR type is also affected.

Example Calculation: VARCHAR(255)

• When the character set is utf8mb4:
• 1 character = up to 4 bytes
• VARCHAR(255) maximum size = 255 × 4 bytes = 1,020 bytes + length prefix (2 bytes)
• A total of 1,022 bytes is required.

SQL Query Example: Setting Maximum Length

CREATE TABLE example (
    large_text VARCHAR(16383)
) CHARACTER SET utf8mb4;

Practical Considerations

• Optimize the length of VARCHAR: Setting the length of a VARCHAR type unnecessarily large can lead to wasted storage and reduced performance. Choosing an appropriate length is important.
• Be aware of the character set used: Especially when using utf8mb4, you can store data that includes emojis and special characters, but you need to be careful as it can affect storage efficiency.

4. Storage Efficiency and Considerations

How VARCHAR Storage Efficiency Works

1. Storage usage based on actual data length

• VARCHAR consumes storage based on the actual length of the stored data.
• Example: Storing the 5-character string “Hello” in a VARCHAR(100) requires storage for 5 characters plus a length prefix (1–2 bytes).

1. Length Prefix

• VARCHAR data includes a prefix indicating its length.
  • If the data length is 255 bytes or less: the prefix is 1 byte.
  • If the data length is 256 bytes or more: the prefix is 2 bytes.
• Example: Storing 200 characters in a VARCHAR(255) uses 200 bytes + 1 byte (prefix).

Relation to Row Size Limits

• Consideration example: The following SQL may violate the row size limit.

  CREATE TABLE example (
      column1 VARCHAR(32767),
      column2 VARCHAR(32767)
  ) CHARACTER SET utf8mb4;

• With utf8mb4, each character can take up to 4 bytes, so 32767 × 4 bytes (column1) + 32767 × 4 bytes (column2) = 131,068 bytes, exceeding the limit.
• Solution: Use a TEXT type or reduce the length of the VARCHAR columns as needed to avoid the limit.

Best Practices for Improving Storage Efficiency

1. Set column lengths appropriately

• Ideally, the length of a VARCHAR should be determined based on the actual length of the data you will store.
• Example: For storing usernames, VARCHAR(50) is often sufficient.

1. When to use CHAR vs. VARCHAR

• If the data length is fixed or nearly constant, CHAR is more efficient.
• Example: Use CHAR(5) for fixed‑length zip codes (5 digits).

1. Consider the character set

• Choose utf8mb4 only when needed; for better storage efficiency, select utf8 or other lightweight character sets.

1. Design indexes appropriately

• When indexing VARCHAR columns, overly long values can degrade performance.
• Using partial indexes can improve index efficiency.

Practical Considerations

• To maximize storage efficiency and performance, verify the following when designing tables:
• Appropriate data types and lengths for each column.
• Ensure the total row size does not exceed MySQL’s limits.
• For very large string data, consider using TEXT types or external storage.

5. Common Reasons for Choosing VARCHAR(255)

Why is VARCHAR(255) used so often?

1. Historical Background

2. Relation to Index Limits

• Example: When creating a table with an indexed VARCHAR column:

  CREATE TABLE users (
      username VARCHAR(255),
      PRIMARY KEY(username)
  );

3. Compatibility Perspective

• Example: In CMSs like WordPress, many tables use VARCHAR(255). This helps maintain compatibility across various server environments and configurations.

4. Practical Flexibility

• Example:
• Username: Typically 50–100 characters.
• Email address: Up to 320 characters by specification, but 255 characters covers almost everything.

5. Relationship with utf8mb4

Considerations When Choosing VARCHAR(255)

• Avoid Overprovisioning: VARCHAR(255) is convenient, but not always the optimal choice. It’s important to select a length appropriate to the data characteristics.
• Example: For fixed-length data such as country codes or postal codes, using CHAR is more efficient.
• Consider the Overall Database Design: Setting every column in a table to VARCHAR(255) can reduce storage efficiency and increase the risk of exceeding row size limits.

6. Practical Examples and Best Practices

Real-World Example: Setting VARCHAR Types

1. Design Based on Use Cases

For Short Strings

• Example: When designing a table to store usernames:

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL
  );

• VARCHAR(50) provides sufficient length to cover most usernames.

For Long Strings

• Example: When designing a table to store reviews:

  CREATE TABLE reviews (
      id INT AUTO_INCREMENT PRIMARY KEY,
      review_text VARCHAR(1000)
  );

• Because overly long data may be truncated, set the length according to the data specifications.

2. Settings That Consider Storage Efficiency

• Note:
• Do not specify overly large lengths such as VARCHAR(255) unless needed.
• Consider using the TEXT type when appropriate.

Using Partial Indexes

• Example:

  CREATE TABLE articles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(500),
      INDEX (title(100))
  );

• Limiting the index length improves storage efficiency and performance.

3. Error Handling

• Error Example:

  INSERT INTO users (username) VALUES ('a'.repeat(100)); -- error occurs

• Mitigation:
• Perform proper data validation on the application side.
• Enable STRICT mode to maintain data integrity.

4. Best Practices

Optimizing Length

• Analyze the maximum length of the data to be stored and set a length with a slight margin.
• Example: For email addresses, VARCHAR(320) covers the standard.

Choosing Between CHAR and VARCHAR

• Use CHAR for fixed-length data and reserve VARCHAR for variable-length data.

Consider the Overall Table Design

• If you have many VARCHAR columns, be careful not to let the row size become too large.
• When necessary, consider splitting data into separate tables.

Conclusion

7. FAQ (Frequently Asked Questions)

Q1. What is the difference between VARCHAR and TEXT types?

• VARCHAR is suitable for short variable-length string data.
• TEXT is used when handling very long strings (e.g., blog posts or comments).

Q2. What happens if you insert data that exceeds the length of a VARCHAR?

1. When STRICT mode is enabled (recommended setting)

• An error occurs and the data is not inserted.
• Example: sql SET sql_mode = 'STRICT_ALL_TABLES'; INSERT INTO users (username) VALUES ('a'.repeat(300)); -- error occurs

1. When STRICT mode is disabled

• The excess data is automatically truncated and a warning message is generated.
• Because this behavior can affect data integrity, enabling STRICT mode is recommended.

Q3. What is the difference between utf8 and utf8mb4?

• Choose utf8mb4 for applications that use emojis or special characters.
• Consider utf8 if storage efficiency is a priority.

Q4. How to set the optimal length for a VARCHAR column?

• Short strings: For usernames or zip codes, VARCHAR(50) or VARCHAR(10) is sufficient.
• Long strings: For email addresses use VARCHAR(320), for short descriptions use VARCHAR(1000).
• Data analysis: Determine the actual maximum length of your data and set the length with a little extra margin.

Q5. What factors affect VARCHAR performance?

1. Excessively long column length:

• Unnecessarily long columns reduce storage efficiency and also impact query performance.

1. Character set:

• When using utf8mb4, storage usage increases, so be cautious if you frequently use long strings.

1. Index design:

• When indexing long VARCHAR columns, using partial indexes can optimize performance.

Q6. How to handle VARCHAR data that hits storage limits?

1. Reevaluate the VARCHAR length:

• If the column length is set longer than needed, reduce it to a realistic value.

1. Switch to TEXT:

• If storing very long data, consider switching from VARCHAR to TEXT.

1. Normalize the data:

• Split large data into separate tables to reduce row size.

Q7. What should you watch out for when using VARCHAR columns in indexes?

• Use partial indexes: For long string data, set partial indexes to improve efficiency.

  CREATE TABLE articles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(500),
      INDEX (title(100))
  );

• Set appropriate length: If the index length is too large, query performance can degrade, so be careful.

Summary

8. Summary

How to Effectively Use MySQL’s VARCHAR Type

What You Learned in This Article

1. Basic Specification of VARCHAR

• A flexible data type that stores variable-length string data and excels in storage efficiency.
• Understanding the differences from CHAR and making the appropriate choice based on use case is important.

1. Maximum Length of VARCHAR

• Depending on the MySQL version and character set, you can set up to a maximum of 65,535 bytes.
• When using utf8mb4, the maximum length is 16,383 characters (4 bytes per character).

1. Storage Efficiency and Design Considerations

• It’s important to consider length prefixes and row size limits to design an efficient database.
• Avoid unnecessarily large column lengths and optimize the balance between storage and performance.

1. Why VARCHAR(255) Is Commonly Chosen

• Historical reasons and the impact of relaxed index limitations.
• High compatibility and flexibility in real-world use.
• Versatility that accommodates many character sets and data patterns.

1. Practical Examples and Best Practices

• Rich use cases and concrete examples that can be applied immediately after reading.
• Includes detailed advice useful in practice, such as leveraging partial indexes.

1. Resolving Common Questions (FAQ)

• Clarifies differences between VARCHAR and TEXT, index considerations, and how to handle data that exceeds length limits.

Aiming for Efficient Database Design

• Understand data characteristics well and set the minimum necessary length.
• Review the overall table structure and be mindful of row size limits.
• Leverage the flexibility of VARCHAR while choosing the appropriate data type.

Next Steps