Summary: in this tutorial, you will learn how to use the MySQL generated columns to store data computed from an expression or other columns.
Introduction to MySQL Generated Column
When you create a new table, you specify the table columns in the CREATE TABLE
statement. 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
);
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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
);
Code language: PHP (php)
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('john','doe','[email protected]');
Code language: SQL (Structured Query Language) (sql)
Now, you can query data from the contacts
table.
SELECT
*
FROM
contacts;
Code language: SQL (Structured Query Language) (sql)
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: stored and 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]]
Code language: SQL (Structured Query Language) (sql)
First, specify the column name and its data type.
Next, add the GENERATED ALWAYS
clause to indicate that the column is a generated column.
Then, indicate the type of the generated column by using the corresponding option: VIRTUAL
or STORED
. By default, MySQL uses VIRTUAL
if you don’t specify explicitly the type of the generated column.
After that, specify the expression within the braces after the AS
keyword. The expression can contain literals, and built-in functions with no parameters, operators, or references to any column within the same table. If you use a function, it must be scalar and deterministic.
Finally, if the generated column is stored, you can define a unique constraint for it.
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
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
Summary
- Use a MySQL Generated column to store data computed from an expression or other columns.