For INSERT , REPLACE , and UPDATE , if a generated column is inserted into, replaced, or updated explicitly, the only permitted value is DEFAULT . 

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 DEFAULT . 

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 JSON  columns; 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.11, “Optimizer Use of Generated Column Indexes” . 

Example: 

Suppose that a table t1  contains first_name  and 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 v1  on 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: 

## 13.1.20.9 Secondary Indexes and Generated Columns 

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; 

InnoDB  supports secondary indexes on virtual generated columns. Other index types are not supported. A secondary index defined on a virtual column is sometimes referred to as a “virtual index” . 

