1

I use SQLyog Community edition and like many other SQL applications out there it puts a 1000 result limit on queries that do not have a limit provided.

I am wondering why MySQL doesn't have an explicit NO LIMIT option when it comes to writing queries.

I'm sure that i'm not the only developer that ends up sticking a large limit on the end (within reason) to get a full display view of the records in the table.

Providing the records in the table is sensible i'd like to be able to do:

SELECT * FROM course NO LIMIT; instead of SELECT * FROM COURSE LIMIT 2500;

1
  • I don't think its very reasonable to expect MySQL to change their query language because of how some tools choose to implement a feature. Apr 2, 2014 at 15:42

1 Answer 1

7

The server was probably started with the --safe-updates or --i-am-a-dummy option which causes MySQL to execute

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

on startup.

See the MySQL reference for details.

2
  • Turns out I am not running MySQL server with Safe Updates on. I done a SHOW VARIABLES LIKE 'sql_safe_updates' from the command line and it did not return any results, however I found a setting in SQLyog that limited results to 1000. I guess it still poses the question; why doesn't MySQL have a "NO LIMIT" option? You could legitimately want to ignore all limits for one specific query, but still run in Safe Update mode the rest of the time.
    – crmpicco
    Apr 2, 2014 at 15:28
  • 2
    MySQL doesn't have a NO LIMIT option because all queries run without a limit by default. If you decide to restrict that elsewhere, there's nothing MySQL could do about that.
    – mgw854
    Apr 2, 2014 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.