
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:

* Refusing to create computed columns.
* Refusing to allow updates to a table containing them.
* Refusing to use an index over such a column if it can not be guaranteed that
the expression is fully deterministic.

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.

If you try to update a virtual column, you will get an error if the default
strict mode is enabled in sql_mode, or a warning otherwise.

Development History
-------------------

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.

Examples
--------

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.

URL: https://mariadb.com/kb/en/generated-columns/