MySQL Three application scenarios

Scene One, data table automatic backup (Multiple tables field synchronization, etc.), using triggers. If Updatelog records all operations logs for a resource, Reslastlog logs information about the last operation of the resource. The synchronization method is implemented as follows:

//Create tables drop table IF EXISTS updatelog;CREATE TABLE ' updatelog ' (' id ' int () Not NULL auto_increment,' ResourceID ' int () DEFAULT NULL,' log ' text,' Createtime ' datetime DEFAULT NULL,PRIMARY KEY (' id ')) ENGINE=myisam DEFAULT charset=latin1//must specify a primary key or unique, otherwise replacedrop TABLE IF EXISTS reslastlog;CREATE TABLE ' reslastlog ' (' ResourceID ' int () not NULL DEFAULT ' 0 ',' log ' text,' UpdateTime ' datetime DEFAULT NULL,PRIMARY KEY (' ResourceID ')) ENGINE=myisam DEFAULT charset=latin1//create trigger drop TRIGGER IF EXISTS t_afterinsert_on_updatelog;delimiter//create TRIGGER t_afterinsert_on_updatelogafter INSERT on updatelogfor all rowbegin replace into Resla Stlog (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 Reslas TLog where ResourceID=old.resourceid;END;//delimiter; //test 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;//Trigger related actions 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.00sec) Drop trigger T_afterinsert_on_updatelog;

Scenario two, user-defined functions or stored procedures implement simple background data operations. Examples are as follows:

/ /user-defined function//create resource basic Information table "CREATE TABLE ' Baseinfo ' (' id ' int () DEFAULT NULL,' content ' text) ENGINE=myisam DEFAULT charset=latin1;INSERT into baseinfo values (1, "one");INSERT into Baseinfo values (2, "both");INSERT into Baseinfo values (3, "three");//Create a daily resource PV table created table ' Dayinfo ' (' id ' int () DEFAULT NULL,' PV ' int () 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");Query Resources Specify a period of time in descending order 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<= "201 4-10-03 "GROUP by ID ORDER BY TOTALPV DESC;+------+---------+---------+| ID | content | TOTALPV |+------+---------+---------+| 2 |      both | 30 || 1 |      One | 28|+------+---------+---------+ The above SQL syntax is very complex, and if you use UDFs it will be easier and more concise. 1to see if the user-defined function is enabled, on for show variables like '%func% ';2if it is off, perform the following operation set global Log_bin_trust_function_creators=1;3, create a user-defined function delimiter $ $CREATE function GETTOTALPV (targetid int, Dayfrom date,Dayto Date) RETURNS intbegindeclare TOTALPV int default0;Set totalpv= (select SUM (PV) from dayinfo where id = Targetid and Day>=dayfrom and Day<=dayto);if TOTALPV is a 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 |      both | 30 || 1 |      One | 28 || 3 |       Three | 0 |+------+---------+---------+4. View UDF definition show Create function GETTOTALPV;//Stored Procedure 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 and then set STR1="";End If;If STR2 is a NULL then set str2="";End If;if STR3 is a NULL then set STR3="";End If;if STR4 is a 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)

Scenario Three: MySQL calls an external application (such as when a table has data updates, invoking an external application through a trigger to perform a task)

1. Lib_mysqludf_sys Introduction MySQL does not have a function to execute external commands, to invoke external commands, can be implemented by developing a MySQL UDF, Lib_mysqludf_sys is a UDF library that implements this function. : Https://github.com/mysqludf/lib_mysqludf_sys2. How to use2.1Install the deployment (requires installation of Mysql-devel) a) lib_mysqludf_sys.so copy to the Mysql/lib/plugin directory. b) Create a function in MySQL (select as needed): 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.2Use this function example: Invoke the mkdir command in the SELECT statement Select Sys_exec (' mkdir-p/home/user1/aaa ') Example: Call an external script in a trigger (script requires executable permission) 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 Three application scenarios