MySQL 5.7新特性之generated column

MySQL 5.7引入了generated column,这篇文章简单地介绍了generated column的使用方法和注意事项,为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开:

  1. generated column是什么
  2. virtual column与stored column的区别
  3. 如果我对generated column做一些破坏行为会怎么样
  4. generated column上创建索引
  5. generated column上创建索引与Oracle的函数索引的区别

1. generated column是什么

generated column是MySQL 5.7引入的新特性,所谓generated column,就是数据库中这一列由其他列计算而得,我们以官方参考手册中的例子予以说明。

例如,知道直角三角形的两条直角边,要求斜边的长度。很明显,斜边的长度可以通过两条直角边计算而得,那么,这时候就可以在数据库中只存放直角边,斜边使用generated column,如下所示:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

查询结果:

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

这个例子就足以说明generated columns是什么、怎么用了。

2. virtual generated column与stored generated column的区别

在MySQL 5.7中,支持两种generated column,即virtual generated column和stored generated column,前者只将generated column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将generated column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与virtual column相比并没有优势,因此,MySQL 5.7中,不指定generated column的类型,默认是virtual column。此外:

  • stored generated column性能较差,见这里
  • 如果需要stored generated column的话,可能在generated column上建立索引更加合适,见本文第4部分的介绍

综上,一般情况下,都使用virtual generated column,这也是MySQL默认的方式,如果使用stored generated column,前面的建表语句将会是下面这样,即多了一个stored关键字:

Create Table: CREATE TABLE `triangle` (
  `sidea` double DEFAULT NULL,
  `sideb` double DEFAULT NULL,
  `sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED
)

3. 如果我对generated column做一些破坏行为会怎么样

我们已经知道了generated column是什么,并且知道了如何使用generated column,为了避免误用,我们先来进行一些实验,以免在具体使用时出现一些未知的情况。

  • 将generated column定义为 “除以0”

    如果我们将generated column定义为 “x列 / 0”,MySQL并不会直接报错,而是在插入数据时报错,并提示”ERROR 1365 (22012): Division by 0”

      mysql> create table t( x int, y int, z int generated always as( x / 0));
      Query OK, 0 rows affected (0.22 sec)
        
      mysql> insert into t(x,y) values(1,1);
      ERROR 1365 (22012): Division by 0
    
  • 插入恶意数据

    如果我们将generated column定义为 “x列/y列”,在插入数据,如果y列为0的话,同样提示错误,如下所示:

      mysql> create table t( x int, y int, z int generated always as( x / y));
      Query OK, 0 rows affected (0.20 sec)
        
      mysql> insert into t(x,y) values(1,0);
      ERROR 1365 (22012): Division by 0
    
  • 删除源列

    如果我们将generated column定义为 “x列/y列”,并尝试删除x列或y列,将提示”ERROR 3108 (HY000): Column ‘x’ has a generated column dependency.”

      mysql> create table t( x int, y int, z int generated always as( x / y));
      Query OK, 0 rows affected (0.24 sec)
      mysql> alter table t drop column x;
      ERROR 3108 (HY000): Column 'x' has a generated column dependency.
    
  • 定义显然不合法的generated column

    如果我们将generated column定义为 “x列+y列”,很明显,x列或y列都是数值型,如果我们将x列或y列定义(或修改)为字符型(当然,实际使用时应该不会有人傻到这样去做),则预期会报错,然而并没有,如下所示,我们可以正常创建。

      mysql> create table t( x int, y varchar(100), z int generated always as( x + y));
      Query OK, 0 rows affected (0.13 sec)
    

    并且插入如下这样的数据也不会出错:

      mysql> insert into t(x,y) values(1,'0');
      Query OK, 1 row affected (0.01 sec)
        
      mysql> select * from t;
      +------+------+------+
      | x    | y    | z    |
      +------+------+------+
      |    1 | 0    |    1 |
      +------+------+------+
      1 row in set (0.00 sec)
    

    但是对于MySQL无法处理的情况,则会报错:

      mysql> insert into t(x,y) values(1,'x');
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x'
    

4. generated column上创建索引

同样,我们可以在generated column上建立索引,建立索引以后,能够加快查找速度,如下所示:

mysql> create table t(x int primary key, y int, z int generated always as (x / y), unique key idz(z));
Query OK, 0 rows affected (0.11 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) NOT NULL,
  `y` int(11) DEFAULT NULL,
  `z` int(11) GENERATED ALWAYS AS (x / y) VIRTUAL,
  PRIMARY KEY (`x`),
  UNIQUE KEY `idz` (`z`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

并且,我们可以创建普通索引和唯一索引,如果是唯一索引,在违反了唯一性约束时,进行报错:

mysql> insert into t(x,y) values(1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t(x,y) values(2,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'idz'

所以,在使用MySQL5.7时,还需要对generated column有所了解,才能够解决一些以前没有遇到过的问题。

索引的限制:

虽然一般情况下,我们都应该使用virtal generated column,但是,目前使用virtual generated column还有很多限制,包括:

  • 聚集索引不能包含virtual generated column

      mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c));
      ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.
    
      mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c));
      Query OK, 0 rows affected (0.11 sec)
    
  • 不能在virtual generated column上创建全文索引和空间索引,这个在之后的MySQL版本中有望解决,见这里
  • virtual generated column不能作为外键
  • 创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的(不可重复的)函数

      mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;                                                                                                           
      ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.
      mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored;                                                                                                            
      ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.
    

5. generated column上创建索引与Oracle的函数索引的区别

介绍完MySQL在generated column上的索引,熟悉Oracle的同学这时候可能会想起Oracle的函数索引,在MySQL的generated column列上建立索引与Oracle的函数索引比较类似,又有所区别:

例如,我们有一张表,如下所示:

mysql> CREATE TABLE t1 (first_name VARCHAR(10), last_name VARCHAR(10));
Query OK, 0 rows affected (0.11 sec)

假设这时候需要建一个full_name的索引,在Oracle中,我们可以直接在创建索引的时候使用函数,如下所示:

alter table t1 add index full_name_idx(CONCAT(first_name,' ',last_name));

但是,上面这条语句在MySQL中就会报错。在MySQL中,我们可以先新建一个generated column,然后再在这个generated column上建索引,如下所示:

mysql> alter table t1 add column full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name));
mysql> alter table t1 add index full_name_idx(full_name);

乍一看,MySQL需要在表上增加一列,才能够实现类似Oracle的函数索引,似乎代价会高很多。但是,我们在第2部分说过,对于virtual generated column,MySQL只是将这一列的元信息保存在数据字典中,并不会将这一列数据持久化到磁盘上,因此,在MySQL的virtual generated column上建立索引和Oracle的函数索引类似,并不需要更多的代价,只是使用方式有点不一样而已。

参考资料

赖明星 /
Published under (CC) BY-NC-SA in categories 数据库  tagged with MySQL  数据库  MySQL5.7