MySQL Generated Columns Tutorial and Examples

In this article, we’ll introduce the usage of generated columns in MySQL.

What is a generated column

In MySQL, a GENERATED COLUMN is a special column whose value is automatically calculated based on the expression specified in the column definition. Also, you cannot directly write or update the value of the generated column.

There are 2 types of generated columns:

  • Virtual Generated Columns: The column values ​​are not stored. When reading this column, MySQL automatically calculates the value of this column.
  • Stored generated columns: When inserting or modifying data, MySQL automatically calculates the value of this column and stores it on disk.

Syntax for Generated Columns

To create a generated column, use the following syntax to define a column:

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

Note the second line [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]:

  • The keyword GENERATED ALWAYS indicates that this column is a generated column. It is optional.

  • AS (expr) set the expression for this generated column.

  • The VIRTUAL or STORED keyword indicates whether to store the column value. This is optional.

    • VIRTUAL: The column values ​​will not be stored. When reading this column, MySQL automatically calculates the value of this column. It is the default value.
    • STORED: When inserting or modifying data, MySQL automatically calculates the value of this column and stores it.

You can define a generated column when creating a table with the CREATE TABLE statement, or add a generated column with the ALTER TABLE statement.

Generate column Examples

Suppose, you have an order details table with the following structure:

CREATE TABLE order_item (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  goods VARCHAR(45) NOT NULL,
  price DECIMAL NOT NULL,
  quantity INT NOT NULL
);

Let’s insert some rows int the test data:

INSERT INTO order_item (goods, price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);

Let’s take a look at all rows in the order_item table:

SELECT * FROM order_item;

+----------+-------+-------+----------+
| order_id | goods | price | quantity |
+----------+-------+-------+----------+
|        1 | Apple |     5 |        3 |
|        2 | Peach |     4 |        4 |
+----------+-------+-------+----------+
2 rows in set (0.00 sec)

Now, we use the following SQL to query the total amount of each order item:

SELECT
  goods,
  price,
  quantity,
  (price * quantity) AS total_amount
FROM order_item;

Here, we calculate the total amount via price * quantity, and use an alias total_amount.

+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple |     5 |        3 |           15 |
| Peach |     4 |        4 |           16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)

There is no doubt that this is correct.

MySQL generated columns can simplify our work and you don’t need to write such complex SELECT statements. Now we want to add a generated column with the following statement:

ALTER TABLE order_item
ADD COLUMN total_amount DECIMAL
  GENERATED ALWAYS AS (price * quantity) STORED;

Here, we’ve added a total_amount column. It is a generated column and its computed expression is price * quantity.

Now take a look at the structure of order_item:

DESC order_item;

+--------------+---------------+------+-----+---------+------------------+
| Field        | Type          | Null | Key | Default | Extra            |
+--------------+---------------+------+-----+---------+------------------+
| order_id     | int           | NO   | PRI | NULL    | auto_increment   |
| goods        | varchar(45)   | NO   |     | NULL    |                  |
| price        | decimal(10,0) | NO   |     | NULL    |                  |
| quantity     | int           | NO   |     | NULL    |                  |
| total_amount | decimal(10,0) | YES  |     | NULL    | STORED GENERATED |
+--------------+---------------+------+-----+---------+------------------+

Here, we found that, total_amount is a stored generated column.

We query the data in the table by using the statement:

SELECT
  goods,
  price,
  quantity,
  total_amount
FROM order_item;

+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple |     5 |        3 |           15 |
| Peach |     4 |        4 |           16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)

Now, we are much more relaxed. We no longer need to write such complex SQL statements like above.

Update generated column

You cannot directly write or update the value of a generated column. This will causes an error. Let’s try it out and see what happens.

Let’s try inserting a data with generated column values ​​first:

INSERT INTO order_item (goods, price, quantity, total_amount)
VALUES ('Banana', 6, 4, 24);

MySQL returned the error: ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed..

Let’s try again to modify the value of the generated column:

UPDATE order_item
SET total_amount = 30
WHERE goods = 'Apple';

This returned the same error: ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed..

VIRTUAL vs STORED

There are two types of generated columns: VIRTUAL and STORED. There are some differences between them:

  • Virtual generated columns do not require storage space; storage generated columns require storage space.
  • The value of the virtual generated column is recalculated on each read operation; the value of the stored generated column is calculated when the row is inserted or modified.

If the data changes frequently, consider using a virtual generated column; if the data does not change frequently after creation, consider using a stored generated column.

Like in the above example, an order is generally not changed after it is created, and it is very suitable to use storage to generate columns here.

Conclusion

In this article, we learned the usage of MySQL generated columns. Here are the main points of this article:

  • A generated column is a column whose value can be calculated automatically.
  • Generated columns have 2 types: VIRTUAL and STORED.
  • You can not insert or update the values of generated columns.