ITPub博客

首页 > 数据库 > MySQL > 【Mysql】MySQL 5.7新特性之Generated Column(函数索引)

【Mysql】MySQL 5.7新特性之Generated Column(函数索引)

MySQL 作者:小亮520cl 时间:2016-01-08 11:01:52 0 删除 编辑
  1. 原文地址
  2.  http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=400998070&idx=1&sn=a3fd251ddd047e089e80cae5d4d90c34&scene=0#wechat_redirect

  1.  正文 

  1. MySQL 5.7引入了Generated Column,这篇文章简单地介绍了Generated Column的使用方法和注意事项,为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开: 
  2. Generated Column是什么 
  3. Virtual Column与Stored Column的区别 
  4. 如果我对Generated Column做一些破坏行为会怎么样 
  5. Generated Column上创建索引 
  6. Generated Column上创建索引与Oracle的函数索引的区别 

  7. Generated Column是什么 

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


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

  10. CREATE TABLE triangle ( 
  11. sidea DOUBLE, 
  12. sideb DOUBLE, 
  13. sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))); 

  14. INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8); 

  15. 查询结果: 

  16. mysql> SELECT * FROM triangle; 
  17. +-------+-------+--------------------+ 
  18. | sidea | sideb | sidec | 
  19. +-------+-------+--------------------+ 
  20. | 1 | 1 | 1.4142135623730951 | 
  21. | 3 | 4 | 5 | 
  22. | 6 | 8 | 10 | 
  23. +-------+-------+--------------------+ 

  24. 这个例子就足以说明Generated Columns是什么,以及怎么使用用了。 


  25. Virtual Generated Column与Stored Generated Column的区别 

  26. 在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。此外: 
  27. Stored Generated Column性能较差,见这里 
  28. 如果需要Stored Generated Golumn的话,可能在Generated Column上建立索引更加合适,见本文第4部分的介绍 

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

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


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

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

  3. 将generated column定义为 "除以0" 

  4. 如果我们将generated column定义为 "x列 / 0",MySQL并不会直接报错,而是在插入数据时报错,并提示"ERROR 1365 (22012): Division by 0" 
  5.   
  6. mysql> create table t( x int, y int, z int generated always as( x / 0)); 
  7. Query OK, 0 rows affected (0.22 sec) 
  8.  
  9. mysql> insert into t(x,y) values(1,1); 
  10. ERROR 1365 (22012): Division by 0 


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

  13. mysql> create table t( x int, y int, z int generated always as( x / y)); 
  14. Query OK, 0 rows affected (0.20 sec) 

  15. mysql> insert into t(x,y) values(1,0); 
  16. ERROR 1365 (22012): Division by 0 


  1. 删除源列 
  2.   
  3. 如果我们将generated column定义为 "x列/y列",并尝试删除x列或y列,将提示"ERROR 3108 (HY000): Column 'x' has a generated column dependency." 

  4. mysql> create table t( x int, y int, z int generated always as( x / y)); 
  5. Query OK, 0 rows affected (0.24 sec) 

  6. mysql> alter table t drop column x; 
  7. ERROR 3108 (HY000): Column 'x' has a generated column dependency. 


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

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

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

  7. mysql> insert into t(x,y) values(1,'0'); 
  8. Query OK, 1 row affected (0.01 sec) 

  9. mysql> select * from t; 
  10. +------+------+------+ 
  11. | x | y | z | 
  12. +------+------+------+ 
  13. | 1 | 0 | 1 | 
  14. +------+------+------+ 
  15. 1 row in set (0.00 sec) 

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

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

  19. Generated Column上创建索引 

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

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

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

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

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

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

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


  1. 索引的限制 

  1. 虽然一般情况下都应该使用Virtal Generated Column,但是,目前使用Virtual Generated Column还有很多限制,包括: 

  2.   
  3. 聚集索引不能包含virtual generated column 

  4. mysql> create table t1(int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c)); 
  5. ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns. 

  6. mysql> create table t1(int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c)); 
  7. Query OK, 0 rows affected (0.11 sec) 

  8. 不能在Virtual Generated Column上创建全文索引和空间索引,这个在之后的MySQL版本中有望解决(Inside君咋记得Stored Column上市可以的呢?)。 

  9. Virtual Generated Column不能作为外键 

  10. 创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的(不可重复的)函数 

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

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




  1. Generated Column上创建索引与Oracle的函数索引的区别 

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

  2. 例如有一张表,如下所示: 

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

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

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

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

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

  9. mysql> alter table t1 add index full_name_idx(full_name); 

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

  11. 参考资料 
  12. http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/ 
  13. http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns 
  14. http://mysqlserverteam.com/virtual-columns-and-effective-functional-indexes-in-innodb/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-1973687/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
毕业以后专业任职数据库工程师职位,itpub一直作为自己的笔记记录的地方,blog写的不详细,草书笔记,仅供参考!

注册时间:2013-09-12

  • 博文量
    531
  • 访问量
    979561