MySQL VARCHAR

Introduction

In this chapter, we will learn about the VARCHAR data type in MySQL. VARCHAR is used to store variable-length character strings and is one of the most commonly used data types for storing text data. Understanding how to use VARCHAR effectively can help optimize storage and improve query performance. We will cover the syntax for defining VARCHAR columns, its default size, examples, and important considerations for using VARCHAR in MySQL.

Syntax

The basic syntax for defining a VARCHAR column in MySQL is:

VARCHAR(length)

• length: Specifies the maximum number of characters that the column can store. The length can be any value from 0 to 65,535, but the actual maximum length depends on the maximum row size (65,535 bytes) and the character set used.

Default Size

• The default size for a VARCHAR column is 1 if no length is specified, though it is recommended to always specify the length explicitly.

Example

Let’s go through an example where we create a table with VARCHAR columns and demonstrate their usage.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with VARCHAR Columns:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    address VARCHAR(255)
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, address) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '123 Main St, New Delhi, India'),
('Priya', 'Singh', 'priya.singh@example.com', '456 Oak St, Mumbai, India'),
('Amit', 'Kumar', 'amit.kumar@example.com', '789 Pine St, Bangalore, India'),
('Neha', 'Verma', 'neha.verma@example.com', '321 Maple St, Hyderabad, India'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '654 Elm St, Chennai, India');

5. Query the Employees Table:

SELECT * FROM employees;

Output

In this example, the first_name, last_name, email, and address columns are defined as VARCHAR with different lengths to accommodate varying lengths of text data.

Important Considerations

• Length Limitation: The maximum length for a VARCHAR column is determined by the maximum row size (65,535 bytes) and the character set. Be mindful of the character set used as it affects the storage size.
• Storage Requirements: VARCHAR columns use 1 byte to store the length of the string if the maximum length is 255 or less, and 2 bytes if it is greater than 255.
• Performance: VARCHAR is suitable for variable-length strings, but for fixed-length strings, CHAR might be more efficient.
• Data Validation: Always specify an appropriate length for VARCHAR columns to ensure proper data validation and avoid excessive storage allocation.
• Truncation: Be cautious of potential truncation if the input data exceeds the specified length of the VARCHAR column. MySQL will truncate the data and may generate a warning.

Conclusion

The VARCHAR data type is a versatile and commonly used data type in MySQL for storing variable-length character strings. This chapter covered the syntax for defining VARCHAR columns, provided examples with different lengths, and discussed important considerations.