gogoWebsite

Introduction

MySQL's virtual columns are a new feature that allows you to use virtual columns directly when querying (instead of view). Stored virtual columns can be used as instantiation caches for dynamically computing costly complex conditions. Virtual columns can be defined when creating tables and used like normal columns when querying. Virtual columns are usually used to calculate or summarize data, such as calculating total prices, calculating averages, etc.

Defining MySQL virtual columns (generated-columns) is a new feature added by MySQL 5.7. In terms of name, "generated fields" are not values ​​that are actively inserted. The MySQL documentation explains virtual columns like this: There are two kinds of Generated Columns: virtual (default) and store-generated (non-default). Virtual columns are generated on the fly as part of an INSERT or UPDATE statement, and they are not stored in the table. Stored-generated columns are calculated based on the values ​​of other columns and are stored in the table.

Classification of virtual columns

In MySQL, there are two types of virtual columns: STORED and VIRTUAL, which represent storage columns and virtual columns respectively.

• Stored column (STORED): The value indicating that the virtual column is actually calculated and stored in a table, and can be used directly during querying. The advantage of storing columns is that they can be indexed, which means they can be searched faster, but they also take up more storage space. The values ​​of the stored column are calculated dynamically when data is inserted or updated and stored in the table.

The formula is:

alter table table_name
     modify column_name int as (expression) stored;

AS (expression): Defines the column as a virtual column and specifies how its value is calculated. An expression is an SQL expression that calculates the value of a virtual column.

STORED: Indicates that the value of the virtual column will be calculated and stored in the table.

• Virtual Column (VIRTUAL): Indicates that the value of the virtual column is not stored in the table, but is calculated dynamically during query. The advantage of virtual columns is that they do not take up extra storage space, but they cannot be indexed, so you need to be careful when using virtual columns in large tables, otherwise it may affect query performance. In the above SQL statement, the STORED keyword is used to indicate that the value of the virtual column will be calculated and stored in the table. Therefore, every time the table is queryed, MySQL returns the virtual column value stored in the table, rather than calculating the value of the virtual column when querying.

The formula is:

alter table table_name
     add column_name int as (expression);

AS (expression): Defines the column as a virtual column and specifies how its value is calculated. An expression is an SQL expression that calculates the value of a virtual column.

Virtual column usage example

1. Create database mydb, create user table and insert data

create database mydb;
 use mydb;
 create table t_user
 (
     id bigint auto_increment
         primary key,
     name varchar(255) null,
     age int default 0 null
 );
 INSERT INTO mydb.t_user (name, age) VALUES ('Xiao Ming', 12)
 INSERT INTO mydb.t_user (name, age) VALUES ('Xiaohong', 18)

Insert two pieces of data.

2. Create a virtual storage column

alter table mydb.t_user
    add store_age int as (age + 9) stored;

A virtual storage column of store_age was created, with age as the standard plus 9.

Display effect

id	name	age	store_age
1	Xiao Ming	12	21
2	Xiaohong	18	27

You can see that the data in store_age is added 9 on the basis of age.

3. Create a virtual column

alter table mydb.t_user
    add virtual_age int as (age + 87);

A virtual column of virtual_age was created, with age as the standard plus 87.

Display effect

id	name	age	store_age	virtual_age
1	Xiao Ming	12	21	99
2	Xiaohong	18	27	105

You can see that the data of virtual_age is added 87 on the basis of age.