专栏首页腾讯云数据库专家服务MySQL 5.7 新特性:Generated Column
原创

MySQL 5.7 新特性:Generated Column

前言

不仅是 MySQL 8.0,5.7 版本其实也推出不过少新功能,Generated Column 在 5.7 就算是一个比较实用的功能,能以巧妙的方式应对一些比较棘手而紧急的需求。

原理简介

顾名思义,Generated Column 的效果就是一个依据某些规则“生成”出来的列,并非表中的常规列。“生成”的列的有两种类型:VIRTUAL 和 STORED。

  • VIRTUAL:列中的值并没有实际存储在磁盘中,而是在需要的时候实时计算,计算过程发生在触发 trigger 的 BEFORE 关键字之前。这是 MySQL 默认使用的类型。
  • STORED:列中的值在发生变化的时候都会实时计算,并存储在磁盘之中。

Generated Column 的使用方式与技巧,主要在于进行计算时依据的“某些规则”。这些规则目前有如下的需要注意的地方:

  • 支持 MySQL 内建的各种函数,包括 MIN,MAX,SUBSTRING 等函数。
    • 不支持 CONNECTION_ID(),NOW() 这一类函数,因为这些函数的返回值会随时变化。
  • 不支持存储过程和用户自定义的函数。
  • 不支持子查询。
  • 可以针对普通列的数据进行计算,也可以使用其他 Generated Column 的数据进行计算,生成新的列。
  • 不可以基于自增列来生成新的列。
  • SQL_MODE 的设置可能会影响计算的结果,使用时需要注意。
  • CREATE TABLE ... LIKE ... 生成的新表会拷贝 Generated Column 的定义。
  • 可以使用 Generated Column 作为分区表的分区列。
  • 可以使用 Generated Column 作为外键。
  • 可以使用 Generated Column 创建索引。

测试一下

实际动手试一下比看文字要直观很多,这里使用腾讯云数据库 MySQL 作为测试环境,来试试这个特性。

测试用的 SQL 语句:

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `sname` varchar(16) NOT NULL,
  `cname` varchar(8) DEFAULT NULL,
  `math` int(11) NOT NULL,
  `eng` int(11) DEFAULT NULL,
  `his` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into stu(id,sname,cname,math,eng,his) values(100,'Dave',1,100,80,77);
insert into stu(id,sname,cname,math,eng,his) values(101,'Lily',1,87,91,99);
insert into stu(id,sname,cname,math,eng,his) values(102,'Wolf',3,82,99,89);
insert into stu(id,sname,cname,math,eng,his) values(103,'Tom',2,69,74,75);

到目前为止,这个测试表算是常见的 MySQL 信息表。

这里创建两个 Generated Column,用来计算总分和显示学生的班级归属:

ALTER TABLE stu ADD COLUMN total INT GENERATED ALWAYS AS (math+eng+his);
ALTER TABLE stu ADD COLUMN class_info VARCHAR(32) GENERATED ALWAYS AS (concat(sname,' is in classe ',cname)) stored;

STORED 和 VIRTUAL 的差别在 ALTER 的时候就能看出来了:

效果对比

可以看到 STORED 的时候,是有数据变更的,但是 VIRTUAL 的时候没有,因此在使用 STORED 的时候要注意,大表上的操作可能会持续很久,产生的影响和大表 ALTER 操作基本一致

现在再 insert 几行数据,会发现新添加的行会自动计算出 Generated Column 的内容:

查看结果

PS:这里要注意,insert 如果不带列会报错:Column count doesn't match value count at row 1,SQL 规范的中 insert 带列名是正规的做法,手写 SQL 的时候要留意一下,框架一般会自动带上。

在 Generated Column 上创建索引也是可以的:

索引效果

如果需要利用 Generated Column 来查找数据,那么就可以创建索引,加快查询效率。

总结一下

Generated Column 通过函数等规则生成的列,配合上索引,分区表的分区键等功能,可以完成一些以前比较难处理的问题和需求,在之后的案例挖掘会陆陆续续的介绍。

随着新功能的不断发布,MySQL 数据库也会变得越来越易于管理和维护,整体的性能也会不断提升,在这个时代,数据库也要开始慢慢习惯定期更新版本,使用新功能来更好的支撑开发,运维人员的工作了。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 案例:Limit 分页查询优化

    在各类系统的表格类信息展示的功能中,经常会用到“翻页”这个操作,在页面上每次只展示有限的数据,需要看其他数据的时候则像翻书一样翻到后面的“页”。在 MySQL ...

    wenanwang@DBA
  • MySQL 8.0 新特性:极速“备份恢复工具”,Clone 插件

    备份恢复是 DBA 必备的技能,开源数据库 MySQL 在社区中有不少常用的备份恢复方案,xtrabackup,mypump,mydumper,mysqldum...

    wenanwang@DBA
  • MySQL 8.0 新特性:WriteSet 复制

    MySQL 的主从同步应该是被各个 DBA 熟知的技术了,从 MySQL 3.23.15 开始一直迭代改进到 8.0 版本。经过这么多年的改进,目前 8.0 提...

    wenanwang@DBA
  • 高考前一天,六月六号,加一!

    今天分享的题目来源于 LeetCode 上第 66 号问题:加一。题目难度为 Easy,目前通过率为 39.0% 。

    五分钟学算法
  • Confluence 6 其他 MBeans 和高 CPU 消耗线程

    希望监控 Hibernate 和 Hazelcast(仅针对 Confluence 数据中心)你需要在你的 setenv.sh / setenv.bat 文件中...

    HoneyMoose
  • 欧拉计划problem 15

    题目 网格路径 从一个2×2方阵的左上角出发,只允许向右或向下移动,则恰好有6条通往右下角的路径。

    用户4492257
  • SAP最佳业务实践:FI–应收帐款(157)-1业务概览

    用途 该业务情景主要描述的是在应收账款模块中对销售业务进行会计记账。 优点 应收帐款模块与销售管理完全集成 应收帐款模块中的所有明细账数据会直接更新总账...

    SAP最佳业务实践
  • 利用Python数据处理进行公交车到站时间预测(一)

    id  int  id编号 type  int   41表示站间数据,42中间站进出数据 43始末站进出数据 route_id int  线路ID号,104...

    钱塘小甲子
  • leetcode118 Pascal's Triangle

    用户1665735
  • 【工程材料B】一:材料力学性能概述

    我们可以看到,材料的性能分为材料的使用性能和材料的工艺性能。使用性能是指材料在使用过程中所表现的性能, 包括力学性能、 物理性能、化学性能。工艺性能是指材料在加...

    周旋

扫码关注云+社区

领取腾讯云代金券