MYSQL8.0-生成列（Generated Columns）基础

生成列是指值是通过表达式计算而来的列，参与表达式计算的列是已经表中已经定义的列

语法及规则

新建表时创建生成列

举个栗子:

  • 想要获取两个列组合的数据
SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;
  • 但是如果full_name在多个场景中使用，那么每个使用的地方都要调用函数组合，这样会有冗余，如果组合形式调整，那么可以考虑使用MYSQL的生成列
CREATE TABLE t1 (
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
);

创建语法说明

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
  • [GENERATED ALWAYS] 起到一个标识是生成列的作用
  • expr 是生成列的表达式
  • [VIRTUAL | STORED] 表示的是虚拟列是怎么存储的，VIRTUAL标识的是不存储（虚拟列也支持二级索引的），STORED，插入数据和更新数据的时候更新生成列的值，生成列占用存储空间和使用索引

生成列表达式需要遵守的规则

  • 表达式的结果值必须是确定的，如果不确定那就会发生错误，例如像now(),current_user()这种函数
  • 不允许使用存储过程和函数参数
  • 不允许使用变量(系统变量、用户定义变量和存储的程序局部变量)。
  • 子查询语句也是不允许的
  • 可以引用其他生成列，但是被引用的生成列
  • 生成列不能使用AUTO_INCREMENT属性，也不能以含有AUTO_INCREMENT属性的列为基础列
  • 如果生成列的表达式计算的值截断或异常，创建表会报错
  • 手动更新生成列的值，只能更新为DEFAULT

生成列的使用场景有那些

  • 简化和统一查询，如果多个地方需要多个列计算的结果
  • 模拟函数索引
  • 获取JSON中某一属性的值，用以多个地方使用

生成列的修改语法

对于生成列，表修改的语句ADD,MODIFY,CHANGE是允许的

  • 生成列新增语法
ALTER TABLE t1 
      ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
  • 修改列语法类型或表达式语法
ALTER TABLE t1 
      MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;
  • 生成列没有被其他生成列引用的情况下，可以进行重命名或者删除
ALTER TABLE t1 
      CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;

ALTER TABLE t1 DROP COLUMN c3;
  • 生成列要从虚拟变成实体列，需要先删除再新增
- 先删除
ALTER TABLE t1 
      DROP COLUMN c2;

- 再增加
ALTER TABLE t1 
      ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
  • 非生成列可以改成STORED类型的生成列
ALTER TABLE t1 
      MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
  • STORED类型的生成列可以变成非生成列
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 INT;
  • 修改的列是生成列表达式中引用的列，那么修改列的默认值和名称后，生成列数据可能会受到影响

生成列的索引优化

索引也是支持生成列的，生成列的索引使用过程中，如果存在和生成列相同的查询表达式，也会被识别成生成列，使用生成列表达式

生成列的语法

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

查询条件表达式如果跟生成列的表达式相同，也会被优化器推断为生成列，按照生成列优化

例如：gc列为生成列

SELECT * FROM t1 WHERE gc > 9;
SELECT * FROM t1 WHERE f1 + 1 > 9;

那么 f1+1>9这个条件项使用后，执行计划如下

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

重写SQL的信息可以用show warnings\G来获取

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

生成列索引优化有一些注意点

  • 查询表达式要能匹配到生成列，则他们生成的结果类型要一致
  • 优化适用于这些操作符:=、<、<=、>、>=、BETWEEN和IN()。
  • 生成列要包含函数使用，或者列操作。如果只是简单的引用其他列则没办法进行优化
  • 生成列如果是JSON数据中的某个属性值，那么要让获取的结果类型统一，使用JSON_UNION函数处理

以上是对生成列的简单总结，更多可以通过阅读官方文档获取。后续在使用中，我也会逐步补充细节。

posted @ 2020-08-18 14:01  PerfectLi  阅读(181)  评论(0)  编辑  收藏  举报