Colonne Virtuali

Sintassi

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

Il default è PERSISTENT.

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".

Implementation Differences

Computed/Virtual columns in other DBMSs are subject to various constraints that are not present in the MariaDB implementation. These constraints are treated at length in those DBMSs documentation (for example, Microsoft SQL Server).

The virtual columns implementation in MariaDB does not enforce the following restrictions (these restrictions are present in MS SQL Server):

• MariaDB allows server variables in virtual column expressions, including @@warning_count and others that change dynamically
• MariaDB allows user variables in virtual column expressions
• MariaDB allows CONVERT_TZ() with a named time zone as an argument, even though time zone names and time offsets are configurable
• DATE_FORMAT() is allowed even though month names are language-dependent;
• MariaDB allows CAST() to non-unicode character sets, even though character sets are configurable and differ between binaries/versions
• MariaDB allows FLOAT expressions in virtual columns, which MS SQL server considers "imprecise" due to potential cross-platform differences in floating-point implementation and precision
• SQL Server requires ARITHABORT mode to be set, so that division by zero returns an error, and not a NULL
• SQL Server requires QUOTED_IDENTIFIER SQL mode to be set. In MariaDB, data inserted under different settings of ANSI_QUOTES will be processed and stored differently in a virtual column that contains quoted identifiers
• MariaDB does not allow user-defined functions, even those flagged as DETERMINISTIC

Microsoft SQL Server enforces the above restrictions by refusing to create virtual columns, refusing to allow updates to a table containing them, and, finally, refusing to use an index over such a column if it can not be guaranteed that the virtual expression is fully deterministic.

In MariaDB, as long as the SQL mode, language, and other settings that were in effect during the CREATE TABLE remain unchanged, the virtual column expression will always be evaluated the same. If the SQL mode, language, etc... are changed later, please be aware that the virtual column expression might not be evaluated the same.

In MariaDB 5.2 you only get a warning if you try to update a virtual column. Starting from 5.3 you will get an error if you try to update it under strict mode (sql_mode = 'strict_all_tables').

Other Information

Virtual columns first appeared in MariaDB 5.2.

Virtual columns was originally developed by Andrey Zhakov. It was then modified by Sanja Byelkin and Igor Babaev at Monty Program for inclusion in MariaDB.

Information about Andrey's original virtual columns patch can be found on MySQL Forge.

Examples

Here is an example table that uses both VIRTUAL and PERSISTENT virtual columns:

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)

If you describe the table, you can easily see which columns are virtual by looking in the "Extra" column:

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)

Both PERSISTENT and VIRTUAL virtual columns show up as "VIRTUAL" in the "Extra" column. To find out which of them (if any) are PERSISTENT, you can use "<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 |

If you try to insert non-default values into a virtual column, you will receive a warning and what you tried to insert will be ignored and the derived value inserted instead:

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]> 

You can also user virtual columns to implement "poor mans partial index". See example at end of UNIQUE constraint.