188.8.131.52 CREATE TABLE and Generated Columns
CREATE TABLE supports the specification of generated columns. Values of a generated column are computed from an expression included in the column definition.
Generated columns are supported by the
NDB storage engine beginning with MySQL NDB Cluster 7.5.3.
The following simple example shows a table that stores the lengths of the sides of right triangles in the
sideb columns, and computes the length of the hypotenuse in
sidec (the square root of the sums of the squares of the other sides):
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) ); INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
Selecting from the table yields this result:
mysql> SELECT * FROM triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+
Any application that uses the
triangle table has access to the hypotenuse values without having to specify the expression that calculates them.
Generated column definitions have this syntax:
col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']
AS ( indicates that the column is generated and defines the expression used to compute column values.
AS may be preceded by
GENERATED ALWAYS to make the generated nature of the column more explicit. Constructs that are permitted or prohibited in the expression are discussed later.
STORED keyword indicates how column values are stored, which has implications for column use:
VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any
BEFOREtriggers. A virtual column takes no storage.
InnoDBsupports secondary indexes on virtual columns. See Section 184.108.40.206, “Secondary Indexes and Generated Columns”.
STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.
The default is
VIRTUAL if neither keyword is specified.
It is permitted to mix
STORED columns within a table.
Other attributes may be given to indicate whether the column is indexed or can be
NULL, or provide a comment.
Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.
Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition:
Stored functions and user-defined functions are not permitted.
Stored procedure and function parameters are not permitted.
Variables (system variables, user-defined variables, and stored program local variables) are not permitted.
Subqueries are not permitted.
A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition. A generated column definition can refer to any base (nongenerated) column in the table whether its definition occurs earlier or later.
AUTO_INCREMENTattribute cannot be used in a generated column definition.
AUTO_INCREMENTcolumn cannot be used as a base column in a generated column definition.
As of MySQL 5.7.10, if expression evaluation causes truncation or provides incorrect input to a function, the
CREATE TABLEstatement terminates with an error and the DDL operation is rejected.
If the expression evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules. See Section 12.2, “Type Conversion in Expression Evaluation”.
If any component of the expression depends on the SQL mode, different results may occur for different uses of the table unless the SQL mode is the same during all uses.
CREATE TABLE ... LIKE, the destination table preserves generated column information from the original table.
CREATE TABLE ... SELECT, the destination table does not preserve information about whether columns in the selected-from table are generated columns. The
SELECT part of the statement cannot assign values to generated columns in the destination table.
Partitioning by generated columns is permitted. See Table Partitioning.
A foreign key constraint on a stored generated column cannot use
SET NULL, or
SET DEFAULT as
ON UPDATE referential actions, nor can it use
SET NULL or
SET DEFAULT as
ON DELETE referential actions.
A foreign key constraint on the base column of a stored generated column cannot use
SET NULL, or
SET DEFAULT as
ON UPDATE or
ON DELETE referential actions.
A foreign key constraint cannot reference a virtual generated column.
Triggers cannot use
NEW. or use
OLD. to refer to generated columns.
A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is
Generated columns have several use cases, such as these:
Virtual generated columns can be used as a way to simplify and unify queries. A complicated condition can be defined as a generated column and referred to from multiple queries on the table to ensure that all of them use exactly the same condition.
Stored generated columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly.
Generated columns can simulate functional indexes: Use a generated column to define a functional expression and index it. This can be useful for working with columns of types that cannot be indexed directly, such as
JSONcolumns; see Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.
For stored generated columns, the disadvantage of this approach is that values are stored twice; once as the value of the generated column and once in the index.
If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution, even if a query does not refer to the column directly by name. For details, see Section 8.3.10, “Optimizer Use of Generated Column Indexes”.
Suppose that a table
last_name columns and that applications frequently construct the full name using an expression like this:
SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;
One way to avoid writing out the expression is to create a view
t1, which simplifies applications by enabling them to select
full_name directly without using an expression:
CREATE VIEW v1 AS SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1; SELECT full_name FROM v1;
A generated column also enables applications to select
full_name directly without the need to define a view:
CREATE TABLE t1 ( first_name VARCHAR(10), last_name VARCHAR(10), full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name)) ); SELECT full_name FROM t1;