语法
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
使用场景
- 简化统一查询
- 可以用作复杂条件下的物化缓存
- 模拟函数索引
示例
- 对表
user3的createdate添加虚拟列v_year,以%Y-%m-%d格式存储数据
mysql> alter table user3 add column v_year date generated always as (date_format(createdate, '%Y-%m-d%'));
- 对虚拟列添加索引
mysql> alter table user3 add index idx_v_year(v_year);
- 使用虚拟列做为查询条件查询数据
mysql> explain SELECT * from user3 where v_year > '2023-10-01';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user3 | NULL | range | idx_v_year | idx_v_year | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
