gogoWebsite

1. Introduction to MySQL virtual columns

1.1 Definition and Function

MySQL virtual column is a special column, which is a new feature introduced by mysql-5.7 version. It does not store data, but generates data dynamically during query. The value of such a column is usually calculated by some expression from the values ​​of other columns. The main function of virtual columns is to improve query efficiency and convenience of data processing. It allows us to query and manage data more efficiently without increasing actual storage overhead.

1.2 The difference between virtual columns and ordinary columns

There are several main differences between virtual columns and ordinary columns in use:

• Storage method: The value of a normal column will be actually stored in the database when inserted or updated, while the value of the virtual column will not be stored, but will be dynamically calculated and generated during query.

• Update method: The value of a normal column can be modified directly through the UPDATE statement, while the value of a virtual column cannot be modified directly. Its value is determined by the expression that defines it.

• Indexing application: Virtual columns can be indexed, which makes it improve query performance in some cases. Although the value of the virtual column is only generated during query, if an index is created for the virtual column, the indexed value will be stored, thereby improving query efficiency.

Although virtual columns are different from normal columns in some ways, in SQL queries we can use virtual columns like normal columns.

2. Types of MySQL virtual columns

In MySQL, virtual columns are mainly divided into two types: generate columns and store columns.

2.1 Generate columns

Generating a column is a special virtual column whose value is generated by the values ​​of other columns through an expression. The value of the generated column is not actually stored, but is generated dynamically when querying. The generated column can be any MySQL legal expression based on one or more columns.

The syntax is as follows

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name));

2.2 Storage columns

Stored columns are a special form of generating columns. Unlike generating columns, the value of the stored column is calculated and actually stored in the database when data is inserted or updated. This means that the value of the stored column does not need to be calculated dynamically at query time.

Although the storage column requires additional storage space, it can improve query speed because its value has been calculated and stored at the time of query. Storage columns are especially suitable for scenarios where computational cost is high but frequent querying.

It should be noted that although the value of the stored column is stored in the database, its value cannot be modified directly, and its value is still determined by the expression that defines it.

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED;

3. How to create and use MySQL virtual columns

3.1 Create a table with virtual columns

When creating a new table, you can include one or more virtual columns in the table definition. Here is an example:

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

In MySQL, GENERATED ALWAYS is a keyword used to define the generated columns (including virtual columns and storage columns). This keyword indicates MySQL, the value of this column is not inserted or updated directly by the user, but is automatically generated by an expression. This expression can refer to the values ​​of other columns in the table.

3.2 Update the value of the virtual column

The value of a virtual column is calculated by an expression that can refer to the values ​​of other columns in the table. Therefore, you cannot directly update the value of the virtual column. On the contrary, when you update the value of the column on which the virtual column depends, the value of the virtual column is automatically updated.

For example, suppose you updated an employee'sfirst_name：

UPDATE employees SET first_name = 'John' WHERE last_name = 'Doe';

In this example,full_nameThe value of the column is automatically updated because it isfirst_nameandlast_nameThe values ​​of .

3.3 Query the value of the virtual column

The value of a virtual column can be queried like the value of a normal column. For example, the following query returns the full names of all employees:

SELECT full_name FROM employees;

In this example,full_nameThe values ​​of the column are calculated at query time, not stored in the table. This means that every time you queryfull_nameMySQL will recalculate the value when the column value is used, but if you use the stored virtual column, it will not be calculated in real time.

4. Use scenarios for MySQL virtual columns

Virtual columns have many practical application scenarios in MySQL, including optimizing queries, managing data redundancy, and performing data conversion and computing. Here are some specific usage examples:

4.1 Joint index optimization

Virtual columns can be used to create joint indexes to optimize query performance. For example, if you often need tofirst_nameandlast_nameConduct a joint query on it, you can create a virtual columnfull_name, and create an index on this column:

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    INDEX (full_name)
);

In this example,full_nameThe index of the column can be used to optimize the query, for exampleSELECT * FROM employees WHERE full_name = 'John Doe';。

4.2 Data redundancy management

Virtual columns can be used to reduce data redundancy. For example, if one column in your table is calculated from the values ​​of other columns, you can use a virtual column instead of storing the calculation result. This way, you can save storage space and ensure consistency of your data.

For example, suppose you have aordersTable, this table hasquantityandpriceTwo columns, you can create a virtual columntotal_price, its value isquantityandpriceThe product of:

CREATE TABLE orders (
    quantity INT,
    price DECIMAL(10, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) VIRTUAL
);

In this example,total_priceThe column's value will be automatically updated whenquantityorpriceWhen the value of varied.

4.3 Data conversion and calculation

Virtual columns can be used for data conversion and calculation. For example, you can create a virtual column to store the year portion of the date, or calculate the proportion of two columns.

For example, suppose you have asalesTable, this table hastotal_salesandtotal_costsTwo columns, you can create a virtual columnprofit_margin, its value istotal_salesandtotal_costsThe ratio:

CREATE TABLE sales (
    total_sales DECIMAL(10, 2),
    total_costs DECIMAL(10, 2),
    profit_margin DECIMAL(10, 2) GENERATED ALWAYS AS (total_sales / total_costs) VIRTUAL
);

In this example,profit_marginThe column's value will be automatically updated whentotal_salesortotal_costsWhen the value of varied.

5. Limitations and precautions for MySQL virtual columns

While virtual columns are very useful in many cases, they also have some limitations and considerations. Here are some of the main restrictions and considerations:

5.1 Data type limitations

The data type of the virtual column must be derived from the result type that generates the list expression. For example, if your expression is the product of two integer columns, the data type of the virtual column should be an integer or any other type that can contain the product result.

5.2 Update and Delete Restrictions

The value of the virtual column is calculated by the expression and cannot be updated directly. If you try to update the value of the virtual column directly, MySQL will return an error. Similarly, you cannot delete a virtual column unless you delete all other objects that depend on that column at the same time, such as indexes and triggers.

5.3 Other precautions

• The expression of a virtual column cannot refer to the values ​​of other virtual columns.
• The expression of a virtual column cannot contain uncertain elements, such as the current time or random number.
• Virtual columns cannot have default values.
• The value of a virtual column is calculated at query time, so if the expression of the virtual column is very complex, or the amount of data in the table is very large, querying the value of the virtual column may be slower than querying the stored column values.
• Although virtual columns do not occupy storage space, indexes created on virtual columns take up storage space.

These limitations and considerations need to be taken into account when using virtual columns to ensure that your database design and query work correctly and effectively.

6. Practical combat: Use MySQL virtual columns to solve practical problems

6.1 Problem Description

Suppose we are developing an e-commerce website, we have aproductstable, this table contains the productpriceanddiscountinformation. Now, we want to be able to quickly query the discounted price, but we don't want to store a discounted price field for each product in the table, because this will increase data redundancy, and whenpriceordiscountWhen changes occur, the discounted price needs to be manually updated.

6.2 Solution Design

We can use MySQL's virtual columns to solve this problem. We canproductsAdd a virtual column to the tablediscounted_price, the value of this column ispriceanddiscountproduct of . Since the value of the virtual column is calculated dynamically,priceordiscountWhen changes occur,discounted_priceThe value of .

6.3 Implementation steps

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount/100)) VIRTUAL;

In this SQL statement, we added a name calleddiscounted_priceThe value of the virtual column ispriceanddiscountproduct of . Note, let's assumediscountis a percentage value, such as 15 means a 15% discount.

6.4 Results and Effect Analysis

Now, we can query directlydiscounted_priceColumns to get the discounted price without calculating in the application. This makes the query simpler and more intuitive. At the same time,discounted_priceThe values ​​of the column are calculated dynamically, so whenpriceordiscountWhen changes occur, we do not need to manually update the discounted price, reducing data redundancy and maintenance work.

It should be noted that although virtual columns do not occupy storage space, if the expression of the virtual column is very complex, or the amount of data in the table is very large, querying the value of the virtual column may be slower than querying the value of the stored column. Therefore, when using virtual columns, trade-offs need to be made based on actual conditions.