触发器和定时器

1. 触发器

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

1.1 语法

1.2 触发的操作

需要注意的是：

1. replace操作如果是数据已存在还会触发删除触发器
2. drop和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 触发器高级使用

所谓物化视图：

1. 不是基于基表的虚表
2. 根据基表实际存在的实表
3. 预先计算并保存多表的链接(JOIN)或聚集(GROUPBY)等耗时较多的SQL操作结果

-- 创建物化视图
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;

3. Percona-Toolkit

3.1 下载安装

‌Percona-Toolkit‌是一套数据库管理命令行工具集，里面提供包括实时变更表结构工具pt-online-schema-change, 从而避免锁表的可能： pt-online-schema-change底层是使用触发器来实现的表变更，然后利用rename的原子性, 可以设置数据库操作记录从log文件变为表，然后查询日志表信息：

-- 查看当前通用日志记录模式，
-- FILE表示文件方式，table表示记录表
show variables like '%log_output%';
-- 关闭日志记录
set global general_log = 0;
-- 设置全局，使得命令行工具使用也能够生效
set global log_output='table';
-- 清空日志表数据
truncate table mysql.general_log;
-- 开启日志记录
set global general_log = 1;

使用pt-online-schema-change工具对数据库的操作：

pt-online-schema-change --alter "convert to character utf8mb4" D=test, t=a --execute

可以发现流程如下：

1. 先建表，按照指定的表结构创建a_new表。
   
2. 创建触发器 在触发器中使用了符号<=>,这是mysql独有的，表示等于null判断，和is null等效。
3. 执行数据导入, 如果数据太多会执行分页插入

INSERT LOW_PRIORITY IGNORE INTO `test`.`_a_new`(`a`, `z`)
SELECT * FROM `test`.`a` LOCK SHARE MODE

使用IGNORE是避免前面因为触发器实时数据插入后，在这里批量插入时忽略数据重复。使用LOCK SHARE MODE使用表锁防止a在被写入。
4. 原子性的修改表名

RENAME TABLE `test`.`a` to `test`.`a_old`, `test`.`a_new` to `test`.`a`;

5. 删除触发器

pt-online-schema-change优点在于表结构修改在主从结构数据库延时很短，对生产的表影响较小，缺点是表必须有主键。