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;

productName                                  stock_value  
-------------------------------------------  -----------  
1969 Harley Davidson Ultimate Chopper        387209.73    
1952 Alpine Renault 1300                     720126.90    
1996 Moto Guzzi 1100i                        457058.75    
2003 Harley-Davidson Eagle Drag Bike         508073.64    
1972 Alfa Romeo GTA                          278631.36    
1962 LanciaA Delta 16V                       702325.22    
1968 Ford Mustang                            6483.12      
2001 Ferrari Enzo                            345940.21    
1958 Setra Bus                               123004.10    
2002 Suzuki XREO                             662501.19    
1969 Corvair Monza                           615600.84    
1968 Dodge Charger                           685684.68    
1969 Ford Falcon                             87119.45     
1970 Plymouth Hemi Cuda                      180762.96    
1957 Chevy Pickup                            341162.50    
1969 Dodge Charger                           430079.79    
1940 Ford Pickup Truck                       152416.29    
1993 Mazda RX-7                              331952.25    
1937 Lincoln Berline                         526969.66    
1936 Mercedes-Benz 500K Special Roadster     209485.10    
1965 Aston Martin DB5                        596410.