MENU

MySQL 5.7 虚拟列

2021 年 12 月 09 日 • 阅读: 650 • 编码,MySQL

MySQL 5.7 开始支持了一个新特性 虚拟列(Generated columns ,又称生成列/计算列),该列的值是通过在列定义时包含的一个计算表达式得到的。

ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 [GENERATED ALWAYS] as (表达式) [VIRTUAL | STORED];

MySQL 在处理 虚拟列存储问题的时候有两种方式:

  • VIRTUAL(默认):不存储列值,在读取表的时候自动计算并返回,不消耗任何存储,这种存储方式仅 InnoDB 支持设置索引。
  • STORED:在插入或更新时计算存储列值,存储的虚拟列需要存储空间,并且 MyISAM 也可以设置索引。

虚拟列注意

  • 如果定义的虚拟列类型和表达式返回的不同,则会进行隐式的强制转换
  • 如果对虚拟列进行显示的写操作,唯一允许的值是 DEFAULT
  • VIRTUAL 每次读表都会计算,但可以使用索引来存储计算值,如果刚好使用到覆盖索引,则不会计算。

虚拟列允许

  • 允许在一个表中混合使用虚拟列和存储列
  • 允许按虚拟列分区
  • 允许使用为本、确定性的内置函数(每次输出的值一样,反向例子:NOW())和运算符
  • 虚拟列定义可以引用其他虚拟列,但只能引用表中定义较早出现的列。
  • 虚拟列允许修改表达式,但不允许修改存储方式(只能通过删除重新创建来修改)。
  • 可以将 STORED 虚拟列与普通列可以互相转化,但 VIRTUAL 不行。

虚拟列不允许

  • 不允许存储函数可加载函数
  • 不允许存储过程或函数参数
  • 不允许使用变量(系统变量、用户定义变量、局部变量)
  • 不允许子查询
  • 虚拟列定义不允许使用自增 (AUTO_INCREMENT),也不允许使用自增基列
  • 外键约束不能引用虚拟列
  • 触发器不能使用或引用虚拟列
  • 被虚拟列引用的基列不允许使用外键约束、AS 或引用操作

虚拟列用途

  • 虚拟列可以简化和统一查询,将复杂条件定义为生成的列,可以在查询时直接使用虚拟列(代替视图)
  • 存储虚拟列可以用作实例化缓存,以用于动态计算成本高昂的复杂条件
  • 虚拟列可以模拟功能索引,并且可以使用索引,这对与无法直接使用索引的列(JSON列)非常有用。

如果虚拟列用作索引,会有一个缺点值会存储两次。一次用作虚拟列的值,一次用作索引中的值。

虚拟列部分参考:

CC版权: 本篇博文采用《CC 协议》,转载必须注明作者和本文链接
最后编辑于: 2021 年 12 月 30 日