[MySQL 5.7.6] Generated Column-CSDN博客

updated @2015-08-09, mysql-5.7.8-rc对generated column的改进:

- InnoDB: InnoDB now supports secondary indexes on virtual generated columns. For more information, seeCREATE TABLE and Generated Columns.
- InnoDB: Virtual generated column values no longer occupy space in database rows. With this change, a table rebuild is no longer required when adding or dropping virtual generated columns.
  Virtual generated columns are still represented in InnoDB metadata. TheN_COLS field of INNODB_SYS_TABLESstill counts virtual generated columns, and INNODB_SYS_COLUMNS still includes virtual generated column metadata.
  
  A new INFORMATION_SCHEMA table, INNODB_SYS_VIRTUAL, provides metadata about columns upon which virtual generated columns are based

简单介绍

从MySQL 5.7.6开始支持Generated Column这个特性。所谓generated column，就是在CREATE或ALTER TABLE时，可以通过表达式的方式定义的新的列类型，表达式可以选择虚拟的（在读取时产生）或者物化的（在插入或更新时自动计算并存储到文件中）。

列定义的语法如下：

col_name data_type [GENERATED ALWAYS] AS (expression)
 [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
 [[NOT] NULL] [[PRIMARY] KEY]

AS后面的expression就是你定义的表达式。也不是任何表达式都可以定义，需要满足一定的条件，如下：

允许内建的具有确定性的函数和操作，但不允许类似CURRENT_TIMESTAMP这样的不确定表达式
不允许的表达式：Subqueries, parameters, variables, stored functions, user-defined functions.
可以引用预先已经定义的其他GC列
允许使用STORED GC列来进行分区，但不允许被触发器引用。（使用GC列来进行分区可以绕过分区表对函数分区的限制）
可以增加/删除GC列，但不能将其转换成普通列
当尝试更改一个有GC依赖的列名时会报错。
可以in-place修改VIRTUAL的GC列，但不能IN-PLACE修改STORED的GC列

另外两个关键字VIRTUAL 和STORED定义了列是否存储到实际文件中。默认为VIRTUAL。

举几个简单的例子：

create table t1 (a int, b int,c int as (a+b) , d int as (a+b+c) stored key) engine = innodb;

insert into t1(a,b) values (1,2),(2,3),(3,4);

root@test 04:07:51>select c,d from t1;
+——+—-+
| c | d |
+——+—-+
| 3 | 6 |
| 5 | 10 |
| 7 | 14 |
+——+—-+
3 rows in set (0.00 sec)

root@test 05:25:02>select COLUMN_NAME, EXTRA, GENERATION_EXPRESSION from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = ‘test’ and table_name = ‘t1’\G
*************************** 1. row ***************************
COLUMN_NAME: a
EXTRA:
GENERATION_EXPRESSION:
*************************** 2. row ***************************
COLUMN_NAME: b
EXTRA:
GENERATION_EXPRESSION:
*************************** 3. row ***************************
COLUMN_NAME: c
EXTRA: VIRTUAL GENERATED
GENERATION_EXPRESSION: a+b
*************************** 4. row ***************************
COLUMN_NAME: d
EXTRA: STORED GENERATED
GENERATION_EXPRESSION: a+b+c
4 rows in set (0.01 sec)

root@test 05:25:35>create table t2 (a int , b int ,c timestamp as (CURRENT_TIMESTAMP));
ERROR 3102 (HY000): Expression of generated column ‘c’ contains a disallowed function.

Generated Column可以协助我们实现之前在SQL中的复杂的计算逻辑，简化SQL。而物化存储的Genrated Column相当于帮助我们将需要复杂计算得到的结果存储起来，无需每次重复计算，从而减少CPU开销。你甚至还可以对generated column进行索引（Tips：目前5.7.6版本的stored的generated column如果定义为索引的话，会自动转换为Primary key，已有人report到buglist了）

代码实现

主要包括：

1.语法支持，主要修改sql_yacc.yy文件

2.检查合法性：Create_field::init、mysql_prepare_create_table

3.对于VIRTUAL GC列，读取数据时需要更新GC列值(handler::ha_rnd_next –> update_generated_read_fields)

4.对于STORED GC列，写入或更新数据时需要生成GC列的值(Sql_cmd_insert::mysql_insert –)

INSERT:Sql_cmd_insert::mysql_insert –> fill_record_n_invoke_before_triggers –> fill_record –> update_generated_write_fields

UPDATE:Sql_cmd_update::try_single_table_update –> mysql_update –> fill_record_n_invoke_before_triggers –> fill_record –> update_generated_write_fields

5.show create table需要显示新的表定义方式：store_create_info

6.增加了一种新的语法类型PARSE_GCOL_EXPR（PARSE_GCOL_KEYWORD，由MySQL Server使用）来辅助将从frm中读取的GC列表达式转换成Item，函数：open_table_from_share –> unpack_gcol_info_from_frm

7.打开frm文件时需要提取存储的表达式：open_binary_frm

8.引擎层基本没啥变化，因为VIRTUAL的GC列计算都在server层，STORED GC列则当作真正的列进行存储。但是依然会为VIRTUAL的GC列在引擎层预留列的位置，只是不写入任何数据。由于VIRTUAL GC列的field->store_in_db标识为FALSE， InnoDB在比较列修改时，可以根据标识直接忽略掉：calc_row_difference