? MySQL VARCHAR, ENUM & SET

? Quick Overview

In MySQL, efficiently storing string data depends on the nature of the data. VARCHAR is for general text, while ENUM and SET are optimized for restricting inputs to a predefined list of valid options.

? Key Concepts

• VARCHAR Variable Character: Best for names, emails, or titles where length varies but has a limit.
• ENUM Enumeration: Restricts a column to a single value chosen from a list (like a Radio Button).
• SET Set: Restricts a column to zero or more values chosen from a list (like Checkboxes).

? Syntax & Theory

1. VARCHAR (Flexible String)

Stores variable-length strings up to a specified maximum. It only uses as much space as needed plus 1-2 bytes for length.

-- Syntax: VARCHAR(max_length)
CREATE TABLE users (
    username VARCHAR(50),  -- Up to 50 chars
    bio VARCHAR(255)       -- Up to 255 chars
);

2. ENUM (Single Choice)

A string object with a value chosen from a list of permitted values. Internally stored as an integer index.

-- Syntax: ENUM('val1', 'val2', ...)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'shipped', 'delivered', 'canceled')
);

3. SET (Multiple Choice)

A string object that can have zero or more values, chosen from a list of permitted values. Stored as a bitmap.

-- Syntax: SET('val1', 'val2', ...)
CREATE TABLE user_preferences (
    user_id INT,
    notifications SET('email', 'sms', 'push', 'slack')
);

? Code Examples (Operations)

-- 1. Inserting ENUM (One value only)
INSERT INTO orders (status) VALUES ('shipped');

-- 2. Inserting SET (Multiple values separated by commas)
INSERT INTO user_preferences (user_id, notifications) 
VALUES (1, 'email,sms');

-- 3. Filtering SET using FIND_IN_SET function
SELECT * FROM user_preferences 
WHERE FIND_IN_SET('sms', notifications);

? Visual Comparison