MySQL SQL_SELECT_LIMIT

I came across a MySQL setting recently called SQL_SELECT_LIMIT which limits the maximum number of rows returned from a SQL query without having to specify the limit in the query. It applies the limit to all queries from the current connection until reset or changed to a different value.

Use of SQL_SELECT_LIMIT

I’m not exactly sure when you might use SQL_SELECT_LIMIT but it may be a useful setting so I’m covering it here in case it could benefit you, or if I may need it at some future time.

How it works

For example, to change queries so they only ever return 1 row run this SQL query:

SET SQL_SELECT_LIMIT = 1;

If the following query is then run against my example MySQL fruit table:

SELECT * FROM fruit;

it would return this, even though there are many more rows in the table:

+----------+-------+---------------+
| fruit_id | name  | variety       |
+----------+-------+---------------+
|        1 | Apple | Red Delicious |
+----------+-------+---------------+

Reset SQL_SELECT_LIMIT to the default

To reset SQL_SELECT_LIMIT back to the default behavior, which is to return all rows (or just the number of rows specified by LIMIT) do this:

SET SQL_SELECT_LIMIT = DEFAULT;

This will now return all rows for the above example SELECT * FROM fruit query.

LIMIT X overrides the SQL_SELECT_LIMIT limit

If a limit is specified in the SQL query then it will will that number of rows and not limit it to the amount specified by SQL_SELECT_LIMIT. For example:

SET SQL_SELECT_LIMIT = 1;
SELECT * FROM fruit LIMIT 3;

will return:

+----------+--------+---------------+
| fruit_id | name   | variety       |
+----------+--------+---------------+
|        1 | Apple  | Red Delicious |
|        2 | Pear   | Comice        |
|        3 | Orange | Navel         |
+----------+--------+---------------+