Using variable in a LIMIT clause in MySQL

mysql limit clause variables
MPX picture MPX · Oct 28, 2008 · Viewed 61.7k times · Source

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;

Answer

ENargit picture ENargit · Jun 12, 2013

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;

Related questions

Maximum number of records in a MySQL database table

What is the upper limit of records for MySQL database table. I'm wondering about autoincrement field. What would happen if I add milions of records? How to handle this kind of situations? Thx!

Read More
MySQL - UPDATE query with LIMIT

I want to update rows in my table with starting from 1001 to next 1000. I tried with following query: UPDATE `oltp_db`.`users` SET p_id = 3 LIMIT 1001, 1000 This is giving me syntax error. Is this correct? am I doing any mistake …

Read More
Pagination using MySQL LIMIT, OFFSET

I have some code that LIMITs data to display only 4 items per page. The column I'm using has about 20-30 items, so I need to make those spread out across the pages. On the first page, I have: $result = mysqli_…

Read More