`
zhaomengsen
  • 浏览: 207752 次
  • 性别: Icon_minigender_1
  • 来自: 河北
社区版块
存档分类
最新评论

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

阅读更多

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

Generated Column是什么 
Virtual Column与Stored Column的区别 
如果我对Generated Column做一些破坏行为会怎么样 
Generated Column上创建索引 
Generated Column上创建索引与Oracle的函数索引的区别

Generated Column是什么

Generated Column是MySQL 5.7引入的新特性,所谓Cenerated 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是什么,以及怎么使用用了。


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。此外:  #p#分页标题#e#
Stored Generated Column性能较差,见这里 
如果需要Stored Generated Golumn的话,可能在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) 


如果对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));  #p#分页标题#e#
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'

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`), #p#分页标题#e#
  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版本中有望解决(Inside君咋记得Stored Column上市可以的呢?)。

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. #p#分页标题#e#




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的函数索引类似,并不需要更多的代价,只是使用方式有点不一样而已。

转载于:http://www.itxuexiwang.com/a/shujukujishu/2016/0302/199.html?1457018327
分享到:
评论

相关推荐

    MYSQL5.7官方文档-5.7

    对于每个版本的变更详情,可以查看MySQL 5.7的发布说明,其中列出了新特性、修复的bug和已知问题。 **法律信息:** 文档的前言和法律通知部分包含了许可证信息和其他法律条款。在使用MySQL时,确保遵循这些规定。 ...

    MySQL 5.7新特性介绍

    MySQL 5.7是MySQL数据库的一个重要版本更新,它带来了一系列的新特性,旨在提升数据库的安全性、灵活性、易用性、可用性和性能。以下是对这些关键领域的详细介绍: 2.1 安全性 MySQL 5.7在安全性方面做出了显著改进...

    mysql5.7 生成列 generated column用法实例分析

    本文实例讲述了mysql5.7 生成列 generated column用法。分享给大家供大家参考,具体如下: 生成列的值是根据列定义中的表达式计算得出的。 mysql5.7支持两种类型的生成列: 1、virtual 生成列:当从表中读取记录时,...

    MySQLMySQL5.7新功能演示 数据库设计专为下一代Web、移动与云应用

    MySQL作为一款流行的开源关系型数据库管理系统,自发布以来不断...无论是对于初学者还是经验丰富的数据库管理员,这些新特性都将有助于提升工作效率,简化数据库管理,并最终构建更为安全、高效和可靠的数据库系统。

    mysql5.7安装包

    以下是对MySQL 5.7安装包及其关键特性的详细说明: 1. **安装过程**: - `mysql-installer-web-community-5.7.2.msi` 是MySQL的Web社区版安装程序,适用于Windows操作系统。它包含了完整的安装向导,帮助用户轻松...

    mysql5.7mysql5.7mysql5.7

    4. **增强的索引**:MySQL 5.7支持更多类型的索引,如虚拟列(generated columns)的索引和覆盖索引,可以更有效地进行查询优化。 5. **存储过程和函数的改进**:新增了窗口函数和GROUP BY优化,使存储过程和函数...

    mysql5.7.38

    - **InnoDB存储引擎优化**:MySQL 5.7 对 InnoDB 引擎进行了大量改进,如更快的插入速度、更高效的索引处理和更优的内存管理,以提高整体数据库性能。 - **Query Cache**:虽然在 MySQL 5.7 中移除了 Query Cache...

    RedHat Linux7.2安装MySQL5.7方法

    ### RedHat Linux 7.2 安装 MySQL 5.7 的详细步骤 #### 安装前的准备工作 在开始安装 MySQL 5.7 之前,确保已经做好了以下准备工作: 1. **登录用户**: 以 root 用户身份登录到 RedHat Linux 7.2 系统。 2. **...

    简单谈谈MySQL5.7 JSON格式检索

    由于MySQL在早期版本中并不直接支持在JSON列上创建索引,我们可以通过创建一个虚拟列(Virtual Column)来辅助实现。虚拟列是一种特殊列,它不是物理存储在表中的,而是通过一个表达式在运行时计算得出。然后,我们...

    MySQL基础之MySQL 5.7 新增配置

    MySQL 5.7是MySQL数据库的一个重要版本,它引入了许多新特性和改进,旨在提高性能、可扩展性和安全性。在MySQL 5.7中,一些关键的新增配置选项提升了用户体验和管理效率。以下是对这些新增配置的详细说明: 1. **...

    centos7安装mysql5.7

    ### CentOS 7 安装 MySQL 5.7 在 CentOS 7 系统中,默认安装的是 MariaDB,作为 MySQL 的一个分支,它在很多场景下能够满足需求。然而,在某些特定的应用环境中,用户可能需要安装 MySQL 以确保兼容性和特定功能的...

    CentOS7下手动安装MySQL5.7.txt

    ### CentOS7 下手动安装 MySQL 5.7 的详细步骤与注意事项 #### 一、准备工作 在 CentOS 7 系统上手动安装 MySQL 5.7 需要做好一系列的准备工作,确保安装过程中不会出现问题。 ##### 1.1 卸载默认安装的 MariaDB ...

    mysql-cluster-excerpt-5.7-en.a4.pdf

    This is the MySQL Cluster NDB 7.5 extract from the MySQL 5.7 Reference Manual. For legal information, see the Legal Notices. For help with using MySQL, please visit the MySQL Forums, where you can ...

    MySQL5.7如何修改root密码

    MySQL5.7 开始,增加了很多安全性的更新。老版本的用户可能会有一些不习惯,这里介绍关于5.7版本的数据库密码问题。 5.7.6 以后的版本 5.7.6 以后的版本在启动数据库的时候,会生成密码放到日志文件里,像这样: ...

    在centOS 7安装mysql 5.7的详细教程

    在CentOS 7上安装MySQL 5.7是一个常见的任务,尤其对于那些需要在Linux环境中部署数据库服务的开发者和系统管理员。以下是一个详尽的步骤指南: 首先,确保你的系统是最新的,通过运行以下命令: ```bash sudo yum ...

    MySQL 5.7.20安装包

    这个版本在MySQL的演化历程中扮演了关键角色,引入了许多新特性和改进,旨在提升用户体验并增强数据库性能。 首先,MySQL 5.7.20 强调了性能的提升。它引入了InnoDB存储引擎的优化,如更快的插入速度,更高效的索引...

    mysql 5.7.23

    MySQL 5.7.23 是 MySQL 数据库管理系统的一个重要版本,它在 5.7 系列中提供了许多增强特性和性能优化。这个版本专为 Windows 操作系统设计,确保了在 Windows 平台上顺畅运行。以下是关于 MySQL 5.7.23 的一些关键...

    快速在Windows上安装MySQL5.7压缩包

    本文给大家分享在Windows上安装MySQL5.7压缩包的方法,具体内容详情如下所示: 压缩包解压到指定路径下 E:\mysql\mysql-5.7.18-winx64 准备my.ini配置文件 # MySQL Server Instance Configuration File # ---------...

    全面解析Windows下安装 mysql5.7的方法

    关于在windows下安装mysql 5.7 •要注意的新坑:  5.7版本安装后ROOT账号是有默认的密码的,这个密码在windows下可以在mysql.ini配置文件指定的data文件夹下面,那个为.err后缀名的文件里面找到。  2016-07-16T...

Global site tag (gtag.js) - Google Analytics