Generated Columns is a feature released on MySQL 5.7. They can be used during
CREATE TABLE or
ALTER TABLE statements. It is a way of storing data without actually sending it through the
UPDATE clauses in SQL. The database resolves what the data will be.
There are two types of Generated Columns: Virtual and Stored. They work with:
- mathematical expressions (
- built-in functions (
- literals (“2”, “new”, 0)
Besides that, they can be indexed but they don’t allow subqueries in it.
A Generated Column works within the table domain. If you need subqueries on a particular column, you may have to look at Views.
The basic example
As an example I am going to use an e-commerce database as based on my past experience of what I have seen and worked. You will probably have at least these tables or something similar:
users– stores user info
products– stores product info like price and description
orders– stores the user_id, date of order
orders_items– stores product_id, order_id, quantity and price at the time of purchase
This is the whole DB: Gist.
The retrieval would bring:
One example is to get the total of that order_item row, something like
total_item_price that would store the value of
quantity to show how much the summed amount of an item would be. Some databases have the
MONEY type to store
price, as with MySQL it is recommended to work with
People solve this problem in different ways:
- store the calculated price on a new column to make it easier to retrieve;
- create a view;
- or they calculate in the application itself, which in this case might cause problems due to how the language handles floats. There are libraries to deal with money values in a lot of languages and frameworks, however, the overhead of converting each row into a money object could be costly depending on the amount of data being transferred.
Another way I’ve seen is: people calculate in the query the total amount for the orders_items row as
- They take no disk space, except when using a Virtual Column as in a Secondary Index.
- They are an
INPLACEoperation: it means the table definition is changed without having to recopy all the data again. More info.
- The values are calculated on the fly during read operations and
Consider using virtual columns for data where changes happens in a significant number of times. The cost of a Virtual Column comes from reading a table constantly and the server has to compute every time what that column value will be.
- They do use disk space.
- It has the same cost of adding a new column, so it is a
- Values are updated in every
You should consider using Stored Columns for when the data doesn’t change significantly or at all after creation, like for instance, the example above with the
orders_items table. Once a purchase is made, the price of the product is stored, not being changed, neither the quantity. Considering this information we could create
total_item_price as a Stored Column.
Creating a table
-- Virtual Column CREATE TABLE `orders_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_id` int(10) unsigned NOT NULL, `product_id` int(10) unsigned NOT NULL, `product_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `quantity` int(10) unsigned NOT NULL DEFAULT 1, `total_item_price` decimal(10,2) AS (`quantity` * `product_price`), `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` varchar(45) NOT NULL DEFAULT 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; -- Stored Column CREATE TABLE `orders_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_id` int(10) unsigned NOT NULL, `product_id` int(10) unsigned NOT NULL, `product_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `quantity` int(10) unsigned NOT NULL DEFAULT 1, `total_item_price` decimal(10,2) AS (`quantity` * `product_price`) STORED, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` varchar(45) NOT NULL DEFAULT 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Notice how the definition changes on line 9 and 23: you have another keyword,
AS, then an expression and specifically on line 23 you see a
STORED keyword. In both lines they are generated columns, if nothing is specified will be a
Altering a table
It uses the same syntax as adding a column, just adding the “AS (expression)” after the data type:
-- `full_name` as VIRTUAL COLUMN ALTER TABLE users ADD COLUMN `full_name` VARCHAR(500) AS (CONCAT_WS(" ", `first_name`, `last_name`)); -- `total_item_price` as STORED COLUMN ALTER TABLE orders_items ADD COLUMN `total_item_price` DECIMAL(10, 2) AS (`quantity` * `product_price`) STORED;
It is also possible to extract data from JSON fields using generated columns. As the functions for JSON are built-in,
JSON_UNQUOTE as well “->” and “->>” work as expressions for a generated column:
-- Stored Columns ALTER TABLE `twitter_users` ADD COLUMN `location` VARCHAR(255) AS (response->>"$.location") STORED;
When the type is
STORED, it must be specified after the expression otherwise the default behaviour will be to be
Generated columns can have indexes created as the following, no matter if stored, virtual or extracted from a JSON field:
ALTER TABLE users ADD INDEX `ix_full_name` (`full_name`);
Which is the same syntax for normal columns.