MySQL中的计算列（Generated Column列）-如何让数据库中某个字段随时间自动更新

一、计算列

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关键字表示是否存储计算列的值：
（3）VIRTUAL：列值不存储，虚拟列不占用存储空间，默认设置为VIRTUAL；
（4）STORED：在添加或更新行时计算并存储列值。存储列需要存储空间，并且可以创建索引。

2、Generated column 表达式必须遵循以下规则。如果表达式包含不允许的定义方式，则会发生错误。1、定义Generated column列的语法如下：

（1）允许使用文本、内置函数和运算符，但不能使用返回值不确定的函数，比如NOW()。
（2）不允许使用存储函数和用户定义函数。
（3）不允许使用存储过程和函数参数。
（4）不允许使用变量（系统变量、用户定义变量和存储程序的局部变量）。
（5）不允许子查询。
（6）计算列在定义时可以引用其他的计算列，但只能引用表定义中较早出现的列。
（7）可以在计算列上创建索引，但不能在VIRTUAL类型的计算列上创建聚集索引。

3、计算列举例

（1）表的定义

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));

（2）插入数据：amount 自动为 8

insert into sales(goods_id,goods_name,unit_price,quantity) values(100101,'Apple',2,4);

查询结果：


（3）查看创建表的语句：可见，计算列的默认类型为VIRTUAL。

show create table sales;

CREATE TABLE `sales` (
  `goods_id` int NOT NULL,
  `goods_name` char(20) DEFAULT NULL,
  `unit_price` int DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `amount` int GENERATED ALWAYS AS ((`unit_price` * `quantity`)) VIRTUAL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci



4、计算列区别于需要我们手动或者程序给予赋值的列，它的值来源于该表中其它列的计算值。
比如，一个表中包含有数量列Number与单价列Price,我们就可以创建计算列金额Amount来表示数量*单价的结果值，创建Amount列后，在程序中需要使用计算金额这个值时，就不用取出Number列与Price列的值后相乘，而是直接取Amount列的值就可以了。　　
那么这个计算列要如何建立呢？先看通过sql的方法创建：

create table table1(
　　number decimal(18,4),
　　price money,
　　Amount as number*price --这里就是计算列
)

计算列是不需要我们指定数据类型与是否允许为null等信息的,SqlServer会根据情况自动赋予数据类型。在microsoft sql server management studio建创计算列更是简单。只要在列属性中的"计算所得的列规范"-"公式"中填写计算列的公式就可以了。计算列如果没有特殊的设定，它将会是一个虚拟列，也就是这个列实际上是不存在的，只是每次要取这列的值时，sql会按照计算列的公式计算一次，再把结果返回给我们。这样就会存在一些问题，比如，每次计算都会消耗一定的时间，而且也不能在这个列上创建索引。那么能不能把计算列的结果存起来，每次取数据的时候直接把结果返回给我们，而不用每次去计算。
创建计算列时把"是持久的"这个选项勾起来，就能达到我们的目的了，这时候，计算列就是一个实实在在的列，也可以在该列上创建索引了。如果要查看所有已经存在计算列以及该计算列是否为"是持久的"，可以利用sys.computed_columns视图。
5、注意：
（1）计算列如果没有设置为"是持久的"，那么它是不可以用来做check,foreign key或not null约束。当然，如果我们在microsoft sql server management studio为计算列设置了check等约束了，sql server会自动将该列设置为"是持久的"。
（2）计算列不可以再次用来作为中一个计算列的一部分。
（3）在触发器，不可以对计算列进行update判断，否则会报如下错误：列不能在IF UPDATE子句中使用，因为它是计算列。

例子：

题解：

create table 院系
(编号 char(12) primary key
);
    
    drop table if exists 学生;
    create table 学生
(学号 char(12) primary key,
院系编号 char(12) generated always as (substring(学号, 3, 2)),
姓名 char(8) not null,
性别 char(4) check( 性别 in ('男','女')),
学籍状态 char(8) check( 学籍状态 in ('正常','留级','休学','退学'))
);

二、如何让数据库中某一个字段随时间自动更新？

①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/12 as numeric(8,1));

②MYSQL

• 首先，需要在MySQL中创建一个存储年龄的字段。在创建表时，我们可以使用一个整数类型的字段来存储年龄。例如：

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT(11) NOT NULL,
  birthday DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   -- 创建时间为系统时间
  PRIMARY KEY (id)
);

  在这个示例中，我们创建了users表，并使用age字段存储年龄。

• 有了age和birthday字段，我们就可以计算年龄了。我们可以使用MySQL内置的TIMESTAMPDIFF函数来计算两个日期之间的差值（以秒为单位），然后将结果转换为年龄。例如：

SELECT
  id,
  name,
  FLOOR(TIMESTAMPDIFF(SECOND, birthday, NOW())/(60*60*24*365)) AS age
FROM
  users;

• 如果我们希望年龄能够随着时间的推移而增加，我们需要定期更新age字段。我们可以使用MySQL的事件调度器来实现这一功能。例如：

CREATE EVENT update_age
ON SCHEDULE EVERY 1 DAY
DO
  UPDATE users SET age = FLOOR(TIMESTAMPDIFF(SECOND, birthday, NOW())/(60*60*24*365));

在这个示例中，我们创建了一个名为update_age的事件，它每天定时执行一次。事件执行的动作是更新age字段，使其等于当前日期与birthday之间的年数。

插入一条错误测试数据

查询

手动更新后,（也可以等待系统自动更新）