What is the 'virtual columns 'of MySQL 5.7?

What is the 'virtual columns 'of MySQL 5.7?

MySQL 5.7 introduces a very practical function: Virtual column Generated (Virtual) Columns. For its purpose, we use a scenario to describe it. Suppose there is a table containing a column of the date type.

`SimpleDate` date

SimpleDate is a common query field and needs to execute a date function for it, such

SELECT ... WHERE dayofweek(SimpleDate) = 3 ...

In this case, even if you create an index for SimpleDate, this query statement cannot be used because the date function blocks the index.

To improve query efficiency, you usually need to perform additional operations, such as creating a new field SimpleDate_dayofweek, storing the calculation result of dayofweek (SimpleDate), and then creating an index for this column.

SimpleDate_dayofweek value needs to be written by the program. For example, if a trigger is used, it is updated when SimpleDate is changed.

In this way, the query can be changed

SELECT ... WHERE SimpleDate_dayofweek = 3 ...

This improves the query performance. You can use the index of the SimpleDate_dayofweek column, but it brings other troubles, such

(1) reduced Data Writing Performance

(2) increased redundant data and occupied more storage space

(3) increase code maintenance costs

The virtual column Generated Columns is used to solve this problem. You can add a column that can be indexed, but it does not actually exist in the data table.

For the above example, you can create a virtual column for SimpleDate and then create an index for the virtual column, as shown in

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=InnoDB

In this way, the virtual column is created, and the value of this column does not need to be calculated.

SELECT ... WHERE SimpleDate_dayofweek = 3 ...

The query statement can use indexes normally.

By using virtual columns, the query performance is satisfied, and there is no potential trouble with the previous solution.

Virtual columns are not stored in data rows, but metadata information of virtual columns is stored in related system tables. adding or deleting virtual columns only involves these system tables, it will not lead to data table reconstruction, so the efficiency is very high

Note that you cannot create a joint index for virtual and real columns.

This article permanently updates the link address: