MySQL Data Types Interview Questions

String types include CHAR (fixed-length), VARCHAR (variable-length), TEXT (large text), and BLOB (binary). Choice depends on data size and performance needs. CHAR is faster for fixed-width, VARCHAR saves space for variable content.

-- String types
CHAR(50): Fixed 50 bytes (padded if shorter)
VARCHAR(255): Variable up to 255 bytes
TEXT: Large text up to 65KB
LONGTEXT: Very large text up to 4GB
BLOB: Binary data up to 64KB
LONGBLOB: Large binaries up to 4GB

-- Examples
CREATE TABLE users (
    id INT PRIMARY KEY,
    country_code CHAR(2),  -- Always 2 chars
    email VARCHAR(255),  -- Variable length
    bio TEXT,  -- Long text
    photo BLOB  -- Image data
);

-- Performance: CHAR faster but wastes space
-- VARCHAR flexible, saves space but slower

Why it matters: String type choice affects storage and query speed.

Real applications: Email uses VARCHAR, country codes use CHAR.

Common mistakes: Using VARCHAR(1000) when 255 sufficient, CHAR for variable.

Date/time types include DATE (YYYY-MM-DD), TIME (HH:MM:SS), DATETIME (both), TIMESTAMP (with timezone), and YEAR. TIMESTAMP auto-updates and uses less storage than DATETIME.

-- Date/Time types
DATE: Stores date only (3 bytes)
TIME: Stores time only (3 bytes)
DATETIME: Stores date & time (8 bytes)
TIMESTAMP: Date & time + timezone (4 bytes)
YEAR: Year only (1 byte)

-- Examples
CREATE TABLE events (
    event_date DATE,  -- '2024-12-25'
    event_time TIME,  -- '14:30:00'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Auto-set on insert
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Range considerations
DATE: 1000-01-01 to 9999-12-31
DATETIME: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP: 1970-01-01 to 2038-01-19 (Y2K38 concern)

Why it matters: Right type ensures efficient storage and correct calculations.

Real applications: TIMESTAMP for audit trails, DATE for birthdays.

Common mistakes: DATETIME instead of TIMESTAMP for auto-update, not considering Y2K38.

ENUM stores one value from predefined list (1-65535 options). SET stores multiple values from list. Both are space-efficient but inflexible if values change frequently.

-- ENUM: One value from list
CREATE TABLE orders (
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

INSERT INTO orders VALUES ('shipped');  -- Valid
INSERT INTO orders VALUES ('invalid');  -- Error

-- SET: Multiple values from list
CREATE TABLE permissions (
    user_roles SET('admin', 'editor', 'viewer', 'commenter')
);

INSERT INTO permissions VALUES ('admin, editor');  -- Multiple values

-- Storage-efficient
-- ENUM: 1 byte for <256 options
-- SET: 1-8 bytes depending on number of options

-- Performance trade-off
-- PROS: Efficient storage, prevents invalid values
// CONS: Inflexible, adding values requires ALTER TABLE

Why it matters: Efficient storage for fixed values, prevents invalid data.

Real applications: Status fields, user permissions, predefined categories.

Common mistakes: Using ENUM for frequently changing values, not considering future expansion.

Efficiency strategy involves right-sizing types (INT not BIGINT for IDs), using ENUM/SET for fixed values, DECIMAL for precision, and VARCHAR for variable-length. Profile to eliminate waste.

-- Inefficient: Over-sized types
CREATE TABLE inefficient (
    id BIGINT,  -- Could be INT
    name VARCHAR(1000),  -- Rarely >100 chars
    age INT,  -- Could be TINYINT
    phone VARCHAR(255)  -- Always ~11 chars, use CHAR(11)
);

-- Efficient: Right-sized types
CREATE TABLE efficient (
    id INT AUTO_INCREMENT,  -- Sufficient for most tables
    name VARCHAR(100),  -- Practical limit
    age TINYINT UNSIGNED,  -- 0-255, perfect for ages
    phone CHAR(11),  -- Fixed length
    status ENUM('active', 'inactive'),  -- One byte
    created TIMESTAMP  -- Auto-managed
);

-- Storage calculation
-- Inefficient: 8 + 1000 + 4 + 255 = 1267 bytes per row
// Efficient: 4 + 100 + 1 + 11 + 1 + 4 = 121 bytes per row
// 10x more efficient!