How to Parameterize the LIMIT Clause in MySQL Stored Procedures?

Parameterizing LIMIT Clause in MySQL Stored Procedures

Question:

In creating a paging class, you aim to pass two parameters to a MySQL stored procedure's LIMIT clause. However, attempting to use INT parameters and a query structure like the following yields an error:

<code class="sql">SELECT *
FROM
`MyTable`
LIMIT
MyFirstParamInt, MySecondParamInt</code>

Is there an alternative approach to achieve this parameterization, or is dynamic query construction and execution the only solution?

Answer:

Prior to MySQL 5.5.6:

Prior to version 5.5.6, parameterization of the LIMIT clause was not supported in MySQL stored procedures. As such, building the query dynamically and executing it was the only feasible method.

MySQL 5.5.6 and Above:

Starting from MySQL 5.5.6, it became possible to parameterize the LIMIT and OFFSET clauses using INTEGER parameters. To do this, simply pass the stored procedure parameters directly to LIMIT and OFFSET, as shown below:

<code class="sql">SELECT *
FROM
`MyTable`
LIMIT ? OFFSET ?</code>

In this example, ? represents the placeholders for the two INTEGER parameters that you intend to pass.

The above is the detailed content of How to Parameterize the LIMIT Clause in MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!