mysql 物化视图 更新_刷新物化视图 - 云原生数仓 AnalyticDB MySQL - 阿里云

自动刷新

自动刷新支持使用时间函数(START WITH ... NEXT)来设置刷新规律。START WITH是可选的，默认从当前时刻开始。# 每周一晚上2点刷新

CREATE MATERIALIZED VIEW myview

REFRESH

START WITH DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')

NEXT DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')

AS

SELECT count(*) as cnt FROM base

# 每天晚上夜里2点刷新

CREATE MATERIALIZED VIEW myview

REFRESH

START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')

NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')

AS

SELECT count(*) as cnt FROM base

# 每个月第一天凌晨2点刷新

CREATE MATERIALIZED VIEW myview

REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00')

AS

SELECT count(*) as cnt FROM base

# 只刷一次