Mysql 使用 Limit 使用占位符动态构建查询，如何在存储过程中创建它

Question

我想在我的存储过程中实现自定义分页，因为我使用了限制，当我在 MY SQL 中作为静态执行查询时它工作正常

示例选择查询

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
WHERE FID.FlightInstanceId > 6
GROUP BY FID.FlightInstanceId 
LIMIT 1, 5;  

但现在我想使用参数动态分页，因此我使用

SET @skip=skip; SET @numrows=numrows;
SET @ID = ID;
PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > 6
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows; 

仍在工作并获取数据

当我创建此查询作为存储过程时，它会成功执行，但没有给出任何结果

DELIMITER $$

USE `mydatabase`$$

DROP PROCEDURE IF EXISTS `SP_Temp`$$

CREATE DEFINER=`mydatabase`@`%` PROCEDURE `SP_Temp`(
    skip INT,
    numrows INT,
    ID INT
)
BEGIN
DECLARE skip INT;
DECLARE numrows INT;
DECLARE ID INT;
SET @skip=skip; SET @numrows=numrows;
SET @ID = ID;
PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > @Id
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

END$$

DELIMITER ;

调用

CALL SP_Temp(1,5,40)

我的问题是，当我调用这个存储过程时，它没有给出任何结果。我哪里出错了？我想使用存储过程。

Answer 1

@Id 还必须使用占位符。

尝试:

PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > ?
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @Id, @skip, @numrows;

在上面的查询中我替换了这一行:

where FID.FlightInstanceId > @Id

这个:

where FID.FlightInstanceId > ?