触发器和定时器

1. 触发器

特点是一触即发，当表上出现特定的事件时触发该程序执行，触发器类型有UPDATE/DELETE/INSERT。

1.1 语法

1.2 触发的操作

mysql支持的触发器事件发生和数据操作的关系如下： 需要注意的是：

1. replace操作如果是数据已存在还会触发删除
2. drop table、drop a partition和truncate操作不会触发删除

create table sql_trains.stu(
name varchar(50),
course varchar(50),
score int,
primary key (name)
);

DELIMITER //
create trigger sql_trains.trg_upd_score
before update on stu 
for each row 
begin
	if NEW.score<0 then 
		set NEW.score=0;
	elseif NEW.score>100 then 
		set NEW.score=100;
	end if;
end; //

1.3 触发器总结

1. 触发器对性能有损耗，应当非常慎重使用
2. 对于事务表，触发器执行失败则整个语句回滚
3. Row格式主从复制，触发器不会在从库上执行
4. 使用触发器时应防止递归执行

1.4 触发器高级使用

mysql并不支持物化视图，下面的例子是通过创建一个实体表来存储查询结果，并使用触发器定期更新它：

-- 创建物化视图
CREATE TABLE sql_trains.Orders_MV(
product name VARCHAR(30) NOT NULL,
price_sum DECIMAL(8,2) NOT NULL,
amount_sum INT NOT NULL,
price_aVg FLOAT NOT NULL,
orders_cnt INT NOT NULL,
UNIQUE INDEX (product_name)
);
-- 创建普通视图
CREATE VIEW sql_trains.v_orders AS SELECT
product_name, sum(price), sum(amount), avg(price), coynt(1) 
FROM Orders
GROUP BY product_name;

CREATE TRIGGER tgr_Orders_insert 
AFTER INSERT ON Orders 
FOR EACH ROW
BEGIN
	SET @old_price_sum = 0;
	SET @old_amount_sum = 0;
	SET @old_price_avg = 0;
	SET @old_orders_cnt = 0;
	SELECT 
		IFNULL(price_sum, 0),IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0)
	FROM Orders_MV
	WHERE product_name = NEW.product_name
	INTO @old_price_sum, @old_amount_sum,@old_price_avg, @old_orders_cnt;

	SET @new_price_sum=@old_price_sum + NEW.price;
	SET @new_amount_sum= @old_amount_sum + NEW.amount;
	SET @new_orders_cnt = @old_orders_cnt + 1;
	SET @new_price_avg = @new_price_sum/@new_orders_cnt ;
	REPLACE INTO Orders_Mv 
    VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt );
end;

2. 定时器

2.1 语法

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON {REPLICA | SLAVE}]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

2.2 查看定时器是否开启

show variables like '%event_scheduler%';

2.3 定时器实操

-- 只执行一次的定时器
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;
-- 每小时执行一次
CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

比如还可以使用定时器每天创建一个分区，，雅虎有个分区脚本：https://github.com/yahoo/mysql_partition_manager