浅析MySQL中的计算列（GeneratedColumn列）与计算字段的
介绍与应⽤-如何让。。。
⼀、计算列
  MySQL 的 Generated Column ⼜称为虚拟列或计算列。Generated Column列的值是在列定义时包含了⼀个计算表达式计算得到的。
1、定义Generated column列的语法如下：
列名类型
[GENERATED ALWAYS]AS (expr) [VIRTUAL | STORED]
[NOT NULL | NULL]
[UNIQUE [KEY]]
[[PRIMARY]KEY]
[COMMENT 'string']
  （1）AS（expr）⽤于⽣成计算列值的表达式。
  （2）VIRTUAL或STORED关键字表⽰是否存储计算列的值：
  VIRTUAL：列值不存储，虚拟列不占⽤存储空间，默认设置为VIRTUAL。
  STORED：在添加或更新⾏时计算并存储列值。存储列需要存储空间，并且可以创建索引。
2、Generated column 表达式必须遵循以下规则。如果表达式包含不允许的定义⽅式，则会发⽣错误。
（1）允许使⽤⽂本、内置函数和运算符，但不能使⽤返回值不确定的函数，⽐如NOW()。
（2）不允许使⽤存储函数和⽤户定义函数。
（3）不允许使⽤存储过程和函数参数。
（4）不允许使⽤变量（系统变量、⽤户定义变量和存储程序的局部变量）。
（5）不允许⼦查询。
（6）计算列在定义时可以引⽤其他的计算列，但只能引⽤表定义中较早出现的列。
（7）可以在计算列上创建索引，但不能在VIRTUAL类型的计算列上创建聚集索引。
3、计算列举例
（1）表的定义
mysql>create table sales(
-> goods_id int primary key,
-> goods_name char(20),
-> unit_price int,
-> quantity int,
-> amount int generated always as (unit_price*quantity));
Query OK, 0 rows affected (0.02 sec)
（2）插⼊数据：amount ⾃动为 8
mysql>insert into sales(goods_id,goods_name,unit_price,quantity) values(100101,'Apple',2,4);
Query OK, 1 row affected (0.00 sec)
mysql>select*from sales;
+----------+------------+------------+----------+--------+
| goods_id | goods_name | unit_price | quantity | amount |
+----------+------------+------------+----------+--------+
|100101| Apple      |2|4|8|
+----------+------------+------------+----------+--------+
1 row in set (0.00 sec)
（3）查看创建表的语句：可见，计算列的默认类型为VIRTUAL。
mysql> show create table sales\G
***************************1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`goods_id` int(11) NOT NULL,
`goods_name` char(20) DEFAULT NULL,
`unit_price` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`amount` int(11) GENERATED ALWAYS AS ((`unit_price` * `quantity`)) VIRTUAL,
PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
4、计算列区别于需要我们⼿动或者程序给予赋值的列，它的值来源于该表中其它列的计算值。
  ⽐如，⼀个表中包含有数量列Number与单价列Price，我们就可以创建计算列⾦额Amount来表⽰数量*单价的结果值，创建Amount列后，在程序中需要使⽤计算⾦额这个值时，就不⽤取出Number列与Price列的值后相乘，⽽是直接取Amount列的值就可以了。 
  那么这个计算列要如何建⽴呢？先看通过sql的⽅法创建：
create table table1(
  number decimal(18,4),
  price money,
  Amount as number*price --这⾥就是计算列
mysql中select)
  计算列是不需要我们指定数据类型与是否允许为null等信息的，SqlServer会根据情况⾃动赋予数据类型。在microsoft sql server management studio建创计算列更是简单。如下图所⽰，只要在列属性中的"计算所得的列规范"-"公式"中填写计算列的公式就可以了。
  在上⾯的图⽚中我们也看到有"是持久的"这个选项，这个选项有什么⽤呢？
  计算列如果没有特殊的设定，它将会是⼀个虚拟列，也就是这个列实际上是不存在的，只是每次要取这列的值时，sql会按照计算列的公式计算⼀次，再把结果返回给我们。
  这样就会存在⼀些问题，⽐如，每次计算都会消耗⼀定的时间，⽽且也不能在这个列上创建索引。
  那么能不能把计算列的结果存起来，每次取数据的时候直接把结果返回给我们，⽽不⽤每次去计算。创建计算列时把"是持久的"这个选项勾起来，就能达到我们的⽬的了，这时候，计算列就是⼀个实实在在的列，也可以在该列上创建索引了。
  如果要查看所有已经存在计算列以及该计算列是否为"是持久的"，可以利⽤sysputed_columns视图
5、注意：
（1）计算列如果没有设置为"是持久的"，那么它是不可以⽤来做check,foreign key或not null约束。当然，如果我们在microsoft sql server management studio为计算列设置了check等约束了，sql server会⾃动将该列设置为"是持久的"。
（2）计算列不可以再次⽤来作为中⼀个计算列的⼀部分。
（3）在触发器，不可以对计算列进⾏update判断，否则会报如下错误：列不能在IF UPDATE⼦句中使⽤，因为它是计算列。
⼆、SQL Server 中如何让数据库中某⼀个字段随时间⾃动更新？
  今天在sql server论坛看到⼀个帖⼦：如何让数据库中某⼀个字段随时间⾃动更新？
  那么如何来实现呢？可以⽤触发器，那么先要写个触发器，但是可能会影响性能。想了想，其实⽤sql server提供的计算列，就可以轻松实现这个需求。
  例⼦如下：员⼯表，有字段：⼈员id，姓名，⼈员编码，⼈员⼊职时间，现在希望要增加⼀个字段显⽰⼯龄，就是在公司⼯作的时间，如 1.5年。
CREATE TABLE emp
(
emp_id INT PRIMARY KEY ,
emp_name NVARCHAR(10) not null,
emp_code VARCHAR(20) not null,
hire_date DATE not null
);
  实现⽅法就是新增⼀个计算列：
alter table emp
add employment_time as cast(datediff(month,hire_date,GETDATE())*1.0/12as numeric(8,1));
三、计算字段的应⽤
1、计算字段
  计算字段存储在数据库表⾥⾯的数据⼀般会出现不是应⽤程序所需要的的数据格式，下⾯举3个简单的例⼦。
（1）如果想要在⼀个字段中既要显⽰公司的名，⼜要显⽰，但是这两个信息⼀般会包含在不同的列中。
（2）⽐如物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格，有时候为了打印发票，所以需要物品的总价格。
（3）⽐如需要根据表数据进⾏总数，平均数和最⼤值和最⼩值或者其他的计算。
  在上述的三个例⼦中，存储在表中的数据都不是应⽤程序所需要的，这个时候我们就需要直接从数据库中检索出转换然后计算或者格式化数据，⽽不是检索出数据，然后在客户机应⽤程序中重新格式化数据了。
  这个时候就要发挥计算字段的作⽤了，计算字段不是存储在数据库表⾥⾯的，⽽是在运⾏SELECT语句内创建的，需要注意的是MySQL 是可以分清哪些是数据库字段，哪些是计算字段的。
2、拼接字段
  为了更好的说明如何使⽤计算字段，下⾯会顺便介绍拼接字段，然后连个⼀起举例展⽰。
（1）需求：vendors 表包含供应商名和位置信息。假如要⽣成⼀个供应商报表，需要在供应商的名字中按照 name(location) 这样的格式列出供应商的位置。此报表需要单个值，⽽表中数据存储在两个列 vend_name 和 vend_ country 中。此外，需要⽤括号将 vend_country 括起来，这些东西都没有明确存储在数据库表中。我们来看看怎样编写返回供应商名和位置的 SELECT语句。
（2）解决办法是：把两个列拼接起来。在MySQL的SELECT语句中，可使⽤ Concat() 函数来拼接两个列。Concat()拼接串，即把多个串连接起来形成⼀个较长的串。看下⾯的sql语句和执⾏结果。
  分析⼀下上⾯的 sql 语句的，⾸先 Concat() 需要⼀个或多个指定的串，各个串之间⽤逗号分隔。上⾯的SELECT语句连接以下4个元素：分别是，存储在vend_name列中的名字；包含⼀个空格和⼀个左圆括号的串；存储在vend_country列中的国家；包含⼀个右圆括号的串。
  从上述输出中可以看到，SELECT语句返回包含上述4个元素的单个列（计算字段）。这个就是拼接字段和计算字段的使⽤⽅法。
3、执⾏算术计算
（1）需求：orders 表包含收到的所有订单，orderitems 表包含每个订单的各项物品，下⾯要查出订单号为20005的所有物品。
  先查询订单号为20005的所有物品
  item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）
  从输出的结果我们可以看出，expanded_price 列为⼀个计算字段展⽰，此列的计算值为 quantity*item_price，客户机可以直接使⽤这个计算出来的结果，⽽不是查询出数量和价格然后在客户机⾥⾯重新定义重新计算。
  内容捯饬当然客户机是完全可以进⾏这些计算和格式化数据的，但是⼀般来说，在数据库服务器上⾯完成这些操作要⽐在客户机中完成要快得多，因为DBMS是设计快速有效地完成这种处理的。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。