mysql三個應用情境

標籤：des   style   blog   http   io   color   os   ar   使用   

情境一，資料表自動備份（多個資料表欄位同步等），使用觸發器。如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，不然無法replaceDROP 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_updatelogAFTER INSERT ON updatelogFOR EACH ROWBEGIN     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_updatelogAFTER DELETE ON updatelogFOR EACH ROWBEGIN     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    |          | [email protected] | latin1               | latin1_swedish_ci    | latin1_swedish_ci  || t_afterdelete_on_updatelog | DELETE | updatelog | BEGIN     delete from reslastlog where resouceid=old.resourceid;END                                              | AFTER  | NULL    |          | [email protected] | 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 intbegindeclare 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_sys2.使用方法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

 

mysql三個應用情境