Colonne Virtuali

Sintassi

<type>  [GENERATED ALWAYS]  AS   ( &lt;espressione&gt; )  [VIRTUAL | PERSISTENT]  [UNIQUE] [UNIQUE KEY] [COMMENT <testo>]

Il default è VIRTUAL.

Dovrebbe essere permessa qualsiasi espressione deterministica, con le seguenti eccezioni:

• Le subquery e tutto ciò che dipende da dati che non si trovano nella riga non sono permessi (non sono realmente deterministiche perché i dati sottostanti possono cambiare).
• Le UDF e le Stored Function non sono permesse (ma si possono usare le funzioni built-in).

La sintassi per le Colonne Virtuali è simile a quella di MS SQL e Oracle.

Spiegazione

Esistono due tipi di colonne virtuali: PERSISTENT (fisiche), e VIRTUAL (generated-only).

Gli indici sono parzialmente supportati. Le Colonne Virtuali non supportano le chiavi primarie, mentre gli indici possono essere basati esclusivamente su colonne PERSISTENT. Vi è anche un supporto parziale per le chiavi esterne, ma alcune opzioni, come ON DELETE SET NULL, non sono permesse.

I Triggers, le Stored Procedure, i comandi informativi (

CREATE
TABLE

, ALTER TABLE, DESCRIBE, SHOW CREATE TABLE e così via...), le query sui dati (SELECT, HANDLER), e le partizioni basate sulle Colonne Virtuali sono pienamente supportati.

Le Colonne Virtuali sono usate nelle query SQL come se fossero campi "reali". Le colonne VIRTUAL e PERSISTENT differiscono nel modo in cui i dati vengono estratti:

• I valori delle colonne PERSISTENT sono registrati fisicamente nel database e vengono letti come i campi "reali".
• I valori delle colonne VIRTUAL non sono registrati nel database e vengono sempre generati/calcolati al volo.
• Qualsiasi colonna VIRTUAL, se non è nominata in una query, non viene calcolata.

Se su una Colonna Virtuale è definito un indice, l'ottimizzatore decide se utilizzarlo esattamente come farebbe per un campo "reale".

Differenze nell'implementazione

Le colonne Virtuali/calcolate negli altri DBMS sono soggette a vari vincoli che non sono presenti nell'implementazione di MariaDB. Queste limitazioni sono spiegate in modo articolato nella documentazione di questi DBMS (ad esempio Microsoft SQL Server).

L'implementazione delle Colonne Virtuali in MariaDB non presenta alcuna delle seguenti restrizioni (che sono invece presenti in MS SQL Server):

• MariaDB permette di utilizzare le variabili server nelle espressioni, comprese @@warning_count e altre che possono cambiare dinamicamente.
• MariaDB permette di utilizzare le variabili utente nelle espressioni.
• MariaDB accetta CONVERT_TZ() con il nome di una time zone come argomento, anche se i nomi delle time zone e i fusi orari sono configurabili.
• DATE_FORMAT() è permesso anche se i nomi dei mesi dipendono dalla lingua corrente.
• MariaDB accetta che CAST() converta le stringhe in set di caratteri non-unicode, anche se i set di caratteri sono configurabili e differiscono tra le varie versioni e tra i vari binari.
• MariaDB permette le espressioni FLOAT, che MS SQL server considera "imprecise" a causa delle potenziali differenze tra le piattaforme nell'implementazione e nella precisione.
• SQL Server richiede che sia impostata la modalità ARITHABORT, così che la divisione per zero produca un errore e non un risultato NULL.
• SQL Server richiede che la modalità QUOTED_IDENTIFIER sia impostata. In MariaDB, i dati inseriti con qualsiasi impostazione di ANSI_QUOTES sono elaborati e registrati in modo diverso in una Colonna Virtuale che ha virgolette differenti.
• MariaDB non accetta le funzioni definite dall'utente, anche se sono DETERMINISTIC

Microsoft SQL Server mette in pratica le suddette limitazioni rifiutando di creare le Colonne Virtuali, o rifiutando di creare un indice definito su quelle colonne, o rifiutando di modificare i dati contenuti in esse, se non può garantire che l'espressione è pienamente deterministica.

In MariaDB, finché l'SQL mode, la lingua, e altre impostazioni che erano in vigore all'esecuzione della CREATE TABLE rimangono invariate, l'espressione della Colonna Virtuale viene sempre valutata allo stesso modo. Se l'SQL mode, la lingua, etc, vengono modificate in un secondo momento, occorre tenere presente che l'espressione potrebbe non essere più valutata allo stesso modo.

In MariaDB 5.2 si ottiene un warning se si tenta di modificare una Colonna Virtuale. Dalla 5.3 si ottiene un errore se si tenta di modificarla in strict mode (sql_mode = 'strict_all_tables').

Ulteriori informazioni

Le Colonne Virtuali sono apparse per la prima volta in MariaDB 5.2.

Sono state sviluppate originariamente da Andrey Zhakov. Sono state poi modificate da Sanja Byelkin e Igor Babaev del Monty Program, per poterle includere in MariaDB.

Informazioni sulla patch originale di Andrey's sono reperibili su MySQL Forge.

Esempi

Ecco una tabella di esempio che utilizza sia una colonna VIRTUAL sia una PERSISTENT:

MariaDB [(none)]> use test
Database changed
MariaDB [test]> create table table1 (
    -> a int not null,
    -> b varchar(32),
    -> c int as (a mod 10) virtual,
    -> d varchar(5) as (left(b,5)) persistent);
Query OK, 0 rows affected (0.06 sec)

Se si effettua DESCRIBE sulla tabella, si può facilmente vedere quali colonne sono virtuali, osservando il campo "Extra":

MariaDB [test]> describe table1;
+-------+-------------+------+-----+---------+---------+
| Field | Type        | Null | Key | Default | Extra   |
+-------+-------------+------+-----+---------+---------+
| a     | int(11)     | NO   |     | NULL    |         |
| b     | varchar(32) | YES  |     | NULL    |         |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL |
| d     | varchar(5)  | YES  |     | NULL    | VIRTUAL |
+-------+-------------+------+-----+---------+---------+
4 rows in set (0.00 sec)

Sia le colonne PERSISTENT sia le VIRTUAL vengono mostrate come "VIRTUAL" nella colonna "Extra". Per capire quali sono PERSISTENT, si può utilizzare "<code>SHOW CREATE TABLE</code>":

MariaDB [test]> show create table table1;

| table1 | CREATE TABLE `table1` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) AS (a mod 10) VIRTUAL,
  `d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Se si tenta di inserire valori non default in una Colonna Virtuale, si riceve un warning; i dati che si tenta di inserire vengono ignorati e al loro posto vengono inseriti i valori calcolati:

MariaDB [test]> warnings;
Show warnings enabled.

MariaDB [test]> insert into table1 values (1, 'some text',default,default);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into table1 values (2, 'more text',5,default);
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1645): The value specified for computed column 'c' in table 'table1' ignored.

MariaDB [test]> insert into table1 values (123, 'even more text',default,'something');
Query OK, 1 row affected, 2 warnings (0.00 sec)

Warning (Code 1645): The value specified for computed column 'd' in table 'table1' ignored.
Warning (Code 1265): Data truncated for column 'd' at row 1

MariaDB [test]> select * from table1;
+-----+----------------+------+-------+
| a   | b              | c    | d     |
+-----+----------------+------+-------+
|   1 | some text      |    1 | some  |
|   2 | more text      |    2 | more  |
| 123 | even more text |    3 | even  |
+-----+----------------+------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> 

E' anche possibile utilizzare le Colonne Virtuali per implementare un "indice parziale dei poveri". Si veda l'esempio alla fine di UNIQUE constraint.