GENERATED COLUMNS

Introduction to MySQL Generated Column

When you create a new table, you specify the table columns in the CREATE TABLEstatement. Then, you use the INSERT, UPDATE, and DELETE statements to modify directly the data in the table columns.

MySQL 5.7 introduced a new feature called the generated column. Columns are generated because the data in these columns are computed based on predefined expressions.

For example, you have the contacts with the following structure:

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

To get the full name of a contact, you use the CONCAT() function as follows:

SELECT 
    id, 
    CONCAT(first_name, ' ', last_name), 
    email
FROM
    contacts;

This is not the most beautiful query yet.

By using a Generated Column, you can recreate the contacts table as follows:

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);

The GENERATED ALWAYS as (expression) is the syntax for creating a generated column.

To test the fullname column, you insert a row into the contacts table.

INSERT INTO contacts(first_name,last_name, email)
VALUES('Teslim','Adeyanju','info@adeyanjuteslim.co.uk');

Now, you can query data from the contacts table.

SELECT 
    *
FROM
    contacts;

id  first_name  last_name  fullname         email                      
--  ----------  ---------  ---------------  -------------------------  
1   Teslim      Adeyanju   Teslim Adeyanju  info@adeyanjuteslim.co.uk  

The values in the fullname column are computed on the fly when you query data from the contacts table.

MySQL provides two types of generated columns:

1. stored
2. virtual.

The virtual columns are calculated on the fly each time data is read whereas the stored columns are calculated and stored physically when the data is updated.

Based on this definition, the  fullname column that in the example above is a virtual column.

Syntax for MySQL Generated Column

The syntax for defining a generated column is as follows:

column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]]

Steps to Create a Generated Column in MySQL

1. Specify the Column Name and Data Type: Start by specifying the name of the column and its data type. This is similar to defining any other column in a table.
2. Add the GENERATED ALWAYS Clause: Use the GENERATED ALWAYS clause to indicate that this column is a generated (computed) column.
3. Specify the Type of Generated Column: Indicate whether the generated column is VIRTUAL or STORED.
• VIRTUAL: The value is computed when it is accessed and not stored physically.
• STORED: The value is computed and stored physically in the table. If you do not specify the type, MySQL defaults to VIRTUAL.
4. Define the Expression: Provide the expression that calculates the value of the generated column. This is done using the AS keyword followed by the expression in parentheses. The expression can include literals, built-in functions without parameters, operators, or references to other columns in the same table. The function used in the expression must be scalar and deterministic.
5. Define a Unique Constraint (Optional): If the generated column is stored, you can apply constraints such as a UNIQUE constraint.

MySQL Stored Column Example

Let’s look at the products table in the sample database:

The data from quantityInStock and buyPrice columns allow us to calculate the stock’s value per SKU using the following expression:

quantityInStock * buyPrice

However, we can add a stored generated column named stock_value to the products table using the following ALTER TABLE ...ADD COLUMN statement:

ALTER TABLE products
ADD COLUMN stockValue DEC(10,2)
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;

Typically, the ALTER TABLE statement requires a full table rebuild, therefore, it is time-consuming if you change the big tables. However, this is not the case for the virtual column.

Now, we can query the stock value directly from the products table.

SELECT
    productName,
    ROUND(stockValue, 2) stock_value
FROM
    products;