mysql generated column
时间: 2023-07-11 07:08:52 浏览: 63
MySQL generated columns are virtual columns that are not stored physically on the disk but are computed based on an expression or formula. They are also known as computed columns or virtual columns.
Generated columns were introduced in MySQL version 5.7 and they provide a way to create a column whose values are computed from an expression. The expression can involve one or more other columns in the same table, constants, or functions.
To create a generated column, you need to specify the column name, data type, and the expression that computes the values for that column. Here is an example:
```
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary INT,
tax_rate DECIMAL(4,2),
net_salary DECIMAL(10,2) GENERATED ALWAYS AS (salary - (salary * tax_rate)) STORED
);
```
In this example, the `net_salary` column is a generated column whose value is computed from the `salary` and `tax_rate` columns using the expression `(salary - (salary * tax_rate))`.
Generated columns can be either `STORED` or `VIRTUAL`. A `STORED` generated column is computed when a row is inserted or updated and its value is stored on the disk like any other column. A `VIRTUAL` generated column is computed dynamically when it is accessed and its value is not stored on the disk.
Generated columns can provide significant performance benefits in certain scenarios because they can avoid the need to compute values in application code or in queries. However, they can also increase the storage requirements for a table, so it's important to use them judiciously.