Gestione dei valori predefiniti della chiave primaria

Questa pagina illustra le strategie da utilizzare per generare valori di chiave primaria nella tabella utilizzando espressioni di valori predefiniti. Le informazioni in questa pagina si applicano sia ai database con dialetto GoogleSQL sia ai database con dialetto PostgreSQL. Queste strategie presentano i seguenti vantaggi:

  • Prevengono gli hotspot.
  • Semplificano le migrazioni da altri database.
  • Incapsulano la logica delle chiavi nel database, in modo che non sia necessario preoccuparsi di gestirla nell'applicazione.
  • Nella maggior parte dei casi, sostituiscono la necessità di creare e gestire le proprie sequenze.

Metodi per generare automaticamente le chiavi primarie

Per generare automaticamente i valori di chiave primaria, puoi utilizzare le seguenti strategie in una colonna con espressioni DEFAULT:

  • Una funzione UUID che genera valori UUID versione 4.
  • Colonne IDENTITY che generano automaticamente valori interi per le colonne chiave e non chiave.
  • SERIAL in PostgreSQL e AUTO_INCREMENT in GoogleSQL, che sono alias DDL (Data Definition Language) per le colonne IDENTITY.
  • Un oggetto schema, SEQUENCE, con un'opzione bit_reversed_positive. SEQUENCE è disponibile sia per GoogleSQL sia per PostgreSQL.
  • Una colonna rowid generata automaticamente per le tabelle che non hanno chiavi primarie definite dall'utente.

Identificatore univoco universale (UUID)

Spanner può generare automaticamente un UUID versione 4 da utilizzare come chiave primaria. Gli UUID sono adatti per le nuove applicazioni e le tabelle con molte righe. Sono distribuiti in modo approssimativamente uniforme nello spazio delle chiavi, il che impedisce gli hotspot su larga scala. La generazione di UUID può creare un numero elevato di valori (2122) e ogni valore è effettivamente univoco. Ad esempio, avresti bisogno di 2, 71 × 1018 valori per una probabilità di collisione del 50% o di 1 miliardo al secondo per 86 anni. Questa ampia gamma garantisce valori univoci quando utilizzi gli UUID in tabelle di grandi dimensioni. Gli UUID sono univoci sia che li generi nel database sia nel client. Ti consigliamo di utilizzare gli UUID quando possibile. Puoi combinare in sicurezza gli UUID generati dal client e quelli generati da Spanner nella stessa tabella se gli UUID generati dal client vengono serializzati in minuscolo, in conformità con RFC 4122.

Per una colonna che richiede valori predefiniti, puoi utilizzare la NEW_UUID funzione GoogleSQL o la gen_random_uuid() funzione PostgreSQL per generarli. L'esempio seguente mostra come creare una tabella in cui la colonna chiave FanId ha un UUID generato nella colonna dei valori come valore predefinito.

GoogleSQL 

CREATE TABLE Fans (
  FanId UUID DEFAULT (NEW_UUID()),
  Name STRING(MAX),
) PRIMARY KEY (FanId);

PostgreSQL 

CREATE TABLE Fans (
  FanId uuid DEFAULT gen_random_uuid(),
  Name text,
  PRIMARY KEY (FanId)
);

GoogleSQL 

INSERT INTO Fans (Name) VALUES ('Melissa Garcia')
THEN RETURN FanId;

PostgreSQL 

INSERT INTO fans (name) VALUES ('Melissa Garcia')
RETURNING fanid;

Questa istruzione restituisce un risultato simile al seguente:

FanId
6af91072-f009-4c15-8c42-ebe38ae83751

Per ulteriori informazioni sulle funzioni UUID generate, consulta la GoogleSQL o PostgreSQL.

Colonne IDENTITY

Con le colonne IDENTITY, puoi generare automaticamente valori interi per le colonne chiave e non chiave. Le colonne IDENTITY non richiedono di gestire manualmente una sequenza sottostante o la relazione tra la colonna e la sequenza sottostante. Quando elimini una colonna identity generata automaticamente, Spanner elimina automaticamente anche la sequenza sottostante.

Puoi utilizzare le colonne IDENTITY fornendo un valore intero iniziale durante la generazione della sequenza o lasciando che Spanner generi la sequenza di numeri interi per te. Per fornire un valore intero iniziale, devi utilizzare l'opzione START COUNTER WITH e un valore iniziale INT64 positivo. Spanner utilizza questo valore per impostare il valore successivo per il contatore della sequenza interna generata automaticamente e inverte i bit del valore prima di inserirlo in questa colonna.

In Spanner, le colonne IDENTITY sono supportate sia in GoogleSQL sia in PostgreSQL.

GoogleSQL

L'esempio seguente mostra come utilizzare le colonne IDENTITY per creare una colonna di chiave primaria intera generata automaticamente per SingerId quando crei una nuova tabella utilizzando il comando CREATE TABLE:

CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

Puoi anche specificare l'inizio del contatore per la colonna utilizzando l'opzione START COUNTER WITH. Nell'esempio seguente, viene creata una colonna intera generata automaticamente per SingerId che ha valori positivi con bit invertiti e un contatore interno che inizia da 1000.

CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

PostgreSQL

L'esempio seguente mostra come utilizzare le colonne IDENTITY per creare una colonna intera generata automaticamente per SingerId quando crei una nuova tabella utilizzando il comando CREATE TABLE:

CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name text,
  PRIMARY KEY (SingerId)
);

Puoi anche specificare l'inizio del contatore per la colonna utilizzando l'START COUNTER WITH opzione. Nell'esempio seguente, viene creata una colonna intera generata automaticamente per SingerId che genera valori positivi con bit invertiti e il contatore interno, prima dell'inversione dei bit, inizia da 1000.

CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name text,
  PRIMARY KEY (SingerId)
);

SERIAL e AUTO_INCREMENT

Spanner supporta SERIAL in PostgreSQL e AUTO_INCREMENT in GoogleSQL, che sono alias DDL per le colonne IDENTITY e vengono utilizzati per creare colonne intere univoche. Prima di utilizzare SERIAL o AUTO_INCREMENT, devi impostare l'opzione default_sequence_kind del database. Puoi utilizzare la seguente istruzione SQL per impostare l'opzione default_sequence_kind del database:

GoogleSQL 

ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 AUTO_INCREMENT PRIMARY KEY,
  name STRING(MAX),
);

PostgreSQL 

ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id serial PRIMARY KEY,
  name text
);

Poiché SERIAL e AUTO_INCREMENT vengono mappati alle colonne IDENTITY, non li vedrai quando serializzi lo schema. Per questo schema, l'output di GetDatabaseDDL sarà:

GoogleSQL 

ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 GENERATED BY DEFAULT AS IDENTITY,
  name STRING(MAX)
) PRIMARY KEY (id);

PostgreSQL 

ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  name character varying,
  PRIMARY KEY(id)
);

Sequenza con bit invertiti

Una sequenza con bit invertiti è un oggetto schema che produce una sequenza di numeri interi e ne inverte i bit. Questo oggetto utilizza l'inversione dei bit su un contatore Spanner interno privato per garantire l'unicità. I valori con bit invertiti risultanti aiutano a evitare gli hotspot su larga scala quando vengono utilizzati in una chiave primaria.

In Spanner, utilizzi le istruzioni DDL SEQUENCE insieme all' bit_reversed_positive per creare, modificare o eliminare una sequenza che produce valori positivi con bit invertiti (GoogleSQL o PostgreSQL).

Ogni sequenza mantiene un insieme di contatori interni e li utilizza per generare un valore. Il contatore della sequenza fornisce l'input all'algoritmo di inversione dei bit.

Quando definisci una colonna con un'espressione DEFAULT che utilizza la funzione GoogleSQL GET_NEXT_SEQUENCE_VALUE o la funzione PostgreSQL nextval come valore predefinito, Spanner chiama automaticamente la funzione e inserisce i valori di output con bit invertiti nella colonna. Le sequenze con bit invertiti sono particolarmente utili per le chiavi primarie, perché i valori con bit invertiti sono distribuiti uniformemente nello spazio delle chiavi in modo da non causare hotspot.

L'esempio seguente mostra come creare una sequenza con bit invertiti e una tabella in cui la colonna chiave utilizza la sequenza come valore predefinito:

GoogleSQL 

CREATE SEQUENCE SingerIdSequence OPTIONS (
  sequence_kind="bit_reversed_positive"
);

CREATE TABLE Singers (
  SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE SingerIdSequence)),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE SEQUENCE SingerIdSequence bit_reversed_positive;

CREATE TABLE Singers (
  SingerId bigint DEFAULT nextval('SingerIdSequence'),
  Name text,
  PRIMARY KEY (SingerId)
);

Puoi quindi utilizzare la seguente istruzione SQL per inserire e restituire il valore della chiave primaria:

GoogleSQL 

INSERT INTO Singers (Name) VALUES ('Example Singer')
THEN RETURN SingerId;

PostgreSQL 

INSERT INTO Singers (name) VALUES ('Example Singer')
RETURNING SingerId;

Questa istruzione restituisce un risultato simile al seguente:

SingerId
3458764513820540928

Creare una tabella senza definire una chiave primaria

Se non fornisci chiavi primarie quando crei una tabella, Spanner crea una colonna nascosta denominata rowid. Questa colonna funge da chiave primaria. Per impostazione predefinita, la colonna rowid è una IDENTITY colonna che utilizza valori INT64 ed è supportata da una sequenza con bit invertiti per generare automaticamente le chiavi.

Puoi creare una tabella senza definire le chiavi primarie nel seguente modo:

GoogleSQL 

CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64
);

PostgreSQL 

CREATE TABLE Singers (
  Name text,
  Rank bigint
);

Lo schema risultante è il seguente:

GoogleSQL 

CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64,
  rowid INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) HIDDEN
) PRIMARY KEY (rowid);

PostgreSQL 

CREATE TABLE Singers (
  Name text,
  Rank bigint,
  rowid bigint GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) NOT NULL HIDDEN,
  PRIMARY KEY (rowid)
);

Non puoi aggiungere una chiave primaria a una tabella che hai creato in precedenza senza definire una chiave primaria.

Poiché Spanner non consente due colonne con lo stesso nome in una tabella, non puoi definire una nuova colonna non chiave denominata rowid in una tabella che hai creato in precedenza senza definire le chiavi primarie. La seguente query restituisce un errore:

GoogleSQL 

CREATE TABLE Singers (
  rowid INT64,
  Name STRING(MAX),
);

PostgreSQL 

CREATE TABLE Singers (
  rowid bigint,
  Name text
);

Spanner imposta la colonna rowid su HIDDEN. Ciò significa che la colonna non viene visualizzata in una query SELECT *:

GoogleSQL 

SELECT * FROM Singers;

/*-------+------+
 | Name  | Rank |
 +-------+------+
 | Alice | 1    |
 | David | 2    |
 +-------+------*/

PostgreSQL 

SELECT * FROM Singers;

/*-------+------+
 | Name  | Rank |
 +-------+------+
 | Alice | 1    |
 | David | 2    |
 +-------+------*/

Tuttavia, puoi eseguire query esplicite sulla colonna rowid nascosta:

GoogleSQL 

SELECT rowid FROM Singers;

/*---------------------+
 | rowid               |
 +---------------------+
 | 3458764513820540928 |
 +---------------------*/

PostgreSQL 

SELECT rowid FROM Singers;

/*---------------------+
 | rowid               |
 +---------------------+
 | 3458764513820540928 |
 +---------------------*/

Sebbene la colonna rowid non venga visualizzata in una query SELECT *, è visibile nelle tabelle INFORMATION_SCHEMA.rowid Ad esempio, puoi eseguire la seguente query:

GoogleSQL 

SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'Singers';

/*-------------+
 | column_name |
 +-------------+
 | rowid       |
 +-------------*/

PostgreSQL 

SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'singers';

 /*-------------+
 | column_name |
 +-------------+
 | "rowid"     |
 +-------------*/

Scenari per l'utilizzo di UUID e sequenze come valori predefiniti per le chiavi primarie

Gli scenari per gli UUID e le sequenze includono quanto segue:

  • Nuove applicazioni
  • Migrazioni

Le sezioni seguenti descrivono ogni scenario.

Nuove applicazioni

Per le nuove applicazioni, Spanner consiglia di utilizzare l'identificatore univoco universale (UUID) per le chiavi primarie. Per ulteriori informazioni, vedi Utilizzare un identificatore univoco universale (UUID).

Se la tua applicazione esistente richiede INT64 chiavi in GoogleSQL o bigint chiavi in PostgreSQL, Spanner offre l' oggetto schema della sequenza positiva con bit invertiti (PostgreSQL o GoogleSQL).

Migrazioni

Per eseguire la migrazione delle tabelle a Spanner, hai le seguenti opzioni:

Passaggi successivi