← Cloudflare D1 / d1 / reference

Генерируемые столбцы

D1 позволяет определять генерируемые столбцы на основе значений одного или нескольких других столбцов, SQL-функций или даже извлечённые значения JSON.

Это позволяет нормализовать данные при записи в таблицу или чтении из неё, упрощая запросы и снижая потребность в сложной логике на стороне приложения.

У генерируемых столбцов также может быть определённые индексы по ним, что может значительно повысить производительность запросов к часто запрашиваемым полям.

Существует два типа генерируемых столбцов:

• VIRTUAL (по умолчанию): столбец вычисляется при чтении. Это не расходует место для хранения, но может увеличить время вычислений и тем самым снизить производительность запросов, особенно крупных.
• STORED: столбец генерируется при записи строки. Он занимает место в хранилище так же, как обычный столбец, но не требует повторной генерации при каждом чтении, что может повысить производительность запросов на чтение.

Если это опущено в выражении генерируемого столбца, по умолчанию используется VIRTUAL тип. STORED тип рекомендуется использовать, когда генерируемый столбец требует значительных вычислений. Например, при разборе больших структур JSON.

Генерируемые столбцы можно определить при создании таблицы в CREATE TABLE оператор, либо позже через ALTER TABLE оператор.

Чтобы создать таблицу с генерируемым столбцом, используйте AS ключевое слово:

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

В качестве конкретного примера, чтобы автоматически извлечь location значение из следующих данных датчика в формате JSON, вы можете определить генерируемый столбец с именем location (типа TEXT), исходя из raw_data столбец, который хранит исходное представление данных JSON.

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

Чтобы определить генерируемый столбец со значением $.measurement.location, вы можете использовать json_extract функцию, чтобы извлечь значение из raw_data столбец при каждой записи в эту строку:

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
);

При необходимости генерируемые столбцы можно указать с помощью column_name GENERATED ALWAYS AS <function> [STORED|VIRTUAL] синтаксис. GENERATED ALWAYS синтаксис необязателен, и его отсутствие не влияет на поведение генерируемого столбца.

Генерируемый столбец также можно добавить в существующую таблицу. Если sensor_readings таблица не имела генерируемого location столбец, его можно добавить, выполнив ALTER TABLE оператор:

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

Это определяет VIRTUAL генерируемый столбец, который выполняет json_extract при каждом запросе на чтение.

Определения генерируемых столбцов нельзя изменить напрямую. Чтобы изменить, как генерируемый столбец формирует данные, можно использовать ALTER TABLE table_name REMOVE COLUMN и затем ADD COLUMN чтобы переопределить генерируемый столбец, либо ALTER TABLE table_name RENAME COLUMN current_name TO new_name чтобы переименовать существующий столбец перед вызовом ADD COLUMN с новым определением.

Генерируемые столбцы не ограничиваются только функциями JSON, такими как json_extract: для определения способа генерации генерируемого столбца можно использовать практически любую доступную функцию.

Например, можно сгенерировать date столбец на основе timestamp столбец из предыдущего sensor_reading таблицы, автоматически преобразуя метку времени Unix в YYYY-MM-dd формат в своей базе данных:

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'))

В качестве альтернативы можно определить expires_at столбец, который вычисляет будущую дату, и фильтровать по этой дате в запросах:

-- 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'));

• В таблице должен быть хотя бы один негенерируемый столбец. Нельзя определить таблицу, состоящую только из генерируемых столбцов.
• Выражения могут ссылаться только на другие столбцы той же таблицы и строки и должны использовать только детерминированные функции ↗. Такие функции, как random(), подзапросы или агрегатные функции нельзя использовать для определения генерируемого столбца.
• Столбцы, добавленные в существующую таблицу через ALTER TABLE ... ADD COLUMN должен быть VIRTUAL. Вы не можете добавить STORED столбец в существующую таблицу.