mysql触发器与定时

 

一、创建触发器限制数字范围

示例一：
    CREATE TRIGGER trg_t_task_typenum_insert_check BEFORE INSERT
    ON t_task_typenum FOR EACH ROW
    BEGIN
     DECLARE msg varchar(100);
     
     IF NEW.f_browse_count <= 0 OR NEW.f_browse_count > 1000
     THEN
      SET msg = CONCAT('您输入的值：',NEW.f_advance_count,' 为无效的，请输入0到1000以内的有效数字。');
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
   ELSEIF NEW.f_collectcart_count <= 0 OR NEW.f_collectcart_count > 1000
   THEN
    SET msg = CONCAT('您输入的值：',NEW.f_collectcart_count,' 为无效的，请输入0到1000以内的有效数字。');
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
     END IF;
    END;
 
示例二：
  CREATE TRIGGER trg_t_task_info_insert_check BEFORE INSERT
    ON t_task_info FOR EACH ROW
    BEGIN
     DECLARE msg varchar(100);
     
     IF NEW.f_task_publish_num <= 0 OR NEW.f_task_publish_num > 200
     THEN
      SET msg = CONCAT('您输入的值：',NEW.f_task_publish_num,' 为无效的，请输入0到200以内的有效数字');
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
     END IF;
    END;

 

 

二、创建定时

查询计划事件的状态：SHOW VARIABLES LIKE 'event_scheduler';

如果返回的是off表示当前是关闭状态，如果是on当前已经开启了计划任务

设置状态为开启：SET GLOBAL event_scheduler = ON;

查看当前定时：select * from mysql.event;

 

示例：

DELIMITER $$
DROP EVENT IF EXISTS deleteLog;
CREATE EVENT deleteLog
ON SCHEDULE EVERY 10 SECOND
ON COMPLETION PRESERVE
DO BEGIN
delete from sys_log limit 10;
END$$
DELIMITER;

 

1、
DROP event IF EXISTS e_delete_sys_log;
CREATE EVENT e_delete_sys_log
ON SCHEDULE
EVERY 1 DAY STARTS '2020-10-30 03:00:00'
DO
DELETE FROM sys_log WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 9 DAY)

2、
DROP event IF EXISTS e_delete_sys_log;
CREATE EVENT e_delete_sys_log
ON SCHEDULE
EVERY 1 DAY STARTS '2020-12-16 11:30:00'
DO
DELETE FROM sys_log WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 10 DAY);

3、
DROP event IF EXISTS e_delete_task_distribution_log;
CREATE EVENT e_delete_task_distribution_log
ON SCHEDULE
EVERY 1 DAY STARTS '2020-12-18 04:30:00'
DO
DELETE FROM t_task_distribution_log WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 10 DAY);