MySQL Generated Columns

In this tutorial, we will learn what the generated columns are in MySQL and how to use them in your tables with practical examples. It is very easy to understand and use generated columns through some basic examples. So, let’s get started!

Also read: MySQL Interval – Detailed Guide

Introduction to MySQL Generated Columns

In MySQL, you specify the column name and the data type of the values that column will contain while creating a table. To manipulate the data, you use the INSERT, UPDATE and DELETE statements.

MySQL provides us with a new feature called “generated columns”. Generated columns are similar to standard columns, however, they are computed from the expression included in the column definition.

Take an example of calculating the area of a circle. You can create a column to store the radius of a circle. You can make use of a generated column to use the radius column value to calculate the area and store it in the new column. The new column is created while defining the table.

See the below code –

Old way –

--Creating a table to store the radius
CREATE TABLE findArea(
radius FLOAT
);

--Writing a select query to display radisu and area
SELECT radius,
(3.14 * radius * radius) AS Area
FROM findArea; Code language: SQL (Structured Query Language) (sql)

As you can see, you can write a query to find the area. But it is not the best way.

We can write the above queries using the generated column.

CREATE TABLE findArea(
radius FLOAT,
area FLOAT GENERATED ALWAYS AS(ROUND((3.14 * radius * radius),2))
);

SELECT * FROM findArea;Code language: SQL (Structured Query Language) (sql)

As you can see, the select query looks very clean.

MySQL Generated Column Syntax

The syntax to define a generated column is as follows-

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']Code language: SQL (Structured Query Language) (sql)

Remember the following points before defining a generated column-

• The “GENERATED ALWAYS” is optional.
• You can not explicitly insert a value in the generated column using the insert statement.
• If you try to insert a value into the generated column, the following error will occur.

• The “exp” is an expression which indicates that the column is generated and will calculate the value for a generated column.
• The column can have two types- STORED or VIRTUAL
• Let’s see what are the STORED and VIRTUAL types.

Generated Column Type – STORED

The STORED column will evaluate and store the values at the time of insert and update operations. That means, the STORED column requires storage space, just like other columns and can be indexed as well.

You need to explicitly define the column type as STORED.

Generated Column Type – VIRTUAL

The generated columns are set to the VIRTUAL by default.

Unlike the STORED generated columns, the VIRTUAL columns are virtually created and don’t require any storage space. The VIRTUAL column values are evaluated at the time of the read operation, immediately after the BEFORE triggers.

If you don’t define the type of a generated column, mysql will consider it as a VIRTUAL.

If you check the table description of generated columns, it will show either STORED or VIRTUAL. Have a look –

Examples of MySQL Generated Columns

Here, we will create a table to store the length and the breadth of a rectangle and calculate the area and perimeter and store them in the table using generated columns.

Note that, you can choose column type as either STORED or VIRTUAL. It totally depends on you.

CREATE TABLE findAP(
id INT AUTO_INCREMENT PRIMARY KEY,
length FLOAT,
breadth FLOAT,
peri FLOAT AS(ROUND((2*length + 2*breadth),3)),
area FLOAT AS(ROUND((length * breadth),3))
);Code language: SQL (Structured Query Language) (sql)

Note that, we have created the columns of type VIRTUAL. Let’s, insert some values into the table.

INSERT INTO findAP(length,breadth) VALUES
(10.3,5.7),(49,8.4),(12.8,12);Code language: SQL (Structured Query Language) (sql)

All good. Now we will display the table and see if we get the correct result.

SELECT * FROM findAP;Code language: SQL (Structured Query Language) (sql)

As you can see, we have received perfect and expected output.

Conclusion

MySQL Generated columns are an awesome feature provided by the team MySQL. You can make use of it for various applications such as calculating the age based on birthdate, calculating complex maths problems and storing them in a column, etc. I hope you find this tutorial helpful and learn something new.

References

MYSQL Official documentation on Generated Columns.