I want to achieve the below 2 scenarios in a single query. (Note- This is just for reference, the actual query is different)

 1. SELECT * FROM CUSTOMER.CUSTOMER LIMIT :startingRow, rowsCount; //WITH LIMIT
 2. SELECT * FROM CUSTOMER.CUSTOMER; // NO LIMIT

Is it possible to write a single conditional query for this? If I pass starting row and rows count params it should go for 1st condition and if no input params are passed, it should give me all records from a table.

CodePudding user response：

The MySQL manual gives a tip for this:

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

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

The very large value used in this example is 2⁶⁴-1, or the greatest value of BIGINT UNSIGNED. There are certainly a smaller number of rows in your table.

In your case, you could use 0 as the default offset and a very large value like that as the default limit.

Speaking for myself, I would just run two different queries. One with a LIMIT clause, and the other with no LIMIT clause. Use some kind of if/then/else structure in your client code to determine which query to run, based on whether the function has specified the limit parameters or not.