MySQLでマテリアライズドビュー7 集計クエリー実装編 手動更新(on demand)
- トリガーを除去
DROP TRIGGER IF EXISTS `access_daily_materialized_insert`
- 本日分の集計を更新するreplace文を書く*1
REPLACE INTO mv_access_daily (access_date,access_daily_num) SELECT CURDATE() , count(access_id) FROM access WHERE CURDATE()=DATE(access_time)
- ストアドプロシージャ化しておく
DROP PROCEDURE access_daily_materialized_insert; delimiter // CREATE PROCEDURE access_daily_materialized_insert() begin replace into mv_access_daily (access_date,access_daily_num) select CURDATE() , count(access_id) from access where CURDATE()=DATE(access_time); end; // delimiter ; call access_daily_materialized_insert();
*1:insert into select duplicate on updateはできないのでreplace into selectを代替とする