Syntax\n------\n\n<type>  [GENERATED ALWAYS]  AS   ( <expression> )\n[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]\n\nDescription\n-----------\n\nA 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).\n\nThere are two types of generated columns:\n\n PERSISTENT (a.k.a. STORED): This type's value is actually stored in the table.\n 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.\n\nGenerated columns are also sometimes called computed columns or virtual columns.\n\nSupported Features\n\nStorage Engine Support\n\n 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:\n\n``sql\nERROR 1910 (HY000): TokuDB storage engine does not support computed columns\n`\n\n InnoDB, Aria, MyISAM and CONNECT support generated columns.\n A column in a MERGE table can be built on a PERSISTENT generated column.\n   However, a column in a MERGE table can not be defined as a VIRTUAL and PERSISTENT generated column.\n\nData Type Support\n\nAll data types are supported when defining generated columns.\n\nUsing the ZEROFILL column option is supported when defining generated columns.\n\nUsing the AUTO_INCREMENT column option is not supported when defining generated columns.\n\nUsing the AUTO_INCREMENT column option is supported when defining generated columns.\n\nIt does not work correctly, though. See MDEV-11117.\n\nIndex Support\n\nUsing 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:\n\n`sql\nERROR 1903 (HY000): Primary key cannot be defined upon a computed column\n`\n\nUsing PERSISTENT generated columns as part of a foreign key is supported.\n\nReferencing PERSISTENT generated columns as part of a foreign key is also supported.\n\nHowever, 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:\n\n`sql\nERROR 1905 (HY000): Cannot define foreign key with ON UPDATE SET NULL clause on a computed column\n`\n\nDefining indexes on both VIRTUAL and PERSISTENT generated columns is supported.\n\nIf an index is defined on a generated column, then the optimizer considers using it in the same way as indexes based on "real" columns.\n\nThe optimizer can recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses. See Virtual Column Support in the Optimizer.\n\nThe optimizer cannot recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses. See Virtual Column Support in the Optimizer.\n\nStatement Support\n\nGenerated columns are used in DML queries just as if they were "real" columns.\n\n However, VIRTUAL and PERSISTENT generated columns differ in how their data is stored.\n   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.\n   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.\n\nThe SELECT statement supports generated columns.\n\nGenerated columns can be referenced in the INSERT, UPDATE, and DELETE statements.\n\n 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.\n\nThe CREATE TABLE statement has limited support for generated columns.\n\n It supports defining generated columns in a new table.\n It supports using generated columns to partition tables.\n It does not support using the versioning clauses with generated columns.\n\nThe ALTER TABLE statement has limited support for generated columns.\n\n It supports the MODIFY and CHANGE clauses for PERSISTENT generated columns.\n It does not support the MODIFY clause for VIRTUAL generated columns if ALGORITHM is not set to COPY. See MDEV-15476 for more information.\n It does not support the CHANGE clause for VIRTUAL generated columns if ALGORITHM is not set to COPY. See MDEV-17035 for more information.\n 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.\n 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.\n It also does not support altering an existing column into a VIRTUAL generated column.\n It supports using generated columns to partition tables.\n It does not support using the versioning clauses with generated columns.\n\nThe SHOW CREATE TABLE statement supports generated columns.\n\nThe DESCRIBE statement can be used to check whether a table has generated columns.\n\n 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:\n\n`sql\nDESCRIBE table1;\n+-------+-------------+------+-----+---------+------------+\n| Field | Type        | Null | Key | Default | Extra      |\n+-------+-------------+------+-----+---------+------------+\n| a     | int(11)     | NO   |     | NULL    |            |\n| b     | varchar(32) | YES  |     | NULL    |            |\n| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |\n| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |\n+-------+-------------+------+-----+---------+------------+\n`\n\nGenerated columns can be properly referenced in the NEW and OLD rows in triggers.\n\nStored procedures support generated columns.\n\nThe HANDLER statement supports generated columns.\n\nExpression Support\n\nMost legal, deterministic expressions which can be calculated are supported in expressions for generated columns.\n\nMost built-in functions are supported in expressions for generated columns.\n\n 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, an error is generated similar to the following:\n\n`sql\nERROR 1901 (HY000): Function or expression 'dayname()' cannot be used in the GENERATED ALWAYS AS clause of v\n`\n\nSubqueries are not supported in expressions for generated columns because the underlying data can change.\n\nUsing anything that depends on data outside the row is not supported in expressions for generated columns.\n\nStored functions are not supported in expressions for generated columns. See MDEV-17587 for more information.\n\nNon-deterministic built-in functions are supported in expressions for not indexed VIRTUAL generated columns.\n\nNon-deterministic built-in functions are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.\n\nUser-defined functions (UDFs) are supported in expressions for generated columns.\n\n 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.\n\nDefining a generated column based on other generated columns defined before it in the table definition is supported. For example:\n\n`sql\nCREATE TABLE t1 (a INT AS (1), b INT AS (a));\n`\n\nHowever, 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.\n\nUsing 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.\n\nUsing constant expressions is supported in expressions for generated columns. For example:\n\n`sql\nCREATE TABLE t1 (a int as (1));\n`\n\nMaking Stored Values Consistent\n\nWhen 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.\n\nThere are currently two affected classes of inconsistencies: character padding and unsigned subtraction:\n\n For a 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.\n If a 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.\n\nA fatal error is 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).\n\nFor 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).\n\nA potentially inconsistent generated column outputs a warning when created or first used (without restricting the creation).\n\nA potentially inconsistent generated column does not output a warning when created or first used.\n\nHere is an example of two tables that are warned about:\n\n`sql\nCREATE TABLE bad_pad (\n  txt CHAR(5),\n  -- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:\n  vtxt VARCHAR(5) AS (txt) PERSISTENT\n);\n\nCREATE TABLE bad_sub (\n  num1 BIGINT UNSIGNED,\n  num2 BIGINT UNSIGNED,\n  -- The resulting value can vary for some large values\n  vnum BIGINT AS (num1 - num2) VIRTUAL,\n  KEY(vnum)\n);\n`\n\nThe warnings look like this:\n\n`sql\nWarning (Code 1901): Function or expression 'txt' cannot be used in the GENERATED ALWAYS AS clause of vtxt\nWarning (Code 1105): Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH\n\nWarning (Code 1901): Function or expression 'num1 - num2' cannot be used in the GENERATED ALWAYS AS clause of vnum\nWarning (Code 1105): Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION\n`\n\nTo work around the issue, force the padding or type to make the generated column's expression return a consistent value. For example:\n\n`sql\nCREATE TABLE good_pad (\n  txt CHAR(5),\n  -- Using RTRIM() or RPAD() makes the value consistent:\n  vtxt VARCHAR(5) AS (RTRIM(txt)) PERSISTENT,\n  -- When not persistent or indexed, it is OK for the value to vary by mode:\n  vtxt2 VARCHAR(5) AS (txt) VIRTUAL,\n  -- CHAR -> CHAR is always OK:\n  txt2 CHAR(5) AS (txt) PERSISTENT\n);\n\nCREATE TABLE good_sub (\n  num1 BIGINT UNSIGNED,\n  num2 BIGINT UNSIGNED,\n  -- The indexed value will always be consistent in this expression:\n  vnum BIGINT AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) VIRTUAL,\n  KEY(vnum)\n);\n`\n\nHere is an example of two tables whose creation is rejected:\n\n`sql\nCREATE TABLE bad_pad (\n  txt CHAR(5),\n  -- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:\n  vtxt VARCHAR(5) AS (txt) PERSISTENT\n);\n\nCREATE TABLE bad_sub (\n  num1 BIGINT UNSIGNED,\n  num2 BIGINT UNSIGNED,\n  -- The resulting value can vary for some large values\n  vnum BIGINT AS (num1 - num2) VIRTUAL,\n  KEY(vnum)\n);\n`\n\nMySQL Compatibility Support\n\n The STORED keyword is supported as an alias for the PERSISTENT keyword.\n Tables created with MySQL 5.7 or later that contain MySQL's generated columns can be imported into MariaDB without a dump and restore.\n\nImplementation Differences\n\nGenerated 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.\n\nImplementation Differences Compared to Microsoft SQL Server\n\nMariaDB's generated columns implementation does not enforce the following\\\nrestrictions that are present in Microsoft SQL Server's computed columns implementation:\n\n MariaDB allows server variables in generated column expressions, including those that change dynamically, such as warning_count.\n 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.\n MariaDB allows the CAST() function to be used with non-unicode character sets, even though character sets are configurable and differ between binaries/versions.\n 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.\n Microsoft SQL Server requires the ARITHABORT mode to be set, so that division by zero returns an error, and not a NULL.\n 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.\n\nMicrosoft SQL Server enforces the above restrictions by doing one of the following things:\n\n Refusing to create computed columns.\n Refusing to allow updates to a table containing them.\n* Refusing to use an index over such a column if it can not be guaranteed that the expression is fully deterministic.\n\nIn MariaDB, as long as the sql_mode, language, and other settings that were in effect duri\n\n[Truncated — full content at mariadb.com/docs]