What does using a negative value for MySQL LIMIT do?

mysql5
b01 picture b01 · Mar 24, 2012 · Viewed 7.3k times · Source

So what happens when you use a negative value in a LIMIT clause? Will there be negative consequences (slight pun intended)?

I don't see any documentation on this, and MySQL forums, as I recall, sux.

Background: I'm using MySQL CE 5.5.20. I wrote a stored procedure that take an int parameter and uses it for the LIMIT clause. I needed a way to return all rows if I wanted, so I tried passing in -1 for the limit parameter in my store procedure/routine, and that worked.

Answer

wmarbut picture wmarbut · Sep 19, 2012

According to the documentation, it must be a non-negative integer

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).

It sounds to me like what you are experiencing is an undocumented bug, in that mysql should throw an error if the parameters are not inline with what the documentation requires.

My suggestion would be to use syntactically corrected SQL in your stored procedure. You could still use a negative int as a flag to show all though. Something like this

IF myparam >= 0 THEN
    SELECT * FROM `mytable` LIMIT myparam;
ELSE
    SELECT * FROM `mytable`;
END IF;

Related questions

How to get a list of user accounts using the command line in MySQL?

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this? I'm using MySQL version 5.4.1.

Read More
Can't connect to MySQL server on 'localhost' (10061)

I recently installed MySQL 5 on Windows 2003 and tried configuring an instance. Everything worked fine until I got to "Applying Security settings", at which point it gave me the above error (Can't connect to MySQL server on 'localhost' (10061)). I do have …

Read More
MySQL 1062 - Duplicate entry '0' for key 'PRIMARY'

I have the following table in MySQL version 5.5.24 DROP TABLE IF EXISTS `momento_distribution`; CREATE TABLE IF NOT EXISTS `momento_distribution` ( `momento_id` INT(11) NOT NULL, `momento_idmember` INT(11) NOT NULL, `created_at` DATETIME DEFAULT NULL, `updated_at` DATETIME DEFAULT …

Read More