Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve timed tasks in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Scheduled task

Check whether event is enabled: show variables like'% sche%'

Turn on event planning: set global event_scheduler=1

Close event task: alter event e_test ON COMPLETION PRESERVE DISABLE

Account opening event task: alter event e_test ON COMPLETION PRESERVE ENABLE

A simple example.

Create table CREATE TABLE test (endtime DATETIME)

Create a stored procedure test

CREATE PROCEDURE test ()

BEGIN

Update examinfo SET endtime = now () WHERE id = 14

END

Create event e_test

CREATE EVENT if not exists e_test

On schedule every 30 second

On completion preserve

Do call test ()

CREATE EVENT if not exists e_test

On schedule every 1 second

On completion preserve

Do insert into aa values (now ())

The stored procedure test is executed every 30 seconds, updating the current time to the endtime field of the id=14 record in the examinfo table.

Trigger

Delimiter / /

CREATE TRIGGER trigger_htmlcache BEFORE INSERT ON t_model

FOR EACH ROW BEGIN

If CURDATE () Insert way of testing.

DELIMITER $$

DROP PROCEDURE IF EXISTS `njfyedepartment`.`sp _ ireport` $$

CREATE `PROCEDURE `sp_ ireport` (IN qureyType VARCHAR (20), IN daytime VARCHAR (20), IN p_ids VARCHAR (50), IN c_ids VARCHAR (50), IN ct1_ids VARCHAR (50), IN ct2_ids VARCHAR (50), IN ku VARCHAR (50), IN ireport_chart varchar (50))

BEGIN

DECLARE i INT DEFAULT 1

IF qureyType = 'insert' OR qureyType =' INSERT' THEN

INSERT INTO ireport

(pid,cid,ct1id,ct2id,creatTime,crawlerNumber,WEEK)

SELECT province AS pid

Cityid AS cid

Category1id AS ct1id

Category2id AS ct2id

(CURRENT_DATE) AS creatTime,COUNT (*) AS crawlerNumber

(FLOOR (DAYOFMONTH (CURRENT_DATE) / 8) + 1) AS WEEK

FROM t_model t

WHERE TIME > (CURRENT_DATE-1) AND TIME < (CURRENT_DATE)

AND province IS NOT NULL

AND cityid IS NOT NULL

GROUP BY province,cityid,category1id,category2id

END IF

IF qureyType = 'month' OR qureyType =' MONTH' THEN

IF EXISTS (SELECT * FROM information_ schema.`TABLES`T WHERE TABLE_NAME = 'tmp_result' AND TABLE_SCHEMA = ku) THEN

DROP TABLE tmp_result

END IF

CREATE TABLE tmp_result

(pid VARCHAR 50), pName VARCHAR 50, cid VARCHAR 50, cName VARCHAR 50, ct1id VARCHAR 50, ct1Name VARCHAR 50

Ct2id VARCHAR (50), ct2Name VARCHAR (50), month2 INTEGER,month3 INTEGER,month4 INTEGER,month5 INTEGER,month6 INTEGER

Month7 INTEGER,month7 INTEGER,month8 INTEGER,month9 INTEGER,month20 INTEGER,month21 INTEGER,month22 INTEGER,heji INTEGER)

Lable_exit: BEGIN

SET @ SqlCmd = 'INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)

SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM

(SELECT ia.pid,a.name AS pname,ia.cid,b.name AS cname,ia.ct1id,c.name AS ct1name,ia.ct2id,d.name AS ct2name

FROM ireport ia

LEFT JOIN province an ON ia.pid=a.id

LEFT JOIN city b ON ia.cid=b.id

LEFT JOIN t_category1 c ON ia.ct1id=c.id

LEFT JOIN t_category2 d ON ia.ct2id=d.id

WHERE YEAR (ia.creatTime) = YEAR'

IF p_ids IS NOT NULL AND p_ids''THEN

SET @ SqlCmd = CONCAT (@ SqlCmd, 'and ia.pid in (')

SET @ SqlCmd = CONCAT (@ SqlCmd, p_ids)

SET @ SqlCmd = CONCAT (@ SqlCmd,')

END IF

IF c_ids IS NOT NULL AND c_ids''THEN

SET @ SqlCmd = CONCAT (@ SqlCmd, 'and ia.cid in (')

SET @ SqlCmd = CONCAT (@ SqlCmd, c_ids)

SET @ SqlCmd = CONCAT (@ SqlCmd,')

END IF

IF ct1_ids IS NOT NULL AND ct1_ids''THEN

SET @ SqlCmd = CONCAT (@ SqlCmd, 'and ia.ct1id in (')

SET @ SqlCmd = CONCAT (@ SqlCmd, ct1_ids)

SET @ SqlCmd = CONCAT (@ SqlCmd,')

END IF

IF ct2_ids IS NOT NULL AND ct2_ids''THEN

SET @ SqlCmd = CONCAT (@ SqlCmd, 'and ia.ct2id in (')

SET @ SqlCmd = CONCAT (@ SqlCmd, ct2_ids)

SET @ SqlCmd = CONCAT (@ SqlCmd,')

END IF

SET @ SqlCmd = CONCAT (@ SqlCmd,') AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name;')

PREPARE stmt1 FROM @ SqlCmd

SET @ a = daytime

EXECUTE stmt1 USING @ a

DEALLOCATE PREPARE stmt1

LEAVE lable_exit

END lable_exit

WHILE i

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.