MySQL FOUND_ROWS & total rows

I'm doing the following

n = 10
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT n, 20;
SELECT FOUND_ROWS();

However, n is set by the user. Is 开发者_运维百科there a way to know that n won't go over the total # of rows without having to run the query twice?

I don't see how you could possibly accomplish your goal without running two queries. You can run a different query to count the number of results that would be returned, and then check that number against the users value.

Very little SQL knowledge in general here, but I'll attempt to help.

What happens if it is over the boundary? Do you just select the last 20? I'm not sure. From what it looks like on the MySQL reference, the LIMIT x, y means results start at the xth value returned and returns y records (x and the y-1 records following). So it would seem you need to check and make sure that n isn't greater than your count - 20.

DECLARE @blah INT;

IF (n <= (SELECT Count(*) FROM tbl_name) - 20) THEN SET @blah = n;
ELSE SET @blah = (SELECT Count(*) FROM tbl_name) - 20;


SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT @blah, 20; 
    SELECT FOUND_ROWS();

END IF