A very practical feature was introduced in Mysql 5.7
Virtual column Generated (Virtual) Columns
For its purpose, we explain it through a scenario
Suppose there is a table containing a column of type date
`SimpleDate` dateSimpleDate is a commonly used query field and requires a date function to be executed on it, for example
SELECT ... WHERE dayofweek(SimpleDate) = 3 ...The problem at this time is that even if you index SimpleDate, this query statement cannot be used because the date function prevents the index
To improve query efficiency, additional operations are usually performed, such as creating a new field SimpleDate_dayofweek, storing the calculation results of dayofweek(SimpleDate), and then creating an index for this column
The value of SimpleDate_dayofweek needs to be written by the program, for example, using a trigger, updated when SimpleDate changes
This way the query can be changed to
SELECT ... WHERE SimpleDate_dayofweek = 3 ...The advantage of this is that it improves query performance, and can use the index of the SimpleDate_dayofweek column, but it also brings other troubles, such as
(1) Reduced data writing performance
(2) Add redundant data, occupying more storage space
(3) Increase code maintenance costs
Virtual columnGenerated Columns is used to solve this problem. It can add an indexable column, but it does not actually exist in the data table.
For the example above, you can create a virtual column for SimpleDate and then create an index for the virtual column, such as
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`SimpleDate` date DEFAULT NULL,
...
`SimpleDate_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(SimpleDate)) VIRTUAL,
PRIMARY KEY (`id`),
KEY `SimpleDate_dayofweek` (`SimpleDate_dayofweek`),
) ENGINE=InnoDBThis way, a virtual column is created, and the value of this column is not calculated without us
SELECT ... WHERE SimpleDate_dayofweek = 3 ...Query statements can use indexes normally
Through virtual columns, the query performance is met, and there will be no potential trouble with the previous solution.
Virtual columns are not stored in data rows, but the metadata information of virtual columns will exist in the relevant system tables. The addition or deletion of virtual columns will only involve these system tables and will not lead to the reconstruction of the data tables, so it is very efficient.
It should be noted that joint indexes of virtual and real columns cannot be established