A generated column is a database column whose value is an expression. Therefore, the value of the generated column is usually derived (for example, it could be derived from values in other columns).
Generated columns are also sometimes referred to as computed columns or virtual columns.
Creating a generated column in MariaDB is essentially the same as creating a normal column. The difference is that the definition of the generated column contains an expression that determines the column’s value.
Syntax
In MariaDB, generated columns have the following syntax:
<type> [GENERATED ALWAYS] AS ( <expression> )
[VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]Example
Here’s an example of creating a table that contains a generated column:
CREATE TABLE Employees (
Id INTEGER PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
FullName VARCHAR(101) AS (CONCAT(FirstName, ' ', LastName))
);The last column (called FullName) is the generated column.
In this case, our generated column simply concatenates the FirstName and LastName columns. This is a good example of what a generated column is. Its value depends on the outcome of the expression. In our case, we used the CONCAT() function to perform the concatenation.
Now let’s insert data into the first three columns (but not the generated column) and select all data from the table:
INSERT INTO Employees (Id, FirstName, LastName)
VALUES (0001, 'Jess', 'Brown');
INSERT INTO Employees (Id, FirstName, LastName)
VALUES (0002, 'Rohit', 'Ahuja');
INSERT INTO Employees (Id, FirstName, LastName)
VALUES (0003, 'Zohan', 'Ahmad');
INSERT INTO Employees (Id, FirstName, LastName)
VALUES (0004, 'Homer', 'Clooney');
INSERT INTO Employees (Id, FirstName, LastName)
VALUES (0005, 'Lisa', 'Smith');
SELECT * FROM Employees;Result:
Id FirstName LastName FullName ------- --------------- --------------- ------------- 1 Jess Brown Jess Brown 2 Rohit Ahuja Rohit Ahuja 3 Zohan Ahmad Zohan Ahmad 4 Homer Clooney Homer Clooney 5 Lisa Smith Lisa Smith
As we can see, our generated column (FullName) contains the concatenation of the FirstName and LastName columns.
Virtual vs Persistent (Stored)
Generated columns can be virtual or persistent (also known as stored).
VIRTUAL: Column values are generated dynamically when the table is queried (i.e they are are not stored in the table). A virtual column takes no storage.PERSISTENT(akaSTORED): Column values are stored in the table. These 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. Therefore, if none of the above keywords are specified, the generated column is a virtual column. So in our example above, we added a virtual column.
Let’s drop our generated column and add it again, this time as a stored column:
ALTER TABLE Employees DROP COLUMN FullName;
ALTER TABLE Employees
ADD FullName VARCHAR(101) GENERATED ALWAYS AS (CONCAT(FirstName, ' ', LastName)) STORED;Now let’s query the table again:
SELECT * FROM Employees;Result:
Id FirstName LastName FullName ------- --------------- --------------- ------------- 1 Jess Brown Jess Brown 2 Rohit Ahuja Rohit Ahuja 3 Zohan Ahmad Zohan Ahmad 4 Homer Clooney Homer Clooney 5 Lisa Smith Lisa Smith
We get the same result. The only difference is that the values in the FullName column are stored, rather than being generated dynamically when we run the query. Our stored generated column also takes up space in the database (the virtual column doesn’t), and the generated column can be indexed (the virtual column can’t).
Note that STORED is an alias for PERSISTENT and can only be used in MariaDB 10.2.1 and later. It was introduced for compatibility with MySQL.