因为MySQL目前不支持动态参数分页所以存储过程中要么传sql语句过去要么自己写个字符串连接方法
下为第二种方法
把每个需要分割的地方单写一行,清除
BEGIN
SELECT CEIL(COUNT(*)/pageNum) INTO pageCount FROM mytestdb.mttest WHERE tt_id = arg_mt_id;
SET @sqlStr = CONCAT('SELECT `mt_id`,`tt_id`,`tt_name` FROM `mytestdb`.`mttest` WHERE `tt_id` = ');
SET @sqlStr = CONCAT(@sqlStr,arg_mt_id);
SET @sqlStr = CONCAT(@sqlStr,' LIMIT ');SET @sqlStr = CONCAT(@sqlStr,((pageNum-1)*pageSize));
SET @sqlStr = CONCAT(@sqlStr,',');
SET @sqlStr = CONCAT(@sqlStr,pageSize);
SET @sqlStr = CONCAT(@sqlStr,';');
PREPARE s1 FROM @sqlStr;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END
或者写成一行,效率高但是更容易写错
BEGIN
SELECT CEIL(COUNT(*)/pageNum) INTO pageCount FROM mytestdb.mttest WHERE tt_id = arg_mt_id;
SET @sqlStr = CONCAT('SELECT `mt_id`,`tt_id`,`tt_name` FROM `mytestdb`.`mttest` WHERE `tt_id` = ',arg_mt_id,' LIMIT ',
((pageNum-1)*pageSize),',',pageSize';');
PREPARE s1 FROM @sqlStr;EXECUTE s1;DEALLOCATE PREPARE s1;END
java端