Why LIMIT and OFFSET does not work in mysql script?

Question

I'm trying the following mysql script, but it says NumOfRecords and Offsetpage not defined.

SET @NumOfRecords = 10;
SET @Offsetpage = 0;
SET @DocumentStatus = 'PENDING';
SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = @DocumentStatus ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT @NumOfRecords OFFSET @Offsetpage

Any help ?

https://stackoverflow.com/questions/245180/using-variable-in-a-limit-clause-in-mysql seems to hold the answer. — Don Dickinson, Dec 31 '18 at 17:44

score 2 · Accepted Answer · answered Dec 31 '18 at 17:56

User variables in the LIMIT clause is not supported.

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html says:

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

In other words, you must use literal integers, or else you can use a prepared statement:

PREPARE stmt FROM 'SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = ? ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT ? OFFSET ?';
EXECUTE stmt USING @DocumentStatus, @NumOfRecords, @Offsetpage;

You do not have to use a stored procedure for this, you can do prepared statements directly in any database session.

You can run prepared statements directly in most client APIs, without using the PREPARE and EXECUTE syntax. You don't have to define user variables either.

For example, in PHP:

<?php

$stmt = $pdo->prepare('SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = ? ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT ? OFFSET ?');
$stmt->execute( [ 'PENDING', 10, 0 ] );

Thank you for a detailed explanation. – PCG Dec 31 '18 at 21:02 — PCG, Dec 31 '18 at 21:02

Why LIMIT and OFFSET does not work in mysql script?

1 Answers1