On gists

Generated columns (Virtual/Stored)

MySql MySql tricks MySql - advanced

examples.sql Raw #

-- Základní matematické operace
amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED

-- Práce s textem
full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

-- Použití CASE
status_description VARCHAR(50) GENERATED ALWAYS AS (
    CASE 
        WHEN status = 1 THEN 'Active'
        WHEN status = 0 THEN 'Inactive'
        ELSE 'Unknown'
    END
) VIRTUAL

-- Práce s datumy
age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL

-- Komplexnější výpočty
total_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE
        WHEN tax_rate IS NULL THEN amount
        ELSE amount * (1 + tax_rate/100)
    END
) STORED

-- Formátování a manipulace s textem
formatted_phone VARCHAR(20) GENERATED ALWAYS AS (
    CONCAT('+420 ', SUBSTR(phone, 1, 3), ' ', SUBSTR(phone, 4, 3), ' ', SUBSTR(phone, 7))
) VIRTUAL

advanced.sql Raw #

CREATE TABLE orders (
    -- Základní sloupce
    id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    discount_percent DECIMAL(5,2),
    created_at TIMESTAMP,
    
    -- Generated columns
    -- Finanční výpočty
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    discount_amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price * discount_percent / 100) VIRTUAL,
    final_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price * (1 - discount_percent / 100)) STORED,
    
    -- Časové údaje
    year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL,
    quarter INT GENERATED ALWAYS AS (QUARTER(created_at)) VIRTUAL,
    is_weekend BOOLEAN GENERATED ALWAYS AS (DAYOFWEEK(created_at) IN (1, 7)) VIRTUAL,
    
    -- Kategorizace
    price_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN unit_price < 100 THEN 'Budget'
            WHEN unit_price < 500 THEN 'Standard'
            ELSE 'Premium'
        END
    ) VIRTUAL,
    
    -- Indexy na generated columns
    INDEX idx_year_quarter (year, quarter),
    INDEX idx_final_price (final_price),
    INDEX idx_price_category (price_category)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    birth_date DATE,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    
    -- Text manipulace
    full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    email_domain VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
    initials CHAR(2) GENERATED ALWAYS AS (CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) STORED,
    
    -- Věkové a finanční výpočty
    age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL,
    age_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) < 25 THEN 'Junior'
            WHEN TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) < 35 THEN 'Mid'
            ELSE 'Senior'
        END
    ) VIRTUAL,
    
    salary_band CHAR(1) GENERATED ALWAYS AS (
        CASE 
            WHEN salary < 30000 THEN 'A'
            WHEN salary < 50000 THEN 'B'
            WHEN salary < 80000 THEN 'C'
            ELSE 'D'
        END
    ) STORED,
    
    -- Indexy
    INDEX idx_email_domain (email_domain),
    INDEX idx_age_category (age_category),
    INDEX idx_salary_band (salary_band)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    stock INT,
    category VARCHAR(50),
    
    -- Stavové sloupce
    stock_status VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN stock = 0 THEN 'Out of Stock'
            WHEN stock < 10 THEN 'Low Stock'
            WHEN stock < 50 THEN 'In Stock'
            ELSE 'Well Stocked'
        END
    ) VIRTUAL,
    
    -- Vyhledávání
    search_text TEXT GENERATED ALWAYS AS (
        CONCAT_WS(' ', name, description, category)
    ) STORED,
    
    -- Příznaky
    is_premium BOOLEAN GENERATED ALWAYS AS (price > 1000) VIRTUAL,
    needs_restock BOOLEAN GENERATED ALWAYS AS (stock < 20) VIRTUAL,
    
    -- Indexy
    INDEX idx_stock_status (stock_status),
    FULLTEXT INDEX idx_search (search_text)
);

readme.txt Raw #

Pár tipů k indexování generated columns:

STORED columns jsou vždy indexovatelné
VIRTUAL columns jsou indexovatelné, ale s určitými omezeními
Index na VIRTUAL column může mít větší režii při INSERT/UPDATE
Pro často dotazované generated columns je dobré zvážit STORED + INDEX
Můžeš vytvářet i složené indexy zahrnující generated columns
Pro fulltextové vyhledávání je lepší použít STORED column

Generated columns + indexy jsou super kombinace pro:

Optimalizaci často používaných vyhledávání
Zrychlení řazení
Efektivní filtrování podle vypočítaných hodnot
Cachování složitých výpočtů



Generated columns jsou super pro:

Automatické výpočty
Formátování dat
Odvození hodnot z jiných sloupců
Cachování často používaných výpočtů (pomocí STORED)