Can I use MySQL functions in the LIMIT offset

Question

Can I use MySQL functions in the LIMIT offset? Like:

SELECT * FROM sites WHERE ... LIMIT FLOOR(1 + RAND() * (SELECT COUNT(*) FROM sites)) , 1

Answer 1

No, you can't do that directly. LIMIT and OFFSET values must be constants.

Citation from the MySQL docs:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

You can use prepared statements and variables, though:

SELECT @offset:=FLOOR(1 + RAND() * COUNT(*)) FROM sites;
PREPARE STMT FROM 'SELECT * FROM sites WHERE ... LIMIT ?, 1';
EXECUTE STMT USING @offset;