Una colonna generata è una colonna che viene sempre calcolata da altre colonne in una riga. Queste colonne possono semplificare una query, risparmiare sui costi di valutazione di un'espressione al momento della query e possono essere indicizzate o utilizzate come chiave esterna. In questo articolo descriveremo come gestire questo tipo di colonna nel tuo database.
Aggiungere una colonna generata a una nuova tabella
Nel seguente snippet CREATE TABLE
viene creata una tabella per archiviare le informazioni
sugli utenti. Abbiamo colonne per FirstName
e LastName
e definiamo una
colonna generata per FullName
, che è la concatenazione di FirstName
e
LastName
. L'SQL tra parentesi è chiamato espressione di generazione.
Una colonna generata può essere contrassegnata come STORED
per risparmiare il costo della valutazione dell'espressione al momento della query. Di conseguenza, il valore FullName
viene calcolato solo quando viene inserita una nuova riga o quando FirstName
o LastName
viene aggiornato per una riga esistente. Il valore calcolato viene archiviato insieme alle altre colonne della tabella.
GoogleSQL
CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);
PostgreSQL
CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);
Puoi creare una colonna generata non archiviata omettendo l'attributo STORED
nel DDL. Questo tipo di colonna generata viene valutata
in fase di query e semplifica la query. In PostgreSQL, puoi creare una colonna generata non archiviata utilizzando l'attributo VIRTUAL
.
GoogleSQL
FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))
PostgreSQL
fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL
expression
può essere qualsiasi espressione SQL valida assegnabile al tipo di dati della colonna con le seguenti restrizioni.L'espressione può fare riferimento solo alle colonne della stessa tabella.
L'espressione non può contenere sottoquery.
Le espressioni con funzioni non deterministiche come
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
eCURRENT_TIMESTAMP()
non possono essere trasformate in una colonna generataSTORED
o in una colonna generata indicizzata.Impossibile modificare l'espressione di un valore
STORED
o la colonna generata indicizzata.
L'attributo
STORED
che segue l'espressione archivia il risultato dell'espressione insieme ad altre colonne della tabella. I successivi aggiornamenti a una qualsiasi delle colonne a cui viene fatto riferimento fanno sì che Spanner rivaluta e archivia l'espressione.Le colonne generate che non sono
STORED
non possono essere contrassegnate comeNOT NULL
.Non sono consentite scritture dirette nelle colonne generate.
L'opzione colonna
allow_commit_timestamp
non è consentita nelle colonne generate o nelle colonne che hanno generato riferimenti alle colonne.Per
STORED
o le colonne generate indicizzate, non puoi modificare il tipo di dati della colonna o di qualsiasi colonna a cui fa riferimento la colonna generata.Non puoi rilasciare una colonna a cui fa riferimento una colonna generata.
Puoi utilizzare una colonna generata come chiave primaria con le seguenti limitazioni aggiuntive:
La chiave primaria generata non può fare riferimento ad altre colonne generate.
La chiave primaria generata può fare riferimento al massimo a una colonna non chiave.
La chiave primaria generata non può dipendere da una colonna non chiave con una clausola
DEFAULT
.
Quando utilizzi le colonne chiave generate, si applicano le seguenti regole:
- API di lettura: devi specificare completamente le colonne chiave, incluse le colonne chiave generate.
- API Mutation: per
INSERT
,INSERT_OR_UPDATE
eREPLACE
, Spanner non consente di specificare colonne chiave generate. PerUPDATE
, puoi specificare facoltativamente le colonne chiave generate. PerDELETE
, devi specificare completamente le colonne delle chiavi, incluse le chiavi generate. - DML: non puoi scrivere esplicitamente nelle chiavi generate nelle istruzioni
INSERT
oUPDATE
. - Query: in generale, ti consigliamo di utilizzare la colonna chiave generata come
filtro nella query. Facoltativamente, se l'espressione per la colonna chiave generata utilizza una sola colonna come riferimento, la query può applicare una condizione di uguaglianza (
=
) oIN
alla colonna di riferimento. Per ulteriori informazioni e un esempio, consulta Creare una chiave univoca derivata da una colonna di valore.
È possibile eseguire query sulla colonna generata come qualsiasi altra colonna, come mostrato nell'esempio seguente.
GoogleSQL
SELECT Id, FullName
FROM Users;
PostgreSQL
SELECT id, fullname
FROM users;
La query che utilizza Fullname
equivale a quella con l'espressione generata. Di conseguenza, una colonna generata può semplificare la query.
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
Crea un indice su una colonna generata
Puoi anche indicizzare o utilizzare una colonna generata come chiave esterna.
Per facilitare le ricerche nella colonna FullName
generata, possiamo creare un
indice secondario come mostrato nello snippet seguente.
GoogleSQL
CREATE INDEX UsersByFullName ON Users (FullName);
PostgreSQL
CREATE INDEX UserByFullName ON users (fullname);
Aggiungere una colonna generata a una tabella esistente
Utilizzando la seguente istruzione ALTER TABLE
, possiamo aggiungere una colonna generata alla
tabella Users
per generare e archiviare le iniziali dell'utente.
GoogleSQL
ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;
PostgreSQL
ALTER TABLE users ADD COLUMN initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(firstname, 0, 1) || SUBSTR(lastname, 0, 1)) STORED;
Se aggiungi una colonna generata archiviata a una tabella esistente, viene avviata un'operazione a lunga esecuzione per eseguire il backfill dei valori della colonna. Durante il backfill, non è possibile leggere o eseguire query sulle colonne generate dall'archivio. Lo stato di backfill si riflette nella tabella INFORMATION_SCHEMA.
Crea un indice parziale utilizzando una colonna generata
E se volessimo eseguire query solo su utenti maggiori di 18 anni? L'analisi completa della tabella non sarebbe efficace, quindi usiamo un indice parziale.
Utilizza la seguente istruzione per aggiungere un'altra colonna generata che restituisce l'età dell'utente se ha più di 18 anni, altrimenti
NULL
.GoogleSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL));
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;
Crea un indice in questa nuova colonna e disabilita l'indicizzazione dei valori
NULL
con la parola chiaveNULL_FILTERED
in GoogleSQL o il predicatoIS NOT NULL
in PostgreSQL. Questo indice parziale è più piccolo e più efficiente di un normale indice perché esclude tutti gli utenti che hanno meno di 18 anni.GoogleSQL
CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);
PostgreSQL
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18) WHERE AgeAbove18 IS NOT NULL;
Per recuperare
Id
eAge
di tutti gli utenti maggiori di 18 anni, esegui questa query.GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 IS NOT NULL;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 IS NOT NULL;
Per filtrare in base a un'età diversa, ad esempio per recuperare tutti gli utenti di età superiore ai 21 anni, utilizza lo stesso indice e applica lo stesso filtro nella colonna generata come segue:
GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
Una colonna generata indicizzata può far risparmiare il costo della valutazione di un'espressione al momento della query ed evitare di archiviare i valori due volte (nella tabella di base e nell'indice) rispetto a una colonna generata da
STORED
.
Rimuovere una colonna generata
La seguente istruzione DDL elimina una colonna generata dalla tabella Users
:
GoogleSQL
ALTER TABLE Users DROP COLUMN Initials;
PostgreSQL
ALTER TABLE users DROP COLUMN initials;
Modificare un'espressione di colonna generata
GoogleSQL
ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));
PostgreSQL
ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;
Non è consentito aggiornare l'espressione di una colonna generata con STORED
o di una colonna indicizzata non archiviata.
Crea una chiave primaria in una colonna generata
In Spanner, puoi utilizzare una colonna STORED
generata nella chiave primaria.
L'esempio seguente mostra un'istruzione DDL che crea la tabella UserInfoLog
con una colonna generata ShardId
. Il valore della colonna ShardId
dipende da un'altra colonna. Viene derivato utilizzando una funzione MOD
nella
colonna UserId
. ShardId
viene dichiarato come parte della chiave primaria.
GoogleSQL
CREATE TABLE UserInfoLog (
ShardId INT64 NOT NULL
AS (MOD(UserId, 2048)) STORED,
UserId INT64 NOT NULL,
FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);
PostgreSQL
CREATE TABLE UserInfoLog (
ShardId BIGINT GENERATED ALWAYS
AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
UserId BIGINT NOT NULL,
FullName VARCHAR(1024) NOT NULL,
PRIMARY KEY(ShardId, UserId));
Normalmente, per accedere in modo efficiente a una riga specifica, devi specificare tutte le colonne
chiave. Nell'esempio precedente, questo significherebbe fornire sia ShardId
sia
UserId
. Tuttavia, Spanner a volte può dedurre il valore della colonna di chiave primaria generata se dipende da una singola altra colonna e se il valore della colonna da cui dipende è completamente determinato. Questo è vero se la colonna a cui fa riferimento la colonna di chiave primaria generata soddisfa una delle seguenti condizioni:
- È uguale a un valore costante o a un parametro associato nella clausola
WHERE
, oppure - Ottiene il suo valore impostato da un operatore
IN
nella clausolaWHERE
- Ottiene il valore da una condizione equi-join
Ad esempio, per la seguente query:
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
Spanner può dedurre il valore di ShardId
dal valore UserId
fornito.
Dopo l'ottimizzazione, la query precedente equivale alla seguente query:
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
L'esempio successivo mostra come creare la tabella Students
e utilizzare un'espressione che recupera il campo id
della colonna JSON StudentInfo
e lo utilizza come chiave primaria:
GoogleSQL
CREATE TABLE Students (
StudentId INT64 NOT NULL
AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);
PostgreSQL
CREATE TABLE Students (
StudentId BIGINT GENERATED ALWAYS
AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
StudentInfo JSONB NOT NULL,
PRIMARY KEY(StudentId));
Visualizza le proprietà di una colonna generata
L'elemento INFORMATION_SCHEMA
di Spanner contiene informazioni
sulle colonne generate nel database. Di seguito sono riportati alcuni esempi di domande
a cui puoi rispondere quando esegui query sullo schema delle informazioni.
Quali colonne generate sono definite nel mio database?
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
IS_STORED
è YES
per le colonne generate memorizzate, NO
per le colonne generate
non archiviate o NULL
per le colonne non generate.
Qual è lo stato attuale delle colonne generate nella tabella Users
?
Se hai aggiunto una colonna generata a una tabella esistente, potresti voler passare SPANNER_STATE
in una query per scoprire lo stato attuale della colonna.
SPANNER_STATE
restituisce i seguenti valori:
COMMITTED
: la colonna è completamente utilizzabile.WRITE_ONLY
: è in corso il backfill della colonna. Nessuna lettura consentita.
Utilizza la seguente query per trovare lo stato di una colonna:
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;
Nota: una colonna generata non archiviata è accessibile solo tramite la query SQL. Tuttavia, se viene indicizzato, puoi utilizzare l'API di lettura per accedere al valore dall'indice.
Prestazioni
Una colonna STORED
generata non influisce sulle prestazioni di un'operazione di lettura o di query. Tuttavia, le colonne generate non memorizzate utilizzate in una query possono influire sulle sue prestazioni a causa dell'overhead associato alla valutazione dell'espressione di colonna generata.
Le prestazioni delle operazioni di scrittura (istruzioni e mutazioni DML) vengono influenzate
quando si utilizza una colonna generata da STORED
o una colonna generata
indicizzata. L'overhead è dovuto alla valutazione dell'espressione di colonna generata quando l'operazione di scrittura inserisce o modifica una qualsiasi delle colonne a cui viene fatto riferimento nell'espressione di colonna generata. Poiché l'overhead varia a seconda del carico di lavoro di scrittura per l'applicazione, la progettazione dello schema e le caratteristiche del set di dati, ti consigliamo di eseguire il benchmarking delle applicazioni prima di utilizzare una colonna generata.
Passaggi successivi
Scopri di più su Schema di informazioni per i database di dialetti GoogleSQL di Spanner e Schema di informazioni per i database di dialetti PostgreSQL.
Per ulteriori informazioni sulle colonne generate, consulta i dettagli del parametro CREATE TABLE.