<type> [GENERATED ALWAYS] AS ( <expression> ) [VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]
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.
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
(a.k.a. 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.
ERROR 1910 (HY000): TokuDB storage engine does not support computed columns
PERSISTENT
generated column. VIRTUAL
and PERSISTENT
generated column. 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:
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. ERROR 1903 (HY000): Primary key cannot be defined upon a computed column
PERSISTENT
generated columns as part of a foreign key is supported. PERSISTENT
generated columns as part of a foreign key is also supported. 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:
VIRTUAL
and PERSISTENT
generated columns is supported. In MariaDB 10.2.2 and before, the following statements apply to indexes for generated columns:
VIRTUAL
generated columns is not supported. PERSISTENT
generated columns is supported. VIRTUAL
and PERSISTENT
generated columns differ in how their data is stored. 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. 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. SELECT
statement supports generated columns. INSERT
, UPDATE
, and DELETE
statements. 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. CREATE TABLE
statement has limited support for generated columns. ALTER TABLE
statement has limited support for generated columns. MODIFY
and CHANGE
clauses for PERSISTENT
generated columns. MODIFY
clause for VIRTUAL
generated columns if ALGORITHM
is not set to COPY
. See MDEV-15476 for more information. CHANGE
clause for VIRTUAL
generated columns if ALGORITHM
is not set to COPY
. See MDEV-17035 for more information. ALGORITHM
is not set to COPY
if the table has a VIRTUAL
generated column that is indexed. See MDEV-14046 for more information. 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. VIRTUAL
generated column. SHOW CREATE TABLE
statement supports generated columns. DESCRIBE
statement can be used to check whether a table has generated columns. 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 | +-------+-------------+------+-----+---------+------------+
NEW
and OLD
rows in triggers. HANDLER
statement supports generated columns. ERROR 1901 (HY000): Function or expression 'dayname()' cannot be used in the GENERATED ALWAYS AS clause of `v`
In MariaDB 10.2.1 and later, the following statements apply to expressions for generated columns:
PERSISTENT
generated columns. 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. VIRTUAL
generated columns. VIRTUAL
generated columns. CREATE TABLE t1 (a int as (1), b int as (a));
CREATE TABLE t1 (a int as (1));
In MariaDB 10.2.0 and before, the following statements apply to expressions for generated columns:
ERROR 1900 (HY000): A computed column cannot be based on a computed column
ERROR 1908 (HY000): Constant expression in computed column function is not allowed
When a generated column is PERSISTENT
or indexed, the value of the expression needs to be consistent regardless of the SQL Mode
flags in the current session. If it is not, then the table will be seen as corrupted when the value that should actually be returned by the computed expression and the value that was previously stored and/or indexed using a different sql_mode
setting disagree.
There are currently two affected classes of inconsistencies: character padding and unsigned subtraction:
VARCHAR
or TEXT
generated column the length of the value returned can vary depending on the PAD_CHAR_TO_FULL_LENGTH sql_mode
flag. To make the value consistent, create the generated column using an RTRIM() or RPAD() function. Alternately, create the generated column as a CHAR
column so that its data is always fully padded. SIGNED
generated column is based on the subtraction of an UNSIGNED
value, the resulting value can vary depending on how large the value is and the NO_UNSIGNED_SUBTRACTION sql_mode
flag. To make the value consistent, use CAST()
to ensure that each UNSIGNED
operand is SIGNED
before the subtraction. Beginning in MariaDB 10.5, there is a fatal error generated when trying to create a generated column whose value can change depending on the SQL Mode
when its data is PERSISTENT
or indexed.
For an existing generated column that has a potentially inconsistent value, a warning about a bad expression is generated the first time it is used (if warnings are enabled).
Beginning in MariaDB 10.4.8, MariaDB 10.3.18, and MariaDB 10.2.27 a potentially inconsistent generated column outputs a warning when created or first used (without restricting their creation).
Here is an example of two tables that would be rejected in MariaDB 10.5 and warned about in the other listed versions:
CREATE TABLE bad_pad ( txt CHAR(5), -- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed: vtxt VARCHAR(5) AS (txt) PERSISTENT ); CREATE TABLE bad_sub ( num1 BIGINT UNSIGNED, num2 BIGINT UNSIGNED, -- The resulting value can vary for some large values vnum BIGINT AS (num1 - num2) VIRTUAL, KEY(vnum) );
The warnings for the above tables look like this:
Warning (Code 1901): Function or expression '`txt`' cannot be used in the GENERATED ALWAYS AS clause of `vtxt` Warning (Code 1105): Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH Warning (Code 1901): Function or expression '`num1` - `num2`' cannot be used in the GENERATED ALWAYS AS clause of `vnum` Warning (Code 1105): Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
To work around the issue, force the padding or type to make the generated column's expression return a consistent value. For example:
CREATE TABLE good_pad ( txt CHAR(5), -- Using RTRIM() or RPAD() makes the value consistent: vtxt VARCHAR(5) AS (RTRIM(txt)) PERSISTENT, -- When not persistent or indexed, it is OK for the value to vary by mode: vtxt2 VARCHAR(5) AS (txt) VIRTUAL, -- CHAR -> CHAR is always OK: txt2 CHAR(5) AS (txt) PERSISTENT ); CREATE TABLE good_sub ( num1 BIGINT UNSIGNED, num2 BIGINT UNSIGNED, -- The indexed value will always be consistent in this expression: vnum BIGINT AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) VIRTUAL, KEY(vnum) );
In MariaDB 10.2.1 and later, the following statements apply to MySQL compatibility for generated columns:
STORED
keyword is supported as an alias for the PERSISTENT
keyword. In MariaDB 10.2.0 and before, the following statements apply to MySQL compatibility for generated columns:
STORED
keyword is not supported as an alias for the PERSISTENT
keyword. 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.
MariaDB's generated columns implementation does not enforce the following restrictions that are present in Microsoft SQL Server's computed columns implementation:
warning_count
. CONVERT_TZ()
function to be called with a named time zone as an argument, even though time zone names and time offsets are configurable. CAST()
function to be used with non-unicode character sets, even though character sets are configurable and differ between binaries/versions. 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. ARITHABORT
mode to be set, so that division by zero returns an error, and not a NULL. 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. Microsoft SQL Server enforces the above restrictions by doing one of the following things:
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
.
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.
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 (<expression>)
:
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.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/virtual-columns/