mysql generated column实际例子和解析
时间: 2023-12-13 17:02:54 浏览: 27
MySQL Generated column是MySQL 5.7版本中引入的新功能,允许用户使用一个表达式来创建一个虚拟列,这个虚拟列的值是根据其他列的值计算而来的。
下面是一个实际例子和解析:
假设我们有一个名为`students`的表,其中包含了学生的姓名、年龄、身高和体重等信息。我们想要在表中加入一个新列,用于计算学生的BMI值。
首先,我们需要创建一个生成列,如下所示:
```
ALTER TABLE students ADD COLUMN bmi FLOAT GENERATED ALWAYS AS (weight / (height * height)) STORED;
```
在这个例子中,我们创建了一个名为`bmi`的生成列,使用了一个表达式`(weight / (height * height))`来计算学生的BMI值。该表达式使用了`weight`和`height`两个已有的列,因此我们需要在表达式中使用这些列名。
在这里,`GENERATED ALWAYS`关键字指定了生成列的类型,并且告诉MySQL每次查询时都需要重新计算这个列的值。`STORED`关键字则指定了这个生成列的值应该被存储在表中,而不是在查询时计算。
当我们向`students`表中插入一条新记录时,MySQL会自动计算出这个学生的BMI值,并将其存储在`bmi`列中。
使用生成列的好处是,它能够简化我们的查询语句,避免了繁琐的重复计算。此外,生成列还可以提高查询性能,因为MySQL会在查询时缓存这些值,避免重复计算。
相关问题
mysql generated column
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.
GENERATED ALWAYS AS 和default区别
GENERATED ALWAYS AS 和default都可以用于设置列的默认值,但是它们有两个主要的区别:
1. 计算方式不同
default是在插入数据时使用的默认值,也可以在定义表时设置默认值。这个值是静态的,不会随着其他列的值而改变。
GENERATED ALWAYS AS是在查询时计算的,它的值是动态的,会随着其他列的值而改变。它可以使用表达式来计算,默认情况下,它是一个虚拟列,不会占用存储空间。
2. 存储方式不同
default值是在表中存储的,当您插入一条记录时,这个默认值将作为列的实际值存储在表中。
GENERATED ALWAYS AS是虚拟列,不会在表中存储。它是在查询时计算的,因此不需要存储。
总的来说,GENERATED ALWAYS AS提供了更灵活的默认值选项,可以使用表达式来计算,而且不会占用存储空间。但是,如果您需要一个静态的默认值,default可能更适合您的需求。