无法导入MYSQL函数-错误1064 (42000)

Question

我正在试图理解为什么下面的失败，我无法看到它。我们开发服务器上的phpMyAdmin正是这样生成的，但是当我尝试导入它时，我遇到了一个奇怪的解析(？)错误。

mysql> DELIMITER $$
mysql>
mysql> --
mysql> -- Functions
mysql> --
mysql> DROP FUNCTION IF EXISTS `get_class_nextsession`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DEFINER=`myusername`@`localhost` FUNCTION `get_class_nextsession`(class_id INT) RETURNS datetime
    ->     READS SQL DATA
    -> BEGIN
    -> DECLARE nextsession,startdate,runningdate,enddate DATETIME;
    -> SET nextsession= '-';
    -> SELECT  MIN(CS.start_datetime) INTO startdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id Group by C.id Limit 0,1;
    -> IF startdate >= NOW() THEN
    -> RETURN startdate;
    -> ELSE
    -> SELECT  CS.start_datetime INTO runningdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id and (NOW() between start_datetime and end_datetime ) Limit 0,1;
    -> IF runningdate IS NOT NULL THEN
    -> RETURN runningdate;
    -> ELSE
    -> SELECT  MAX(CS.end_datetime) INTO enddate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id Group by C.id Limit 0,1;
    -> IF enddate < NOW() THEN
    -> RETURN enddate;
    -> ELSEIF enddate >= NOW() THEN
    -> SELECT  MIN(CS.start_datetime) INTO startdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id and CS.start_datetime > NOW() Group by C.id Limit 0,1;
    ->
Display all 1866 possibilities? (y or n)
    ->  startdate;
    ->
Display all 1866 possibilities? (y or n)
    -> D IF;
    -> END IF;
    -> END IF;
    -> RETURN nextsession;
    -> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
D IF;
END IF;
END IF;
RETURN nextsession;
END' at line 19
mysql>
mysql>

最新进展情况1

这起作用了

mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `get_class_nextsession`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DEFINER=`myusername`@`localhost` FUNCTION `get_class_nextsession`(class_id INT) RETURNS datetime
    ->     READS SQL DATA
    -> BEGIN
    -> RETURN nextsession;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

这起作用了

mysql>
mysql> DROP FUNCTION IF EXISTS `get_class_nextsession`$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE DEFINER=`myusername`@`localhost` FUNCTION `get_class_nextsession`(class_id INT) RETURNS datetime
    ->     READS SQL DATA
    -> BEGIN
    -> DECLARE nextsession,startdate,runningdate,enddate DATETIME;
    -> RETURN nextsession;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

这是行不通的--注意我是如何将查询/语句简化为基本语句的。

mysql> DROP FUNCTION IF EXISTS `get_class_nextsession`$$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE DEFINER=`myusername`@`localhost` FUNCTION `get_class_nextsession`(class_id INT) RETURNS datetime
    ->     READS SQL DATA
    -> BEGIN
    -> DECLARE nextsession,startdate,runningdate,enddate DATETIME;
    -> SET nextsession= '-';
    -> SELECT  MIN(start_datetime) INTO startdate FROM site_class_schedule;
    -> IF startdate >= NOW() THEN
    -> RETURN startdate;
    -> ELSE
    -> END IF;
    -> RETURN nextsession;
    -> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF;
RETURN nextsession;
END' at line 10

Answer 1

啊哈！

绝对是一个“不寻常”的错误，其中‘没有’是错误的！我所面临的问题是由于过多的选项卡被生成或放置在原来的函数创建中！这两个连续的\t命令中断mysql提示符上的数据输入流，并让mysql问询。

 Display all 1866 possibilities? (y or n)

此外，当我在这里复制粘贴时，我正在粘贴与MYSQL命令提示符交互的最后输出，而不是我实际放置的“在那里”！

DROP FUNCTION IF EXISTS `get_class_nextsession`$$
CREATE DEFINER=`myusername`@`localhost` FUNCTION `get_class_nextsession`(class_id INT) RETURNS datetime
    READS SQL DATA
BEGIN
DECLARE nextsession,startdate,runningdate,enddate DATETIME;
SET nextsession= '-';
SELECT  MIN(CS.start_datetime) INTO startdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id Group by C.id Limit 0,1;
IF startdate >= NOW() THEN
RETURN startdate;
ELSE    
SELECT  CS.start_datetime INTO runningdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id and (NOW() between start_datetime and end_datetime ) Limit 0,1;
IF runningdate IS NOT NULL THEN
RETURN runningdate;
ELSE
SELECT  MAX(CS.end_datetime) INTO enddate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id Group by C.id Limit 0,1;
IF enddate < NOW() THEN
    RETURN enddate;
ELSEIF enddate >= NOW() THEN
SELECT  MIN(CS.start_datetime) INTO startdate FROM site_class_schedule AS CS INNER JOIN site_class AS C ON (C.id=CS.class_id) WHERE CS.class_id=class_id and CS.start_datetime > NOW() Group by C.id Limit 0,1;
            RETURN startdate; ****ISSUE WITH MORE THAN 2 TABS****
        END IF; ****ISSUE WITH MORE THAN 2 TABS****
    END IF;
END IF;
RETURN nextsession;
END$$