存储过程&事件&触发器

基于存储过程实现数据库(mysql)历史数据的迁移

存储过程&事件&触发器

Store Procedure

存储过程,MySQL 5.0版本开始支持存储过程。存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。它存储在数据库系统中，一次编译后永久有效.

-- 创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
  routine_body
 
proc_parameter:
-- 过程参数: [ IN | OUT | INOUT ] param_name type
-- INOUT: 输入输出参数,输入输出都能用
    
routine_body: Valid SQL routine statement(sql语句)
-- DECLARE x_int int unsigned default 4000000; 定义变量
-- SET @p_in=1; (变量赋值)
BEGIN
　　[存储过程body]
END

delimiter自定义mysql语句结束符

delimiter $$ # 默认是;
-- ...sql
create procedure sp_read_customer(IN x int)
BEGIN
  select * from tb_customer where id = @x;
END
$$

基本应用sql

-- 查询存储过程
select * from mysql.proc where db = 'mysql'
select * from mysql.proc;
SHOW CREATE PROCEDURE 数据库.存储过程名;

declare x_int int; -- 声明局部变量
set @x_int = 1; -- 用户变量,断开客户端
set @@x_gloabl- 2; -- 全局变量

Event Scheduler

定时任务,MySQL5.1.6起增加了事件调度器（Event Scheduler),事件调度器也称为临时触发器（Temporal Triggers），因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器（Triggers）是基于某个表所产生的事件触发的

-- 1. mysql 事件调度器的状态(status=OFF)
show variables like 'event_scheduler';
select @@event_scheduler;
-- 2. 开启/关闭任务的调度
----  1. 全局： my.ini => event_schduler=1
----  2. 临时： 
set global event_scheduler = ON ; -- OFF/ON
set @@global .event_scheduler  = 0; -- 0/1
-- 3.  查看数据库状态
select  version();  -- 数据库版本
show events;   -- 查看定时事件
select  * from mysql.event; -- 查看定时事件
-- 4. Event操作

alter event `event_name` on completion preserve disable; --关闭任务
alter event `event_name` on completion preserve enable; --打开任务
drop event if exists event_name;  -- 删除event

---- 5. 创建任务Event -----
CREATE EVENT event_name  -- 事件名字
  ON SCHEDULE EVERY 10 second  STARTS now()  -- 事件执行的开始时间和周期
  ON COMPLETION PRESERVE -- 事件保存
DO  -- 执行的内容
BEGIN 
  CALL producer()
END

在事件中ON SCHEDULE计划任务中有2种设定的方式

用来完成单次计划任务

AT 时间戳 => 在某个时间点执行

eg：5天后
AT CURRENT_TIMESTAMP + INTERVAL 5 DAY

eg：某时间点
AT TIMESTAMP '2018-07-12 12:00:00'

用来完成重复的计划任务

EVERY 时间(单位)的数量 时间单位 [STARTS 时间戳] [ENDS时间戳]

eg：每隔1秒
EVERY 1 SECOND

eg：每隔10分钟。
EVERY 10 MINUTE

eg：从2018-08-01 12:00:00开始每隔1天
EVERY 1 DAY STARTS '2018-08-01 12:00:00'
EVERY 10 second STARTS TIMESTAMP '2018-08-01 12:00:00'

eg：5天后开启每天定时处理
EVERY 1 DAY START CURRENT_TIMESTAMP + INTERVAL 5 DAY

eg：每天定时处理5天后停止
EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY

在两种计划任务中，时间戳可以是任意的TIMESTAMP和DATETIME数据类型，时间戳需要大于当前时间。

在重复的计划任务中，时间（单位）的数量可以是任意非空（NOT NULL）的整数形式，时间单位是关键词：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

[ON COMPLETION [NOT] PRESERVE]

ON COMPLETION参数表示“当这个事件不会再发生的时候”，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被DROP掉，建议使用该参数，以便于查看EVENT具体信息。系统默认 NOT PRESERVE

应用

问题 : 迁移1个月之前的数据到新的表单?

1. 创建存储过程

-- 这里拆分了三个存储过程
delimiter $$

-- 指定存储过程应用的数据库, select * from mysql.proc where db = 'wallet';
use `wallet`;

create procedure tb_exist(IN tb_name varchar(20), OUT is_exists integer)
  begin
    -- 变量应用在SQL的条件参数里面
    SELECT COUNT(*) into is_exists FROM information_schema.`TABLES` WHERE TABLE_NAME = tb_name;
  end $$;

create procedure tb_create(IN tb_name varchar(20))
  begin
    set @tbn = tb_name;
    -- 动态生成sql的形式
    set @sql_drop = concat('drop table if exists ', @tbn);
    set @sql_cre = concat('CREATE TABLE ', @tbn,' (
      `id`          int(11)  NOT NULL AUTO_INCREMENT,
      `addr`        varchar(60)       DEFAULT NULL,
      `public_key`  varchar(100)      DEFAULT NULL,
      `private_key` varchar(100)      DEFAULT NULL,
      `created_at`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at`  datetime          DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    )
      ENGINE = InnoDB
      DEFAULT CHARSET = utf8');
    prepare tmp_2 from @sql_drop;
    execute tmp_2;
    prepare tmp_1 from  @sql_cre;
    execute tmp_1;

  end $$;

create procedure procedure_save_by_date(IN tbName varchar(20))
  begin
    -- declare tb_is_exists integer default 0;
    set @tb_pre = tbName;
    set @date_suffix = date_format(now(),'%Y%m%d');
    set @tb_name = concat(tbName, '_', @date_suffix);
    set @cur_date = curdate();

    call tb_create(@tb_name);

    -- 拼接sql, 预编译，执行
    set @sql_in = concat('insert into ',@tb_name,
                         ' select * from ',@tb_pre,' where date(created_at) = \'',@cur_date,'\'');
    set @sql_del = concat('delete from ', @tb_pre, ' where date(created_at) = \'',@cur_date,'\'');

    -- 输出信息
    select @sql_in;
    select @sql_del;

    prepare tmp_3 from @sql_in;
    execute tmp_3;
    prepare tmp_4 from @sql_del;
    execute tmp_4;
  end $$
  
delimiter ;


delimiter $$
-- 备份历史数据
create procedure procedure_backup_hist_data(IN tbName varchar(20),IN tbDate datetime)
  begin
    -- declare tb_is_exists integer default 0;
    set @tb_pre = tbName;
    set @cur_date = tbDate;
    set @date_suffix = date_format(@cur_date,'%Y%m%d');
    set @tb_name = concat(tbName, '_', @date_suffix);

    call tb_create(@tb_name);
    set @sql_in = concat('insert into ',@tb_name,
                         ' select * from ',@tb_pre,' where date(created_at) = \'',@cur_date,'\'');
    set @sql_del = concat('delete from ', @tb_pre, ' where date(created_at) = \'',@cur_date,'\'');
    select @sql_in;
    select @sql_del;

    prepare tmp_3 from @sql_in;
    execute tmp_3;
    prepare tmp_4 from @sql_del;
    execute tmp_4;
  end $$;

-- 根据日期备份历史数据
create procedure procedure_loop_by_date(in tbName varchar(20), IN tDate datetime)
  begin
    set @start_date = tDate;
    while datediff(@start_date,curdate()) < 0 do
      set  @start_date = date_add(@start_date,interval 1 day );
      select @start_date;
      -- select date_format(@start_date,'%Y%m%d');
      call procedure_backup_hist_data(tbName,date(@start_date));
    end while ;
  end $$
delimiter ;

问题

1. 怎么解决存储过程日志打印的问题

2. 怎么比较使用存储过程 VS 使用代码的性能优劣

1. 创建任务

create event event_save_by_date
  on schedule every 1 hour starts now()
  on completion preserve do call procedure_save_by_date('addresses');

1. 执行任务

alter event event_save_by_date on completion preserve disable ;

Triggers

触发器

mysql-procedure-1