mysql 数学表达式_MySQL虚拟GENERATED COLUMNS如何与数学表达式一起使用?

可以借助一个示例进行说明,在该示例中,我们在名为“ triangle”的表中创建虚拟生成的列。众所周知,可以在使用或不使用关键字'virtual'的情况下生成虚拟生成的列。

示例mysql> Create table triangle(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB)));

mysql> Describe Triangle;

+-------+--------+------+-----+---------+-------------------+

| Field | Type   | Null | Key | Default | Extra             |

+-------+--------+------+-----+---------+-------------------+

| SideA | double | YES  |     | NULL    |                   |

| SideB | double | YES  |     | NULL    |                   |

| SideC | double | YES  |     | NULL    | VIRTUAL GENERATED |

+-------+--------+------+-----+---------+-------------------+

3 rows in set (0.00 sec)

上面的描述表明,列SideC是虚拟生成的列。mysql> INSERT INTO triangle(SideA, SideB) Values(1,1),(3,4),(6,8);

Records: 3 Duplicates: 0 Warnings: 0

mysql> Select * from triangle;

+-------+-------+--------------------+

| SideA | SideB | SideC              |

+-------+-------+--------------------+

| 1     | 1     | 1.4142135623730951 |

| 3     | 4     | 5.291502622129181  |

| 6     | 8     | 10.583005244258363 |

+-------+-------+--------------------+

3 rows in set (0.03 sec)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
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.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值