INTEGRITY Dokumentace

← Cloudflare D1 / d1 / reference

Generované sloupce

D1 umožňuje definovat generované sloupce na základě hodnot jednoho nebo více jiných sloupců, funkcí SQL nebo dokonce extrahované hodnoty JSON.

Díky tomu můžete data normalizovat už při zápisu do tabulky nebo při čtení z ní, což usnadňuje dotazování a snižuje potřebu složité logiky na straně aplikace.

Generované sloupce mohou mít také definované indexy vůči nim, což může výrazně zvýšit výkon dotazů nad často dotazovanými poli.

Typy generovaných sloupců

Existují dva typy generovaných sloupců:

Pokud je z výrazu generovaného sloupce vynechán, generované sloupce ve výchozím nastavení použijí VIRTUAL typ. STORED typ se doporučuje, pokud je generovaný sloupec výpočetně náročný. Například při parsování velkých struktur JSON.

Definování generovaného sloupce

Generované sloupce lze definovat při vytváření tabulky v CREATE TABLE příkaz, nebo později přes ALTER TABLE příkaz.

Chcete-li vytvořit tabulku, která definuje generovaný sloupec, použijte AS klíčové slovo:

CREATE TABLE some_table (
    -- other columns omitted
    some_generated_column AS <function_that_generates_the_column_data>
)

Jako konkrétní příklad, chcete-li automaticky extrahovat location hodnotu z následujících dat senzoru ve formátu JSON, můžete definovat generovaný sloupec s názvem location (typu TEXT) na základě raw_data sloupec, který ukládá surovou reprezentaci našich dat JSON.

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

Chcete-li definovat generovaný sloupec s hodnotou $.measurement.location, můžete použít json_extract funkci k extrakci hodnoty z raw_data sloupec při každém zápisu do daného řádku:

CREATE TABLE sensor_readings (
    event_id INTEGER PRIMARY KEY,
    timestamp INTEGER NOT NULL,
    raw_data TEXT,
    location as (json_extract(raw_data, '$.measurement.location')) STORED
);

Generované sloupce lze volitelně určit pomocí column_name GENERATED ALWAYS AS <function> [STORED|VIRTUAL] syntaxe. GENERATED ALWAYS syntaxe je volitelná a při vynechání nemění chování generovaného sloupce.

Přidání generovaného sloupce do existující tabulky

Generovaný sloupec lze také přidat do existující tabulky. Pokud sensor_readings tabulka neměla generovaný location sloupec, mohli byste ho přidat spuštěním ALTER TABLE příkaz:

ALTER TABLE sensor_readings
ADD COLUMN location as (json_extract(raw_data, '$.measurement.location'));

Tím se definuje VIRTUAL generovaný sloupec, který spouští json_extract při každém čtecím dotazu.

Definice generovaných sloupců nelze přímo upravovat. Pokud chcete změnit způsob, jakým generovaný sloupec vytváří svá data, můžete použít ALTER TABLE table_name REMOVE COLUMN a poté ADD COLUMN k opětovné definici generovaného sloupce, nebo ALTER TABLE table_name RENAME COLUMN current_name TO new_name k přejmenování stávajícího sloupce před voláním ADD COLUMN s novou definicí.

Příklady

Generované sloupce nejsou omezené jen na funkce JSON, jako je json_extract: k definování způsobu generování generovaného sloupce můžete použít téměř jakoukoli dostupnou funkci.

Můžete si například vygenerovat date sloupec na základě timestamp sloupec z předchozího sensor_reading tabulku a automaticky převádí Unix timestamp na YYYY-MM-dd formát ve vaší databázi:

ALTER TABLE your_table
-- date(timestamp, 'unixepoch') converts a Unix timestamp to a YYYY-MM-dd formatted date
ADD COLUMN formatted_date AS (date(timestamp, 'unixepoch'))

Alternativně můžete definovat expires_at sloupec, který vypočítá budoucí datum, a podle tohoto data filtrovat ve svých dotazech:

-- Filter out "expired" results based on your generated column:
-- SELECT * FROM your_table WHERE current_date() > expires_at
ALTER TABLE your_table
-- calculates a date (YYYY-MM-dd) 30 days from the timestamp.
ADD COLUMN expires_at AS (date(timestamp, '+30 days'));

Další aspekty