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 chiavi esterne. In questo articolo viene descritto come gestire questo tipo di colonna nel tuo database.
Aggiungere una colonna generata a una nuova tabella
Nel seguente snippet CREATE TABLE
, creiamo una tabella per archiviare le informazioni sugli utenti. Abbiamo
le colonne per FirstName
e LastName
e definiamo una colonna generata per
FullName
, che è la concatenazione di FirstName
e LastName
SQL standard di Google
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)
);
Il valore di FullName
viene calcolato quando viene inserita una nuova riga o quando vengono aggiornati FirstName
e/o LastName
per una riga esistente. Il valore calcolato viene archiviato insieme ad altre colonne della tabella. L'SQL tra parentesi è chiamato espressione di generazione.
expression
può essere qualsiasi espressione SQL valida assegnabile al tipo di dati di colonna con le seguenti restrizioni.L'espressione può fare riferimento solo a colonne nella stessa tabella.
L'espressione non può contenere query secondarie.
L'espressione non può contenere funzioni non deterministiche, come
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
eCURRENT_TIMESTAMP()
.Non puoi modificare l'espressione di una colonna generata.
L'attributo
STORED
che segue l'espressione determina l'archiviazione della funzione insieme ad altre colonne della tabella. Gli aggiornamenti successivi a qualsiasi colonna di riferimento fanno sì che l'espressione venga rivalutata e archiviata.Non sono consentite colonne generate senza l'attributo
STORED
.Non sono consentite scritture dirette nelle colonne generate.
Le colonne generate non possono essere utilizzate, o come parte di, una chiave primaria. Tuttavia, possono essere chiavi di indice secondarie.
L'opzione per le colonne
allow_commit_timestamp
non è consentita nelle colonne generate o nelle colonne a cui fanno riferimento.Non puoi modificare il tipo di dati di una colonna generata né le eventuali colonne a cui viene fatto riferimento nella colonna generata.
Non puoi rilasciare una colonna a cui si fa riferimento in una colonna generata.
Puoi eseguire query sulle colonne generate come su qualsiasi altra colonna, come mostrato nell'esempio seguente.
SQL standard di Google
SELECT Id, FullName
FROM Users;
PostgreSQL
SELECT id, fullname
FROM users;
Equivale alla seguente istruzione, che non utilizza la colonna generata.
SQL standard di Google
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
Oltre a semplificare la query e a risparmiare il costo della valutazione dell'espressione al momento della query, una colonna generata può essere indicizzata o utilizzata anche come chiave esterna.
Creare un indice in una colonna generata
Per semplificare le ricerche nella colonna FullName
generata, possiamo creare un indice secondario come mostrato nello snippet seguente.
SQL standard di Google
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
, aggiungiamo una colonna generata alla
tabella Users
per generare e archiviare le iniziali degli utenti.
SQL standard di Google
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;
L'aggiunta di una colonna generata archiviata a una tabella esistente è un'operazione a lunga esecuzione per eseguire il backfill dei valori della colonna. Durante il backfill, le colonne generate archiviate non possono essere lette o eseguite query. Lo stato di backfill si riflette in INFORMATION_SCHEMA.
Creare un indice parziale utilizzando una colonna generata
E se volessimo eseguire query solo per utenti che hanno più di 18 anni? Una scansione completa della tabella non sarebbe efficiente, quindi utilizziamo 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 e restituisce
NULL
diversamente.SQL standard di Google
ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL)) STORED;
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
Crea un indice in questa nuova colonna e disattiva l'indicizzazione dei valori
NULL
con la parola chiaveNULL_FILTERED
in SQL standard di Google o il predicatoIS NOT NULL
in PostgreSQL. Questo indice parziale è più piccolo ed efficiente rispetto a un indice normale perché esclude tutti gli utenti al di sotto dei 18 anni.SQL standard di Google
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 la query seguente.SQL standard di Google
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 maggiori di 21 anni, utilizza lo stesso indice e filtro nella colonna generata come segue:
SQL standard di Google
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
Rimuovere una colonna generata
La seguente istruzione DDL elimina una colonna generata dalla tabella Users
.
ALTER TABLE Users
DROP COLUMN Initials;
Modificare un'espressione di colonna generata
Non è consentito modificare l'espressione di una colonna generata. Dovrai, invece, eliminare la colonna esistente e crearne una nuova generata con la nuova espressione.
Visualizzare le proprietà di una colonna generata
INFORMATION_SCHEMA di Cloud Spanner contiene informazioni sulle colonne generate nel database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere interrogando lo schema delle informazioni.
Quali colonne generate sono definite nel mio database?
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
Qual è lo stato corrente delle colonne generate nella tabella Users
?
Se hai aggiunto una colonna generata a una tabella esistente, potresti volerne visualizzare lo stato attuale per determinare, ad esempio, se la colonna è stata sottoposta a backfill e completamente utilizzabile. Se SPANNER_STATE
restituisce WRITE_ONLY
nella query seguente, significa che Cloud Spanner è ancora in fase di backfill della colonna e non sono consentite letture.
SQL standard di Google
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;
Passaggi successivi
Scopri di più sulla SCHERMATA DI INFORMAZIONI di Cloud Spanner.
Scopri ulteriori dettagli sulle colonne generate nei dettagli del parametro CREATE TABLE.