Trying to use limit with variable in MySQL

Question

I am trying this code to use LIMIT in MYSQL with a variable. When I use a simple number for LIMIT it works fine but when I use a variable then it's not working.

SET @increment:=9;
SET @number:=1;
SET @end:=10*@number;
SET @start:=@end-@increment;

SELECT name, detail FROM tab1 LIMIT @start, @end;

When I use the below code (no variable) it works fine:

SELECT name, detail FROM tab1 LIMIT 0,10;

informatik01 · Accepted Answer · 2021-07-21 01:25:37Z

4

I'm using in this way. Maybe it'll help you.

SET @increment := 4;
SET @number := 1;
SET @end := 5 * @number;
SET @start := @end - @increment;


PREPARE stmt FROM 'SELECT @i := total, user FROM logintable LIMIT ?,?';
EXECUTE stmt USING @start, @end;

SELECT @i AS result;

edited Jul 21, 2021 at 1:25

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Oct 25, 2015 at 12:08

Gurkan Yesilyurt

2,6752 gold badges24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Constantin Over a year ago

This should be the accepted answer as it is the only way to achieve it.

informatik01 Over a year ago

@Constantin Actually there is another way too: instead of using user-defined variables, you can declare local variables using DECLARE statement. Variables declared in such way can be used with the LIMIT clause.

Phil Cooper · Accepted Answer · 2015-10-25 11:22:12Z

0

The values need to be constant.

According to the spec:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants.

https://dev.mysql.com/doc/refman/5.5/en/select.html

answered Oct 25, 2015 at 11:22

Phil Cooper

3,1231 gold badge43 silver badges63 bronze badges

1 Comment

Sheraz Over a year ago

then how to get the specific record from database through variable

Collectives™ on Stack Overflow

Trying to use limit with variable in MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related