mysql中的物化视图 [英] Materialized view in mysql

问题描述

如何在mysql中创建实例化视图? 我无法像在MS SQL Server中那样创建实例化视图.

How to create a materialized view in mysql ? I am not able to create a materialized view as we do in MS SQL Server.

任何人都可以让我知道在mysql中创建它的方法.

Could anyone let me know the ways to create it in mysql.

推荐答案

您可以创建不可更新的动态视图-如果基础表中有时间戳(索引)，则可以添加快照，类似:

You can create a non-updateable dynamic view - and if you've got (indexed) timestamps in the underlying table(s) you can add in a snapshot, something like:

CREATE VIEW almost_materialzd
AS
SELECT snp.*
FROM snapshot snp
WHERE s.id NOT IN (SELECT id
  FROM source_data sd
  INNER JOIN ref_data rd
  ON rd.value='snapshot of source_data'
  AND sd.update_timestamp>rd.timetamp)
UNION
SELECT *
FROM source_data sd2
INNER JOIN ref_data rd2
ON rd2.value='snapshot of source_data'
AND sd2.update_timestamp>rd2.timetamp);

但是更好的解决方案是在基础表中添加一个或多个触发器，以便在基础表发生更改时重新填充代表物化视图的表中的相关行.

But a better solution is to add a trigger (or triggers) to the the underlying table(s) to re-populate the relevant rows in a table representing the materialized view when the underlying tables are changed.

这篇关于mysql中的物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！