mysql - 创建 mysql 过程时出现错误 1064

Question

我正在 HeidiSql 中创建 mySql 过程:

   CREATE DEFINER=root@localhost PROCEDURE checkSchedule
 ( IN sDate date, IN eDate date, IN sTime time, IN eTime time, IN weekDay int(7), IN classId int(11) )
   BEGIN 
   select schedule.idSchedule 
       from schedule 
          where ( (sDate>=schedule.startDate and sDate<=schedule.endDate) 
       or (sDate<=schedule.startDate and eDate>=schedule.endDate) 
       or (eDate>=schedule.startDate and eDate<=schedule.endDate) ) 
       and ( (sTime>=schedule.startTime and sTime<=schedule.endTime) 
       or (sTime<=schedule.startTime and eTime>=schedule.endTime) 
       or (eTime>=schedule.startTime and eTime<=schedule.endTime) ) 
       and weekDay=schedule.weekDay and classId=schedule.classroom_idClassRoom; 
    END

我收到下一个错误:

    SQL Error (1064): check the manual corresponds your MariaDB server 
    version for the right syntax to use near '' at line 12

有什么想法可以解决这个问题吗？

Answer 1

您需要临时更改 DELIMITER 才能执行该过程。默认DELIMITER是;，但是在创建过程时使用;，所以它会产生问题。

DELIMITER $$
CREATE DEFINER=root@localhost PROCEDURE checkSchedule
 ( IN sDate date, IN eDate date, IN sTime time, IN eTime time, IN weekDay int(7), IN classId int(11) )
   BEGIN 
   select schedule.idSchedule 
       from schedule 
          where ( (sDate>=schedule.startDate and sDate<=schedule.endDate) 
       or (sDate<=schedule.startDate and eDate>=schedule.endDate) 
       or (eDate>=schedule.startDate and eDate<=schedule.endDate) ) 
       and ( (sTime>=schedule.startTime and sTime<=schedule.endTime) 
       or (sTime<=schedule.startTime and eTime>=schedule.endTime) 
       or (eTime>=schedule.startTime and eTime<=schedule.endTime) ) 
       and weekDay=schedule.weekDay and classId=schedule.classroom_idClassRoom; 
    END$$

 DELIMITER ;

DEMO