MySQL触发器中IF条件判断问题

2020/03/12 16:52
阅读数 52

前几天修改了一张回答表的触发器,结果保存草稿的时候报错:Data truncation: Truncated incorrect datetime value: '',经排查是触发器中一条判断的问题,我们来看原来的语句:

IF CONCAT(new.delete_flag,new.status)!=CONCAT(old.delete_flag,old.status) AND new.delete_flag=0 AND new.status=0 AND IFNULL(new.publish_time,'')!='' AND new.publish_time>DATE_ADD(NOW(),INTERVAL -30 DAY) THEN
    INSERT IGNORE INTO kid_follow_feed(receiver,operator,type,content_type,content_id,time) VALUES(new.user_id,new.user_id,3,3,new.id,new.publish_time);
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',3,'contentType',3,'contentId',new.id)));
END IF;

原来的语句中先判断状态是否有变化,如果没有变化,再判断是否发布状态并且有发布时间,同时发布时间要在30天之内,结果这条语句就报错了,原因是保存草稿的时候发布时间是空字符串,修改成这样就没问题了:

  IF new.delete_flag=0 AND new.status=0 AND IFNULL(new.publish_time,'')!='' THEN
    IF CONCAT(new.delete_flag,new.status)!=CONCAT(old.delete_flag,old.status) AND new.publish_time>DATE_ADD(NOW(),INTERVAL -30 DAY) THEN
      INSERT IGNORE INTO kid_follow_feed(receiver,operator,type,content_type,content_id,time) VALUES(new.user_id,new.user_id,3,3,new.id,new.publish_time);
      SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',3,'contentType',3,'contentId',new.id)));
    END IF;
  END IF;
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部