Click on the blue letters above to follow us


In our day-to-day development process, sometimes an index fails because of a function call to an indexed column. For example, let’s say we want to query records by month, and when we only have time left in the table, if we use the following statement, where create_time is the index column

Although it is possible to find the correct result, we can find that the index is not taken by explaining. So we might change it to make sure we use indexes

Or simply redundant a column field for one month in a database table and create an index on that month. If we are using mysql version 5.7, we can use a new feature provided by the mysql version 5.7 – virtual columns to achieve the above effect

Virtual columns

In mysql 5.7 supports 2 kinds of virtual columns and stored columns. The difference between the two is that virtual only calculates the result when reading rows, but is not physically stored, so it does not occupy storage space, and only builds secondary indexes on the InnoDB engine, while stored is calculated and stored when row data is inserted or updated, which requires physical space and supports indexing in MyISAM and InnoDB engines

mysql5.7 The default virtual column type is virtual columns

Create a virtual column syntax

Considerations for using virtual columns

a. The definition of the derived column can be modified, but the virtual and stored cannot be converted to each other, and if necessary, it needs to be deleted and rebuilt

b. Virtual column fields are read-only, INSRET and UPDATE are not supported

c. You can only refer to the non-generated column field of this table, and you cannot refer to the fields of other tables

The expressions and operators used must be Immutable properties, for example, you cannot use CONNECTION_ID(), CURRENT_USER(), NOW()

e, you can convert existing ordinary columns into derived columns of stored type, but virtual types do not; Similarly, a derived column of type stored can be converted to a normal column, but not of the virtual type

f. Increment (AUTO_INCREMENT) is not allowed in virtual column definitions, nor incremented base columns

g. Virtual columns allow modification of expressions, but do not allow modification of storage methods (can only be modified by deleting and re-creating)

h. If the virtual column is used as an index, there is a disadvantage that the value will be stored twice. Once as the value of the virtual column and once as the value in the index

Scenarios for using virtual columns

a. Virtual columns can simplify and unify the query, define complex conditions as generated columns, and directly use virtual columns (instead of views) when querying.

b. Storage virtual columns can be used as instantiation caches for dynamic computationally expensive complex conditions

c. Virtual columns can simulate functional indexes and can use indexes, which is useful for columns that cannot directly use indexes (JSON columns).


Because mysql 5.7 also supports json columns, this example demonstrates an example with json and virtual columns

Create a sample table

Create a virtual column

Note: Virtual columns can be created directly when the table is created. This example is to highlight the virtual column syntax

Normal our json statement is as follows

We remove the double quotes by JSON_UNQUOTE, otherwise the value of the generated virtual column v_user_name will become “likairui”, and the actual field value we need should be likairui

Because mysql 5.7 json is not the focus of this article, this article will not discuss, if you are interested in mysql5.7 json syntax function friends can view the following link

Create an index for the virtual column

View the resulting table data

See if an index is used

Note: In mysql 8.0 you can use EXPLAIN ANALYZE, he can see the time consumption of SQL

Small details at the code level

Because virtual columns cannot be inserted and updated, special attention should be paid to when using the orm framework. For example, when using mybatis-plus, remember to add the following annotation to the mapped field of the virtual column of the entity

With this annotation, the virtual column fields are not updated or inserted


This article is based on mysql 5.7 a general introduction to the virtual column, if you use mysql 8.0.13 or above version, you can function index, his implementation is essentially based on the virtual column implementation. The so-called function index is to support the use of function expressions when creating an index. Like what

It is also convenient to improve our query efficiency through function indexing. For specific use, you can view the following link

demo link