Syntax
------ 
 [GENERATED ALWAYS] AS (  )
[VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY]
[COMMENT ]
 
MariaDB's generated columns syntax is designed to be
similar to the syntax for Microsoft SQL Server's computed
columns and Oracle Database's virtual columns. In MariaDB
10.2 and later, the syntax is also compatible with the
syntax for MySQL's generated columns.
 
Description
----------- 
A generated column is a column in a table that cannot
explicitly be set to a specific value in a DML query.
Instead, its value is automatically generated based on an
expression. This expression might generate the value based
on the values of other columns in the table, or it might
generate the value by calling built-in functions or
user-defined functions (UDFs).
 
There are two types of generated columns:
PERSISTENT or STORED: This type's value is actually stored
in the table.
VIRTUAL: This type's value is not stored at all. Instead,
the value is generated dynamically when the table is
queried. This type is the default.
 
Generated columns are also sometimes called computed columns
or virtual columns.
 
Supported Features
 
Storage Engine Support
 
Generated columns can only be used with storage engines
which support them. If you try to use a storage engine that
does not support them, then you will see an error similar to
the following:
 
ERROR 1910 (HY000): TokuDB storage engine does not support
computed columns
InnoDB, Aria, MyISAM and CONNECT support generated columns.
 
A column in a MERGE table can be built on a PERSISTENT
generated column.
However, a column in a MERGE table can not be defined as a
VIRTUAL and PERSISTENT generated column.
 

Data Type Support
 
All data types are supported when defining generated
columns.
 
Using the ZEROFILL column option is supported when defining
generated columns.
 
In MariaDB 10.2.6 and later, the following statements apply
to data types for generated columns:
Using the AUTO_INCREMENT column option is not supported when
defining generated columns. Previously, it was supported,
but this support was removed, because it would not work
correctly. See MDEV-11117.
 
Index Support
 
Using a generated column as a table's primary key is not
supported. See MDEV-5590 for more information. If you try to
use one as a primary key, then you will see an error similar
to the following:
 
ERROR 1903 (HY000): Primary key cannot be defined upon a
computed column
Using PERSISTENT generated columns as part of a foreign key
is supported.
 
Referencing PERSISTENT generated columns as part of a
foreign key is also supported.
However, using the ON UPDATE CASCADE, ON UPDATE SET NULL, or
ON DELETE SET NULL clauses is not supported. If you try to
use an unsupported clause, then you will see an error
similar to the following:
 
ERROR 1905 (HY000): Cannot define foreign key with ON UPDATE
SET NULL clause on a computed column
 
In MariaDB 10.2.3 and later, the following statements apply
to indexes for generated columns:
Defining indexes on both VIRTUAL and PERSISTENT generated
columns is supported.
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.
 

MariaDB until 10.2.2
 
In MariaDB 10.2.2 and before, the following statements apply
to indexes for generated columns:
Defining indexes on VIRTUAL generated columns is not
supported.
 
Defining indexes on PERSISTENT generated columns is
supported.
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.
 

Statement Support
 
Generated columns are used in DML queries just as if they
were "real" columns.
However, VIRTUAL and PERSISTENT generated columns differ in
how their data is stored.
Values for PERSISTENT generated columns are generated
whenever a DML queries inserts or updates the row with the
special DEFAULT value. This generates the columns value, and
it is stored in the table like the other "real" columns.
This value can be read by other DML queries just like the
other "real" columns.
Values for VIRTUAL generated columns are not stored in the
table. Instead, the value is generated dynamically whenever
the column is queried. If other columns in a row are
queried, but the VIRTUAL generated column is not one of the
queried columns, then the column's value is not generated.
 
The SELECT statement supports generated columns.
 
Generated columns can be referenced in the INSERT, UPDATE,
and DELETE statements.
However, VIRTUAL or PERSISTENT generated columns cannot be
explicitly set to any other values than NULL or DEFAULT. If
a generated column is explicitly set to any other value,
then the outcome depends on whether strict mode is enabled
in SQL_MODE. If it is not enabled, then a warning will be
raised and the default generated value will be used instead.
If it is enabled, then an error will be raised instead.
 
The CREATE TABLE statement has limited support for generated
columns.
It supports defining generated columns in a new table.
It supports using generated columns to partition tables.
It does not support using the versioning clauses with
generated columns.
 
The ALTER TABLE statement has limited support for generated
columns.
It supports the MODIFY and CHANGE clauses for PERSISTENT
generated columns.
It does not support the MODIFY clause for VIRTUAL generated
columns if ALGORITHM is not set to COPY. See MDEV-15476 for
more information.
It does not support the CHANGE clause for VIRTUAL generated
columns if ALGORITHM is not set to COPY. See MDEV-17035 for
more information.
It does not support altering a table if ALGORITHM is not set
to COPY if the table has a VIRTUAL generated column that is
indexed. See MDEV-14046 for more information.
It does not support adding a VIRTUAL generated column with
the ADD clause if the same statement is also adding other
columns if ALGORITHM is not set to COPY. See MDEV-17468 for
more information.
It also does not support altering an existing column into a
VIRTUAL generated column.
It supports using generated columns to partition tables.
It does not support using the versioning clauses with
generated columns.
 
The SHOW CREATE TABLE statement supports generated columns.
 
The DESCRIBE statement can be used to check whether a table
has generated columns.
You can tell which columns are generated by looking for the
ones where the Extra column is set to either VIRTUAL or
PERSISTENT. For example:
 
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 |
+-------+-------------+------+-----+---------+------------+
Generated columns can be properly referenced in the NEW and
OLD rows in triggers.
 
Stored procedures support generated columns.
 
The HANDLER statement supports generated columns.
 
Expression Support
 
Most legal, deterministic expressions which can be
calculated are supported in expressions for generated
columns.
 
Most built-in functions are supported in expressions for
generated columns.
However, some built-in functions can't be supported for
technical reasons. For example, If you try to use an
unsupported function in an expression, then you will see an
error similar to the following:
 
ERROR 1901 (HY000): Function or expression 'dayname()'
cannot be used in the GENERATED ALWAYS AS clause of `v`
Subqueries are not supported in expressions for generated
columns because the underlying data can change.
 
Using anything that depends on data outside the row is not
supported in expressions for generated columns.
 
Stored functions are not supported in expressions for
generated columns. See MDEV-17587 for more information.
 
In MariaDB 10.2.1 and later, the following statements apply
to expressions for generated columns:
Non-deterministic built-in functions are supported in
expressions for PERSISTENT generated columns.
If a non-deterministic function is used for a PERSISTENT
generated column, then any changes made to this table should
be logged to the binary log in the row-based logging format
when the binlog_format system variable is set to MIXED.
However, this does not always happen. Therefore, it is
recommended to set the binlog_format system variable to ROW.
See MDEV-10436 for more information.
 
Non-deterministic built-in functions are not supported in
expressions for VIRTUAL generated columns.
 
User-defined functions (UDFs) are supported in expressions
for generated columns.
However, MariaDB can't check whether a UDF is
deterministic, so it is up to the user to be sure that they
do not use non-deterministic UDFs with VIRTUAL generated
columns.
 
Defining a generated column based on other generated columns
defined before it in the table definition is supported. For
example:
 
CREATE TABLE t1 (a int as (1), b int as (a));
However, defining a generated column based on other
generated columns defined after in the table definition is
not supported in expressions for generation columns because
generated columns are calculated in the order they are
defined.
 
Using an expression that exceeds 255 characters in length is
supported in expressions for generated columns. The new
limit for the entire table definition, including all
expressions for generated columns, is 65,535 bytes.
 
Using constant expressions is supported in expressions for
generated columns. For example:
 
CREATE TABLE t1 (a int as (1));
 
MariaDB until 10.2.0
 
In MariaDB 10.2.0 and before, the following statements apply
to expressions for generated columns:
Non-deterministic built-in functions are not supported in
expressions for generated columns.
 
User-defined functions (UDFs) are not supported in
expressions for generated columns.
 
Defining a generated column based on other generated columns
defined in the table is not supported. Otherwise, it would
generate errors like this:
 
ERROR 1900 (HY000): A computed column cannot be based on a
computed column
Using an expression that exceeds 255 characters in length is
not supported in expressions for generated columns.
 
Using constant expressions is not supported in expressions
for generated columns. Otherwise, it would generate errors
like this:
 
ERROR 1908 (HY000): Constant expression in computed column
function is not allowed
 
MySQL Compatibility Support
 
In MariaDB 10.2.1 and later, the following statements apply
to MySQL compatibility for generated columns:
The STORED keyword is supported as an alias for the
PERSISTENT keyword.
 
Tables created with MySQL 5.7 or later that contain MySQL's
generated columns can be imported into MariaDB without a
dump and restore.
 
MariaDB until 10.2.0
 
In MariaDB 10.2.0 and before, the following statements apply
to MySQL compatibility for generated columns:
The STORED keyword is not supported as an alias for the
PERSISTENT keyword.
 
Tables created with MySQL 5.7 or later that contain MySQL's
generated columns can not be imported into MariaDB without a
dump and restore.
 
Implementation Differences
 
Generated columns are subject to various constraints in
other DBMSs that are not present in MariaDB's
implementation. Generated columns may also be called
computed columns or virtual columns in different
implementations. The various details for a specific
implementation can be found in the documentation for each
specific DBMS.
 
Implementation Differences Compared to Microsoft SQL Server
 
MariaDB's generated columns implementation does not enforce
the following
restrictions that are present in Microsoft SQL Server's
computed columns implementation:
MariaDB allows server variables in generated column
expressions, including those that change dynamically, such
as warning_count.
MariaDB allows the CONVERT_TZ() function to be called with a
named time zone as an argument, even though time zone names
and time offsets are configurable.
MariaDB allows the CAST() function to be used with
non-unicode character sets, even though character sets are
configurable and differ between binaries/versions.
MariaDB allows FLOAT expressions to be used in generated
columns. Microsoft SQL Server considers these expressions to
be "imprecise" due to potential cross-platform differences
in floating-point implementations and precision.
Microsoft SQL Server requires the ARITHABORT mode to be set,
so that division by zero returns an error, and not a NULL.
Microsoft SQL Server requires QUOTED_IDENTIFIER to be set in
SQL_MODE. In MariaDB, if data is inserted without
ANSI_QUOTES set in SQL_MODE, then it will be processed and
stored differently in a generated column that contains
quoted identifiers.
In MariaDB 10.2.0 and before, it does not allow user-defined
functions (UDFs) to be used in expressions for generated
columns.
 
Microsoft SQL Server enforces the above restrictions by
doing one of the following things:
Refusing to create computed columns.
Refusing to allow updates to a table containing them.
Refusing to use an index over such a column if it can not be
guaranteed that the 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 generated column expression will always be
evaluated the same. If any of these things change, then
please be aware that the generated column expression might
not be
evaluated the same way as it previously was.
 
In MariaDB 5.2, you will get a warning if you try to update
a virtual column. In MariaDB 5.3 and later, this warning
will be converted to an error if strict mode is enabled in
SQL_MODE.
 
Development History
 
Generated 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.
 
Examples
-------- 
Here is an example table that uses both VIRTUAL and
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)
 
If the ZEROFILL clause is specified, it should be placed
directly after the type definition, before the AS ():
 
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.
 


URL: https://mariadb.com/kb/en/generated-columns/