Virtual and persistent columns

We described a view on a virtual table, meaning that its contents are the results of a query. The idea behind virtual columns is very similar: they are columns based on SQL expressions.

A virtual column overview

The terminology is a bit confusing here. In fact, all automatically calculated columns are called virtual columns in MariaDB KnowledgeBase. However, there are two types of virtual columns: VIRTUAL columns and PERSISTENT columns.

• A VIRTUAL column is calculated on-the-fly when it is referenced in a statement—for example, when a SELECT clause extracts its value. This is the default type.
• A PERSISTENT column is written in the table and its value can be read when needed, as happens with regular columns.

Calculating each value on-the-fly is additional work for the CPU, but saves space on the disk. Only PERSISTENT columns can be indexed or used in a foreign key.

In both cases, the calculated values cannot be directly inserted or modified. MariaDB automatically...