如何根据数据库特定记录数返回布尔值？函数报错求助

你的函数存在几处语法错误，导致执行时报错：

• 赋值给inDB的子查询未用括号包裹
• 函数末尾缺少RETURN语句，无法返回结果
• 部分语句结尾未加分号，语法不完整
• 冗余的WHERE 1=1可直接移除

修正后的代码如下：

DELIMITER %%
CREATE FUNCTION DriverActiveInTruckByDate(
    pdriver_id INT,
    ptruck_number INT,
    pdate DATETIME
) 
RETURNS boolean
DETERMINISTIC
BEGIN
    DECLARE inDB INT DEFAULT 0;
    SET inDB = (
        SELECT IF(COUNT(*) >= 1, 1, 0)
        FROM truck_timeline tl
        WHERE tl.driver_id = pdriver_id
          AND tl.truck_number = ptruck_number
          AND ((pdate BETWEEN tl.begin_date AND tl.end_date) OR (pdate >= tl.begin_date AND tl.end_date IS NULL))
    );
    RETURN inDB;
END
%%
DELIMITER ;

另外可以简化逻辑，用EXISTS直接判断是否存在符合条件的记录，效率更高（无需统计所有符合条件的行数，找到第一条即停止）：

DELIMITER %%
CREATE FUNCTION DriverActiveInTruckByDate(
    pdriver_id INT,
    ptruck_number INT,
    pdate DATETIME
) 
RETURNS boolean
DETERMINISTIC
BEGIN
    RETURN EXISTS(
        SELECT 1
        FROM truck_timeline tl
        WHERE tl.driver_id = pdriver_id
          AND tl.truck_number = ptruck_number
          AND ((pdate BETWEEN tl.begin_date AND tl.end_date) OR (pdate >= tl.begin_date AND tl.end_date IS NULL))
    );
END
%%
DELIMITER ;

内容的提问来源于stack exchange，提问作者Theb