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)(责任编辑:好模板) |