Can I use MySQL functions in the LIMIT offset

问题:

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

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

回答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;