mysql 物化视图,MySQL中的物化视图

How to create a materialized view in mysql ?

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

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.