Using variable in a LIMIT clause in MySQL
MysqlLimitClauseVariablesMysql Problem Overview
I am writing a stored procedure where I have an input parameter called my_size that is an INTEGER. I want to be able to use it in a LIMIT
clause in a SELECT
statement. Apparently this is not supported, is there a way to work around this?
# I want something like:
SELECT * FROM some_table LIMIT my_size;
# Instead of hardcoding a permanent limit:
SELECT * FROM some_table LIMIT 100;
Mysql Solutions
Solution 1 - Mysql
For those, who cannot use MySQL 5.5.6+ and don't want to write a stored procedure, there is another variant. We can add where clause on a subselect with ROWNUM.
SET @limit = 10;
SELECT * FROM (
SELECT instances.*,
@rownum := @rownum + 1 AS rank
FROM instances,
(SELECT @rownum := 0) r
) d WHERE rank < @limit;
Solution 2 - Mysql
STORED PROCEDURE
DELIMITER $
create PROCEDURE get_users(page_from INT, page_size INT)
begin
SET @_page_from = page_from;
SET @_page_size = page_size;
PREPARE stmt FROM "select u.user_id, u.firstname, u.lastname from users u limit ?, ?;";
EXECUTE stmt USING @_page_from, @_page_size;
DEALLOCATE PREPARE stmt;
end$
DELIMITER ;
USAGE
call get_users(1, 10);
Solution 3 - Mysql
A search turned up this article. I've pasted the relevant text below.
> Here's a forum post showing an example of prepared statements letting > you assign a variable value to the limit clause: > > http://forums.mysql.com/read.php?98,126379,133966#msg-133966 > > However, I think this bug should get some attention because I can't > imagine that prepared statements within a procedure will allow for any > procedure-compile-time optimizations. I have a feeling that prepared > statements are compiled and executed at the runtime of the procedure, > which probaby has a negative impact on efficiency. If the limit > clause could accept normal procedure variables (say, a procedure > argument), then the database could still perform compile-time > optimizations on the rest of the query, within the procedure. This > would likely yield faster execution of the procedure. I'm no expert > though.
Solution 4 - Mysql
I know this answer has come late, but try SQL_SELECT_LIMIT.
Example:
Declare rowCount int;
Set rowCount = 100;
Set SQL_SELECT_LIMIT = rowCount;
Select blah blah
Set SQL_SELECT_LIMIT = Default;
Solution 5 - Mysql
This feature has been added to MySQL 5.5.6. Check this link out.
I've upgraded to MySQL 5.5 just for this feature and works great. 5.5 also has a lot of performance upgrades in place and I totally recommend it.
Solution 6 - Mysql
Another way, the same as wrote "Pradeep Sanjaya", but using CONCAT:
CREATE PROCEDURE `some_func`(startIndex INT, countNum INT)
READS SQL DATA
COMMENT 'example'
BEGIN
SET @asd = CONCAT('SELECT `id` FROM `table` LIMIT ',startIndex,',',countNum);
PREPARE zxc FROM @asd;
EXECUTE zxc;
END;
Solution 7 - Mysql
As of MySQL version 5.5.6, you can specify LIMIT
and OFFSET
with variables / parameters.
For reference, see the 5.5 Manual, the 5.6 Manual and @Quassnoi's answer
Solution 8 - Mysql
I've faced the same problem using MySql 5.0 and wrote a procedure with the help of @ENargit's answer:
CREATE PROCEDURE SOME_PROCEDURE_NAME(IN _length INT, IN _start INT)
BEGIN
SET _start = (SELECT COALESCE(_start, 0));
SET _length = (SELECT COALESCE(_length, 999999)); -- USING ~0 GIVES OUT OF RANGE ERROR
SET @row_num_personalized_variable = 0;
SELECT
*,
@row_num_personalized_variable AS records_total
FROM(
SELECT
*,
(@row_num_personalized_variable := @row_num_personalized_variable + 1) AS row_num
FROM some_table
) tb
WHERE row_num > _start AND row_num <= (_start + _length);
END;
Also included the total rows obtained by the query with records_total.
Solution 9 - Mysql
you must DECLARE a variable and after that set it. then the LIMIt will work and put it in a StoredProcedure not sure if it works in normal query
like this:
DECLARE rowsNr INT DEFAULT 0;
SET rowsNr = 15;
SELECT * FROM Table WHERE ... LIMIT rowsNr;