Create a Generated Column in MySQL

Posted on by Ian

This article contains an example of adding a generated column to a table in MySQL.

Also known as computed columns, generated columns usually contain values that are dependent on other factors (such as the values in other columns).

Creating (or adding) a generated column in MySQL is basically the same as creating a normal column, except that the definition of the generated column contains an expression that determines the column’s value.

Syntax

In MySQL, generated columns have the following syntax:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Example

Suppose we run the following query against a table called Products:

SELECT
  ProductName,
  ProductPrice,
  Quantity
FROM Products;

Result:

+-------------------------------------+--------------+----------+
| ProductName                         | ProductPrice | Quantity |
+-------------------------------------+--------------+----------+
| Left handed screwdriver             |        25.99 |       10 |
| Right handed screwdriver            |        25.99 |       12 |
| Long Weight (blue)                  |        14.75 |        5 |
| Long Weight (green)                 |        11.99 |        3 |
| Smash 2000 Sledge Hammer            |         NULL |        7 |
| Chainsaw (Includes 5 spare fingers) |       245.00 |       21 |
| Straw Dog Box                       |        55.99 |       31 |
| Bottomless Coffee Mugs (4 Pack)     |         9.99 |        9 |
+-------------------------------------+--------------+----------+

Here, we have a list of products and their prices. We also have a Quantity column that contains the number of each product in stock.

We could add a generated column to this table that calculates each product’s price by the quantity in stock.

Here’s how we could do that:

ALTER TABLE Products ADD TotalValue INT AS (Quantity * ProductPrice);

That’s all we needed to do in order to create a generated column.

We could have optionally included GENERATED ALWAYS (before the AS keyword) to make the definition more explicit. Either way, the result is the same.

In this case, our computed column multiplies the Quantity column by the ProductPrice column. That gives us the total value of all products in stock.

Now let’s run the previous query again, but this time we’ll add the newly created generated column to our query:

SELECT
  ProductName,
  ProductPrice,
  Quantity,
  TotalValue
FROM Products;

Result:

+-------------------------------------+--------------+----------+------------+
| ProductName                         | ProductPrice | Quantity | TotalValue |
+-------------------------------------+--------------+----------+------------+
| Left handed screwdriver             |        25.99 |       10 |        260 |
| Right handed screwdriver            |        25.99 |       12 |        312 |
| Long Weight (blue)                  |        14.75 |        5 |         74 |
| Long Weight (green)                 |        11.99 |        3 |         36 |
| Smash 2000 Sledge Hammer            |         NULL |        7 |       NULL |
| Chainsaw (Includes 5 spare fingers) |       245.00 |       21 |       5145 |
| Straw Dog Box                       |        55.99 |       31 |       1736 |
| Bottomless Coffee Mugs (4 Pack)     |         9.99 |        9 |         90 |
+-------------------------------------+--------------+----------+------------+

We can see that our generated column has been added to the table, and its value is determined by the expression that we gave it. In this case, its value is determined by the values in the ProductPrice and Quantity columns.

We can use the computed column to perform other calculations if we want. For example, we can use the SUM() function calculate the total value of all products in stock:

SELECT SUM(TotalValue)
FROM Products;

Result:

+-----------------+
| SUM(TotalValue) |
+-----------------+
|            7653 |
+-----------------+

Virtual vs Stored

Generated columns can be virtual or stored.

  • VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.
  • 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. In other words, if none of the above keywords are specified, the generated column is a virtual column. Therefore, in our example, we added a virtual column.

Let’s drop our generated column and add it again, this time as a stored column:

ALTER TABLE Products DROP COLUMN TotalValue;
ALTER TABLE Products 
  ADD TotalValue INT GENERATED ALWAYS AS (Quantity * ProductPrice) STORED;

Now let’s query the table again:

SELECT
  ProductName,
  ProductPrice,
  Quantity,
  TotalValue
FROM Products;

Result:

+-------------------------------------+--------------+----------+------------+
| ProductName                         | ProductPrice | Quantity | TotalValue |
+-------------------------------------+--------------+----------+------------+
| Left handed screwdriver             |        25.99 |       10 |        260 |
| Right handed screwdriver            |        25.99 |       12 |        312 |
| Long Weight (blue)                  |        14.75 |        5 |         74 |
| Long Weight (green)                 |        11.99 |        3 |         36 |
| Smash 2000 Sledge Hammer            |         NULL |        7 |       NULL |
| Chainsaw (Includes 5 spare fingers) |       245.00 |       21 |       5145 |
| Straw Dog Box                       |        55.99 |       31 |       1736 |
| Bottomless Coffee Mugs (4 Pack)     |         9.99 |        9 |         90 |
+-------------------------------------+--------------+----------+------------+

We get the same result. The only difference is that the values in the TotalValue 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).

Restrictions of Generated Columns

Generated columns in MySQL have a quite a few restrictions (for example, an AUTO_INCREMENT column cannot be used as a base column in a generated column definition). See MySQL’s documentation for a complete list of restrictions.