MySQL limit with variable

Question

I have an error in my syntax :

SET @start := 0;
SELECT (ROUND((count(item))/2)) FROM car INTO @until; 

SELECT * FROM car
LIMIT @until OFFSET @start;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@until OFFSET @start' at line 1

Anyone can help me?
Thanks

Answer 1

You cannot use a user-defined variable (@until) in the LIMIT clause.

A possible solution (a variation on this):

SELECT (ROUND((count(item))/2)) FROM car INTO @until;
SELECT * FROM (
  SELECT *, 
         @rownum := @rownum + 1 AS rank
    FROM car, 
         (SELECT @rownum := 0) r
) d WHERE rank < @until;

Only downside is you lose the offset, but you can accommodate that by adjusting the WHERE clause. Otherwise, you can use a stored procedure.