mysql三个应用场景

场景一，数据表自动备份（多个数据表字段同步等），使用触发器。如updatelog记录对资源的所有操作日志，reslastlog记录资源最后操作的日志信息。同步方式实现如下：

//创建表
DROP TABLE IF EXISTS updatelog;
CREATE TABLE `updatelog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resourceid` int(11) DEFAULT NULL,
  `log` text,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

//必须指定主键或unique，不然无法replace
DROP TABLE IF EXISTS reslastlog;
CREATE TABLE `reslastlog` (
  `resourceid` int(11) NOT NULL DEFAULT '0',
  `log` text,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`resourceid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

//创建触发器
DROP TRIGGER IF EXISTS t_afterinsert_on_updatelog;
delimiter //
CREATE TRIGGER t_afterinsert_on_updatelog
AFTER INSERT ON updatelog
FOR EACH ROW
BEGIN
     replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);
END;
//
delimiter ; 

DROP TRIGGER IF EXISTS t_afterdelete_on_updatelog;
delimiter //
CREATE TRIGGER t_afterdelete_on_updatelog
AFTER DELETE ON updatelog
FOR EACH ROW
BEGIN
     delete from reslastlog where resourceid=old.resourceid;
END;
//
delimiter ; 

//测试
insert into updatelog(resourceid, log, createtime) values(1, "version 1-0",now());
insert into updatelog(resourceid, log, createtime) values(1, "version 1-1",now());
insert into updatelog(resourceid, log, createtime) values(2, "version 2-2",now());
delete from updatelog where resourceid = 2;

//触发器相关操作
mysql> show triggers;
+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger                    | Event  | Table     | Statement                                                                                                          | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| t_afterinsert_on_updatelog | INSERT | updatelog | BEGIN
     replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);
END | AFTER  | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| t_afterdelete_on_updatelog | DELETE | updatelog | BEGIN
     delete from reslastlog where resouceid=old.resourceid;
END                                              | AFTER  | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
drop trigger t_afterinsert_on_updatelog;

场景二，用户定义函数或者存储过程实现简单的后台数据运算。示例如下：

//用户定义函数

//创建资源基本信息表
CREATE TABLE `baseinfo` (
  `id` int(11) DEFAULT NULL,
  `content` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into baseinfo values(1,"one");
insert into baseinfo values(2,"two");
insert into baseinfo values(3,"three");

//创建每日资源pv表
CREATE TABLE `dayinfo` (
  `id` int(11) DEFAULT NULL,
  `pv` int(11) DEFAULT NULL,
  `day` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into dayinfo values(1,10,"2014-10-01");
insert into dayinfo values(1,12,"2014-10-02");
insert into dayinfo values(1,16,"2014-10-03");
insert into dayinfo values(2, 30, "2014-10-02");

查询资源指定时段降序排列
mysql> select baseinfo.id as id, content, sum(pv) as totalpv from baseinfo,dayinfo where baseinfo.id=dayinfo.id and day>="2014-10-02" and day<="2014-10-03" group by id order by totalpv desc;
+------+---------+---------+
| id   | content | totalpv |
+------+---------+---------+
|    2 | two     |      30 |
|    1 | one     |      28 |
+------+---------+---------+

上面的sql语法非常复杂，如果用UDF会方便简洁很多。

1，查看用户定义函数功能是否开启，ON为开启
show variables like '%func%';

2，如果是OFF，则执行下面的操作
set global log_bin_trust_function_creators=1;

3，创建用户定义函数
delimiter $$
CREATE FUNCTION getTotalPV(targetid int,dayfrom date,dayto date) RETURNS int
begin
declare totalpv int default 0;
set totalpv=(select sum(pv) from dayinfo where id = targetid and day>=dayfrom and day<=dayto);
if totalpv is null then
    set totalpv = 0;
end if;
return totalpv;
end$$
delimiter ;

mysql> select id, content, getTotalPV(id, "2014-10-02", "2014-10-03") as totalpv from baseinfo order by totalpv desc;
+------+---------+---------+
| id   | content | totalpv |
+------+---------+---------+
|    2 | two     |      30 |
|    1 | one     |      28 |
|    3 | three   |       0 |
+------+---------+---------+

4，查看udf定义show create function getTotalPV;


//存储过程
drop procedure if exists getjson;
delimiter $$
create procedure getjson
(
   str1 varchar(1024),
   str2 varchar(1024),
   str3 varchar(1024),
   str4 varchar(1024)
)
begin
   if str1 is NULL then
       set str1="";
   end if;
   if str2 is NULL then
       set str2="";
   end if;
   if str3 is NULL then
       set str3="";
   end if;
   if str4 is NULL then
       set str4="";
   end if;
   select CONCAT("[",str1,",",str2,",",str3,",",str4,"]") as jsonstr;
end;$$
delimiter ;

mysql> call getjson("a","b","c","d");
+-----------+
| jsonstr   |
+-----------+
| [a,b,c,d] |
+-----------+
1 row in set (0.00 sec)

 场景三：mysql调用外部应用程序（如表有数据更新后，通过触发器调用外部应用程序执行任务）

1.lib_mysqludf_sys简介
mysql中没有执行外部命令的函数，要调用外部的命令，可以通过开发MySQL UDF来实现，lib_mysqludf_sys 就是一个实现了此功能的UDF库。
下载地址：https://github.com/mysqludf/lib_mysqludf_sys

2.使用方法
2.1 安装部署(需要安装mysql-devel)
a) lib_mysqludf_sys.so复制到mysql/lib/plugin目录下。

b) 在mysql中创建函数(根据需要选取)：

Drop FUNCTION IF EXISTS lib_mysqludf_sys_info;
Drop FUNCTION IF EXISTS sys_get;
Drop FUNCTION IF EXISTS sys_set;
Drop FUNCTION IF EXISTS sys_exec;
Drop FUNCTION IF EXISTS sys_eval;
 
Create FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';
Create FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';
Create FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';
Create FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
Create FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';
2.2 使用此函数
例：在select语句调用mkdir命令

Select sys_exec('mkdir -p /home/user1/aaa')
例：在触发器中调用外部的脚本(脚本需要可执行权限)

Create TRIGGER trig_test AFTER Insert ON <table1>
FOR EACH ROW 
BEGIN
    DECLARE ret INT;
    Select sys_exec('/home/user1/test.sh') INTO ret;
END