All geek questions in one place

Why is "LIMIT 0" allowed even in MySQL SELECT statements?

Limit 0, 1000 returns the first 1000 results, but LIMIT 0 returns 0 results.

This is not very intuitive IMHO. For example, dumb old I thought removing 1000 would remove the upper limit for the SELECT query, returning all the results.

Why does anyone even want to query MySQL for 0 results?

+6
source share
3 answers

MySQL documentation

LIMIT 0 quickly returns an empty set. This can be useful for validating a request. When using one of the MySQL APIs, it can also be used to get the column types of the results.

+21
source

* Polite corrections are welcome and appreciated if I am incorrect here, but:

I understand that LIMIT 0, 1000 tells SQL that you want to start with the first set of 1000 results in this database for these criteria. For example, if there are 10,000 result rows in a dataset, LIMIT 0, 1000 will show you the first set of 1000 results. Zero as an array index in JavaScript - the code starts the ITS count with zero, and not one, when referring to an array element. Thus, item # 1 is item # 0, item # 2 is item # 1, etc.

0
source

In addition to the answers already indicated, this is also useful if you want to do operations on a table based on the number of rows present in that table.

those. using PHP if you want to delete all entries except the one with the largest identifier from the "myTable" table:

 <?php $con = mysqli_connect("hostname", "username", "password", "database"); // Connect $totalRows = mysqli_query($con, "SELECT COUNT(*) FROM myTable"); // Get total row count $mysqli_query($con, "DELETE FROM myTable WHERE id >= 0 LIMIT ($totalRows - 1);"); // Delete ?>

This is really useful because if you have only 1 line left, you will be left with LIMIT 0 , which is what you need.

0
source

Source: https://habr.com/ru/post/987306/

More articles:


All Articles