mysql insert时计算生成虚拟列数据[GENERATED ALWAYS AS () VIRTUAL]

 在一些数据入库时需要生成虚拟列，可以通过  GENERATED ALWAYS AS () VIRTUAL 处理

示例：

1、表test001，存储时生成虚拟列avg_speed=total_speed/flow

CREATE TABLE `test001` (
  `stat_hour` varchar(20) NOT NULL,
  `cross_id` varchar(100) NOT NULL,
  `road_id` varchar(100) NOT NULL,
  `flow` int(11) DEFAULT NULL,
  `total_speed` double DEFAULT NULL,
  `avg_speed` double GENERATED ALWAYS AS ((`total_speed` / `flow`)) VIRTUAL,
  PRIMARY KEY (`stat_hour`,`cross_id`,`road_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2、插入一条数据

INSERT  INTO test001 (stat_hour,cross_id,road_id,flow,total_speed) VALUES("124","125","126",1123,23543)

3、插入结果

4、还可用于计算时间日期等

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `buy_time` int(10) unsigned NOT NULL COMMENT '时间',
  `buy_day` int(9) GENERATED ALWAYS AS (date_format(from_unixtime(`buy_time`),'%Y%m%d')) VIRTUAL,
  `buy_month` int(9) GENERATED ALWAYS AS (date_format(from_unixtime(`buy_time`),'%Y%m')) VIRTUAL,
  `buy_week` int(9) GENERATED ALWAYS AS (yearweek(date_format(from_unixtime(`buy_time`),'%Y%m%d'),1)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`buy_day`),
  KEY `idx2` (`buy_month`),
  KEY `idx3` (`buy_week`)
) ENGINE=InnoDB AUTO_INCREMENT=228214 DEFAULT CHARSET=utf8 COMMENT='用户科目表';

5、创建虚拟列

alter table 表名 add column 虚拟列名 varchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.虚拟列名')) VIRTUAL;

# yearweek语法
alter table user add column buy_day int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y-%m-%d')) VIRTUAL,add column buy_month int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y-%m')) VIRTUAL,add column buy_week int(9) GENERATED ALWAYS AS (YEARWEEK(FROM_UNIXTIME(buy_time,'%Y-%m-%d'))) VIRTUAL;
alter table crm_course add index idx_bday(buy_day),add index idx_bmonth(buy_month),add index idx_bweek(buy_week);

# 
alter table user modify column buy_day int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y%m%d')) VIRTUAL,modify column buy_month int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y%m')) VIRTUAL,modify column buy_week int(9) GENERATED ALWAYS AS (YEARWEEK(FROM_UNIXTIME(buy_time,'%Y%m%d'), 1)) VIRTUAL;

6、修改虚拟列

alter table 表名 modify column 虚拟列名 varchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.虚拟列名')) VIRTUAL;

参考：mysql虚拟列处理日期_generated always as (date_format(`create_time`'%y%-CSDN博客