mysql - Sql Query 中的错误 - 无法找到解决方案

Question

我很难找到我的 sql 查询中的错误。

CREATE FUNCTION freeSeats(bookingID INT)
RETURNS VARCHAR(30)
BEGIN
DECLARE numberBooked INT ;
DECLARE status VARCHAR(30);

SELECT count(*) FROM passenger WHERE Booking IN(SELECT Id FROM booking WHERE
Flight = (SELECT Flight FROM booking WHERE Id = bookingID)) INTO numberBooked;

IF (numberBooked > 59) THEN SET status =”No free seats”;
ELSE SET status =”OK”;
END IF;

RETURN status;
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 'free seats”; ELSE SET status =”OK”; END IF;

RETURN status; END' at line 10

我会很感激一些帮助。

提前致谢

Answer 1

• 不要更改 DELIMITER
• 使用单引号

例子，

DELIMITER $$
CREATE FUNCTION freeSeats(bookingID INT)
RETURNS VARCHAR(30)
BEGIN
    DECLARE numberBooked INT ;
    DECLARE status VARCHAR(30);

    SET numberBooked =
    (
    SELECT count(*) 
    FROM passenger 
    WHERE Booking IN 
        (   SELECT  Id 
            FROM    booking 
            WHERE   Flight = (SELECT Flight FROM booking WHERE Id = bookingID)
        ) 
    );

    IF (numberBooked > 59) THEN 
        SET status = 'No free seats';
    ELSE 
        SET status = 'OK';
    END IF;

    RETURN status;
END $$
DELIMITER ;