Mysql 触发器

CREATE TRIGGER insertToTbdaylogger AFTER INSERT ON tbtaskflow FOR EACH ROW BEGIN DECLARE i_temp int; select count(*) into i_temp from tbdaylogger where insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND) and insertTimes < NOW() and taskid=NEW.taskid; if i_temp = 0 then insert into tbdaylogger(taskid,totalSuccessNum,totalSuccessFlow,totalErrorFlow,totalErrorNum) values(NEW.taskid,NEW.totalSuccessNum,NEW.totalSuccessFlow,NEW.totalErrorFlow,NEW.totalErrorNum); else update tbdaylogger set totalSuccessNum=CAST(totalSuccessNum AS UNSIGNED)+CAST(NEW.totalSuccessNum AS UNSIGNED), totalSuccessFlow=CAST(totalSuccessFlow AS UNSIGNED) + CAST(NEW.totalSuccessFlow AS UNSIGNED), totalErrorFlow= CAST(totalErrorFlow AS UNSIGNED)+CAST(NEW.totalErrorFlow AS UNSIGNED), totalErrorNum=CAST(totalErrorNum AS UNSIGNED)+CAST(NEW.totalErrorNum AS UNSIGNED) where insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND) and insertTimes <NOW() and taskid=NEW.taskid; end if; END;

查询当前小时内共有的记录数

select count(*) into i_temp from tbdaylogger where insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND) and insertTimes < NOW()

CONCAT(str1,str2)链接字符串

DATE_SUB(NOW(),INTERVAL 1 DAY)

向前查询一天内的时间 即为当前时间向前一天

CAST(totalSuccessNum AS UNSIGNED)

转换成数字相加