Why doesn't MySQL have an explicit "No Limit" option for queries?

Question

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;

• GrandmasterB: 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.

Answer 1

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.

• crmpicco: 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.
• mgw854: 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.