西部数码主机 | 阿里云主机| 虚拟主机 | 服务器 | 返回乐道官网
当前位置: 主页 > 开发教程 > mysql教程 >

MySQL 5.7新特性generated columns

时间:2016-04-14 20:38来源:未知 作者:好模板 点击:
MySQL 5.7的一个新特性,generated column 即generated column的值是普通column产生值,有点像视图,但是又有别于视图,因为它可以选择是否存储generated column产生的值。 CREATE TABLE triangle ( sidea D

MySQL 5.7的一个新特性,generated column

即generated column的值是普通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 |
+-------+-------+--------------------+
col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  [[NOT] NULL] [[PRIMARY] KEY]

virtual不存储值,stored存储值(并支持索引)。

但是MySQL这个特性貌似用处并不大,例如要参与计算的行只能是当前行。

在物联网中,可能存在类似的需求,但是一般会要求参与计算的行是相邻的N行,或者有规则可寻的N行。例如按照相邻的5行计算平均值,最大值,最小值,方差。

MySQL 满足不了这样的需求。

在PostgreSQL中,这不是什么新鲜概念,而且支持得更彻底。

例子:

对应 mysql vitrual generated column

postgres=# create table test(c1 int, c2 int);
CREATE TABLE
postgres=# create view v_test as select c1,c2,sqrt(c1*c2+c1*c2) from test;
CREATE VIEW
postgres=# insert into test values (1,2),(10,20);
INSERT 0 2
postgres=# select * from v_test;
 c1 | c2 | sqrt 
----+----+------
  1 |  2 |    2
 10 | 20 |   20
(2 rows)

对应 mysql stored generated column

postgres=# create materialized view v_test1 as select c1,c2,sqrt(c1*c2+c1*c2) from test;
SELECT 2
postgres=# select * from v_test1;
 c1 | c2 | sqrt 
----+----+------
  1 |  2 |    2
 10 | 20 |   20
(2 rows)

还有一个更适合物联网场景的,流式处理 :

pipeline=# create stream s1(c1 int, c2 int);
CREATE STREAM
pipeline=# create continuous view test as select c1,c2,sqrt(c1*c1+c2*c2) from s1;
CREATE CONTINUOUS VIEW
pipeline=# activate;
ACTIVATE
pipeline=# insert into s1 values (1,2),(10,20);
INSERT 0 2
pipeline=# select * from test;
 c1 | c2 |       sqrt       
----+----+------------------
  1 |  2 | 2.23606797749979
 10 | 20 | 22.3606797749979
(2 rows)

流式处理加窗口和实时聚合 :

pipeline=# create continuous view test1 as select c1,count(*) over(partition by c1) from s1 ;
CREATE CONTINUOUS VIEW
pipeline=# create continuous view test2 as select c2,count(*) over w from s1 window w as(partition by c2);
CREATE CONTINUOUS VIEW
pipeline=# insert into s1 values (1,2);
INSERT 0 1
pipeline=# select * from test1;
 c1 | count 
----+-------
  1 |     1
(1 row)

pipeline=# select * from test2;
 c2 | count 
----+-------
  2 |     1
(1 row)
(责任编辑:好模板)
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
栏目列表
热点内容