Virtual Column

Le virtual column sono una delle caratteristiche più interessanti introdotte con MySQL 5.7, con il nome di generated column. La creazione di colonne virtuali era stata introdotta nel noto fork di MySQL, MariaDB, già dalla versione 5.2, senza però essere mai state inserite nel suo predecessore. L'introduzione di una funzionalità così importante non poteva però essere tralasciata dagli sviluppatori di MySQL, che la hanno introdotta dopo che la community la ha massivamente richiesta.

Cosa sono le Virtual Column

Nei database relazionali, le virtual column sono colonne di una determinata tabella capaci di calcolare 'on demand' dei valori, sulla base di altre colonne della tabella stessa. Le virtual column non fanno parte dello standard SQL e ciò comporta la loro presenza solo in alcuni DBMS, quali il già citato MariaDB, ma anche SQL Server e Oracle Database. Volendo dare una definizione più tecnica, una virtual column potrebbe essere considerata come un metodo alternativo per creare un functional based index, ovvero un indice che viene creato sulla base del risultato di una funzione o di una espressione. Per molto tempo questa è stata una particolarità limitata a pochi DBMS, ma grazie alla nascita delle virtual column è stato possibile estenderla anche ad altri tipi di DBMS. Chi ha dimistichezza con i database Oracle ne avrà già intuito le potenzialità, che comunque describeremo meglio nel seguito.

Esistono due tipologie diverse di colonne virtuali:

• Virtual Column
• Persistent Column

La differenza sostanziale tra le due tipologie di colonne virtuali è che le omonime virtual column sono calcolate on the fly, ovvero quando richieste, mentre le seconde sono salvate in memoria (come suggerisce lo stesso nome).

Entrambe hanno vantaggi e svantaggi. Le virtual column non richiedono spazio sul disco per essere memorizzate, ma ovviamente richiedono tempo di calcolo ogni qual volta se ne richieda l'uso. Le persistent column, al contrario, non richiedono tempo di calcolo, bensì spazio sul disco. La scelta tra le due tipologie di virtual column non è sempre possibile, perchè un DBMS potrebbe implementarne solo una tipologia (o addirittura nessuna).

Fortunatamente, MySQL permette l'implementazione di entrambe le tipologie, consentendo allo sviluppatore di scegliere la soluzione migliore per la propria architettura.

Come si usano?

La creazione di una virtual column può essere effettuata sia in fase di creazione di una tabella, sia quando una nuova colonna viene aggiunta ad una tabella già esistente. Per fissare meglio questo concetto, serviamoci di un esempio.

Supponiamo di voler creare una tabella che racchiuda nome, cognome ed email di una serie di persone a noi note: per esempio, i dati di una rubrica personale. Normalmente, eseguiremmo il seguente codice SQL per generare la suddetta tabella:

CREATE TABLE IF NOT EXISTS rubrica(
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    cognome VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Supponiamo adesso di eseguire una query di selezione (SELECT) che ci restituisca nome e cognome (concatenati) ed email di un particolare contatto:

SELECT
    CONCAT(nome, ' ', cognome), email
FROM
    rubrica;
WHERE id = '1'

Decidere di utilizzare una query SQL di questo tipo non è sempre conveniente. Sebbene in questo i dati che vogliamo mostrare sono pochi, in scenari più complessi (e realistici) si potrebbe dar vita a query tremendamente verbose, e conseguentemente poco leggibili e manutenibili.

A questo proposito ci vengono in aiuto le virtual column di MySQL. Possiamo, infatti, creare la precedente tabella rubrica in questo modo:

CREATE TABLE rubrica(
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    cognome VARCHAR(50) NOT NULL,
    nome_completo varchar(101) GENERATED ALWAYS AS (CONCAT(nome,' ',cognome)),
    email VARCHAR(100) NOT NULL
);

Come vediamo abbiamo aggiunto in fase di creazione della tabella una nuova colonna, chiamata nome_completo, che si occuperà di concatenare automaticamente il nome ed il cognome, generando il nome completo della persona.

In generale, la sintassi per definire una virtual column in MySQL è la seguente:

NOME_COLONNA TIPO [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Di default, in MySQL le virtual column sono create come VIRTUAL, e quindi generate solo su richiesta (per esempio quando richiamate tramite SELECT). Se nella creazione della tabella rubrica avessimo invece definito la keyword STORED, avremmo invece creato una virtual column persistente.

Se adesso eseguiamo la seguente SELECT:

SELECT
    *
FROM
    rubrica;

L' output sarà:

+----+-----------+------------+-----------------+-----------------+
| id | nome      |cognome     | nome_completo   | email           |
+----+-----------+------------+-----------------+-----------------+
| 1  | vito      | Monteleone | vito monteleone | prova@gmail.com |
+----+-----------+------------+-----------------+-----------------+

Proviamo adesso a lanciare il comando desc dalla console MySQL:

mysql> desc rubrica;
 +---------------+--------------+------+------+---------+-----------------------------+
 | Field         | Type         | Null | Key  | Default | Extra                        |
 +---------------+--------------+------+------+---------+-----------------------------+
 | id            | int(11)      | NO   | PRI  | NULL    | auto_increment              |
 | nome          | varchar(50)  | NO   |      | NULL    |                             |
 | cognome       | varchar(50)  | NO   |      | NULL    |                             |
 | nome_completo | varchar(101) | YES  |      | NULL    | VIRTUAL GENERATED           |
 | email         | varchar(100) | NO   |      | NULL    |                             |
 +---------------+--------------+------+------+---------+---------+.-----------------+

Possiamo vedere che la colonna nome_completo è definita come VIRTUAL GENERATED, ovvero generata virtualmente.

L'altro modo per generare una virtual column consiste nell'aggiungerla a una tabella già esistente. Per poter far ciò, si utilizza l'usuale comando ALTER:

ALTER TABLE rubrica
ADD COLUMN nome_completo varchar(101)
GENERATED ALWAYS AS (CONCAT(nome,' ',cognome));

Ovviamente, la colonna nome_completo non deve essere stata precedentemente creata.

Conclusioni: vantaggi e svantaggi

Come si evince da quanto visto finora, le virtual column sono uno strumento molto comodo che MySQL mette a disposizione degli sviluppatori. Possiamo così riassumerne i principali vantaggi e svantaggi:

• Vantaggi:
  • Permettono di semplificare le query, aumentandone la leggibilità e manutenibilità
  • Su MySQL, è possibile dichiarare una virtual column come NOT NULL (cosa non possibile su altri DBMS, ad esempio MariaDB)
• Svantaggi:
  • I valori possono essere duplicati (nel caso di virtual column di tipo STORED), con conseguente impatto sulla memoria utilizzata
  • Possono risultare più onerose in termini di tempo di processing (nel caso di virtual column di tipo VIRTUAL)