The Essential Guide to MySQL VARCHAR Data Type

The VARCHAR data type in MySQL is used to store variable-length strings, making it ideal for storing names, emails, addresses, and other text-based values with varying lengths. Unlike the TEXT data type, VARCHAR is stored inline with table rows and provides better indexing performance.

1. Understanding MySQL VARCHAR Data Type

VARCHAR Characteristics

Stores variable-length character strings.
Maximum length: 65,535 characters, depending on row size limits.
Requires 1 or 2 bytes of extra storage for length:
- 1 byte for strings ≤ 255 characters.
- 2 bytes for strings > 255 characters.

2. Declaring a VARCHAR Column

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) NOT NULL
);

✔ The name column can store up to 100 characters, while email can store up to 255 characters.

3. Storing and Retrieving VARCHAR Data

Inserting Data

INSERT INTO users (name, email) 
VALUES ('John Doe', 'johndoe@example.com');

Selecting Data

SELECT name, email FROM users;

4. VARCHAR vs TEXT: Key Differences

Feature	VARCHAR	TEXT
Max Length	65,535 (row size limit)	4GB (`LONGTEXT`)
Storage	Stored inline with table rows	Stored separately from table rows
Indexing	Fully indexed	Requires FULLTEXT index
Use Case	Short to medium text fields	Large text content (blogs, logs)

✔ Use VARCHAR for short/medium text fields and TEXT for large content.

5. Best Practices for VARCHAR

✅ Use VARCHAR instead of TEXT when indexing is needed.
✅ Set a reasonable maximum length to avoid excessive memory use.
✅ Use VARCHAR(255) for email addresses (common practice).
✅ For fixed-length strings, use CHAR instead for faster performance.

6. Example Use Cases

Storing User Information

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150),
    phone_number VARCHAR(20),
    email VARCHAR(255) UNIQUE
);

Updating a VARCHAR Column

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

Finding the Length of a VARCHAR Value

SELECT LENGTH(name) FROM users;

✔ Returns length in bytes.

SELECT CHAR_LENGTH(name) FROM users;

✔ Returns length in characters.

7. Summary

VARCHAR is efficient for variable-length text storage.
Maximum size is 65,535 characters, depending on row limits.
It is fully indexed, unlike TEXT.
Use VARCHAR for short-to-medium text fields, and TEXT for large content.

Would you like examples on optimizing VARCHAR performance? 🚀

Souy Soeng

The Essential Guide to MySQL VARCHAR Data Type