Generated (Virtual and Persistent) Columns
MariaDB starting with 5.2
<type> [GENERATED ALWAYS] AS ( <expression> ) [VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]
The virtual columns syntax is designed to be similar to MS SQL and Oracle.
A virtual column, also called a computed or a generated column, is a column in a table that has its value automatically calculated using a deterministic expression, in particular from the values of other fields in the table.
There are two types of virtual , or computed, columns:
PERSISTENT, same as
STORED, which are stored in the table,
VIRTUAL, which are generated when the table is queried. The default is
Most legal, deterministic expression which can be calculated are permitted, with the following exceptions:
- subqueries or anything that depends on data outside the row are not allowed (these are not deterministic because the underlying data can change)
- stored functions cannot be used (built-in functions can be used)
- One can't use other virtual columns defined after the current one in expressions. This is because virtual columns are calculated in the order they are defined.
- Virtual columns do not support primary keys
MariaDB until 10.1.6
- Constant expressions were not allowed (you would get error 1908: "Constant expression in computed column function is not allowed").
MariaDB until 10.2.1
- The expression couldn't exceed 252 characters in length.
- Other virtual columns could not be used in the expression (error 1900: "A computed column cannot be based on a computed column").
- There are a few functions that depend on the client's environment that can't yet be used in virtual expressions as we parse and pre-calculate the expression when the table is opened.
- User-defined functions couldn't be used (built-in functions can be used).
- Virtual columns could only use deterministic functions. Since MariaDB 10.2.1 this limitation applies only to PERSISTENT columns.
- The STORED keyword is not supported.
- MySQL 5.7 tables with virtual column definitions couldn't be used without a dump and restore.
MariaDB until 10.2.3
- Only PERSISTENT columns can be indexed.
MariaDB until 10.2.6
- Versioning clauses can not be applied to virtual columns.
All datatypes can be used, as well as the ZEROFILL clause.
Virtual columns can only be used with storage engines which support them. InnoDB, Aria, MyISAM and CONNECT support virtual columns. If you try to use a storage engine that does not support them, error 1910 will be issued. A column in a MERGE table can be built on a
PERSISTENT column. However, MERGE columns cannot be virtual or persistent.
Indexes are partially supported. Virtual columns do not support primary keys
and before 10.2.3 only
PERSISTENT virtual columns could be indexed.
PERSISTENT columns can be part of a foreign key and can be referenced by foreign keys, but
ON UPDATE CASCADE,
ON UPDATE SET NULL,
ON DELETE SET NULL are not allowed.
Triggers, stored procedures, informational commands (CREATE TABLE, DESCRIBE,
SHOW CREATE TABLE, and so on...), data queries
HANDLER), and partitioning based on
virtual columns are all fully supported.
CHANGE clauses are supported for
PERSISTENT columns, but not for
VIRTUAL columns, nor is it possible to alter an existing column into a
Virtual Columns are used in SQL queries just as if they were "real"
columns differ in how the data is fetched:
- Values for
PERSISTENTvirtual columns are stored in the database and read like "real" fields.
- Values for
VIRTUALvirtual columns are not stored in the database and are always generated/calculated on the fly.
VIRTUALvirtual columns which are not involved in the query are not calculated.
PERSISTENT columns cannot be changed. In
NULL or the
DEFAULT keyword can be used to explicitly indicate that the default value will be inserted. If another value is specified, an error will be issued in strict mode, otherwise a warning will be issued.
If an index is defined on a generated column, then the optimizer considers using it in the same way as indexes based on "real" columns.
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 those that change dynamically, such as warning_count.
- MariaDB allows CONVERT_TZ() with a named time zone as an argument, even though time zone names and time offsets are configurable.
- 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 before 10.2.1 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.
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. Monty did the work on MariaDB 10.2 to lift a some of the old limitations.
Restrictions Lifted in MariaDB 10.2.1
- A virtual column can now have a constant expression:
a int as (1)
- Virtual columns are computed in the order they are defined. A later virtual column can refer to an earlier one.
CREATE TABLE t1 (a int as (1), b int as (a))
- Before MariaDB 10.2.1 a virtual expression was limited to 255 characters. Now the limit for all expressions + table definition is together is 65535.
STOREDkeyword is supported
- MySQL 5.7 tables with virtual columns can be used in MariaDB.
- User-defined functions can be used. As MariaDB can't check if an UDF is deterministic, one need to be sure that one doesn't use them for not persistent fields.
- If non deterministic functions are used for a table, then MariaDB will switch to row level replication for changes to this table.
Here is an example table that uses both
PERSISTENT virtual columns:
USE 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);
If you describe the table, you can easily see which columns are virtual by looking in the "Extra" column:
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 | PERSISTENT | +-------+-------------+------+-----+---------+------------+
To find out what function(s) generate the value of the virtual column you can use
SHOW CREATE TABLE:
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:
WARNINGS; Show warnings enabled. INSERT INTO table1 VALUES (1, 'some text',default,default); Query OK, 1 row affected (0.00 sec) 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' has been ignored. 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' has been ignored. Warning (Code 1265): Data truncated for column 'd' at row 1 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)
ZEROFILL clause is specified, it should be placed directly after the type definition, before the
CREATE TABLE table2 (a INT, b INT ZEROFILL AS (a*2) VIRTUAL); INSERT INTO table2 (a) VALUES (1); SELECT * FROM table2; +------+------------+ | a | b | +------+------------+ | 1 | 0000000002 | +------+------------+ 1 row in set (0.00 sec)
You can also use virtual columns to implement a "poor man's partial index". See example at the end of Unique Index.
- Putting Virtual Columns to good use on the mariadb.com blog.