Selecting all records using SQL LIMIT and OFFSET query
Solution 1
From the MySQL documentation:
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
So getting all rows might look as follows:
SELECT * FROM tbl LIMIT 0,18446744073709551615;
Solution 2
Yes, it is possible by providing NULL
:
SELECT * FROM tab LIMIT NULL OFFSET NULL
LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument.
The values NULL, empty string (''), and $$$$ are also accepted and are treated as “unlimited”; this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.
SELECT * FROM demo1 ORDER BY i LIMIT NULL OFFSET NULL; SELECT * FROM demo1 ORDER BY i LIMIT '' OFFSET ''; SELECT * FROM demo1 ORDER BY i LIMIT $$$$ OFFSET $$$$;
Solution 3
This may not be the best way to do it, but its the first that comes to mind...
SELECT * FROM myTable LIMIT 0,1000000
Replace 1000000 with some adequately large number that you know will always be larger than the total number of records in the table.
Solution 4
I used this code in nodeJS with MySQL and it's run well, It's may help you. Why you use it?
- It's reliable because it's a string that will append with query.
If you want to set limit then you can put the limitation with the variable otherwise pass 0 with variable.
var noOfGroupShow=0; //0: all, rest according to number if (noOfGroupShow == 0) { noOfGroupShow = ''; } else { noOfGroupShow = ' LIMIT 0, '+noOfGroupShow; } var sqlGetUser = "SELECT `user_name`,`first_name`,`last_name`,`image`,`latitude`, `longitude`,`phone`,`gender`,`country`,`status_message`,`dob` as user_date_of_birth FROM `tb_user` WHERE `user_id`=?"+noOfGroupShow;
Solution 5
As the record will grow up, use mysql_num_rows
to dynamically find total amount of records, instead of using some large number
.
$cektotalrec=mysql_query("SELECT * FROM TABLE");
$numoffset=mysql_num_rows($cektotalrec);
$numlimit="0";
then:
$final="SELECT * FROM table ".$numlimit.", ".$numoffset"";
Lior Elrom
I'm a software engineer who like to learn and explore new technologies and not afraid to ask questions when needed.
Updated on August 05, 2020Comments
-
Lior Elrom over 3 years
I wonder if there is a way to accomplish:
SELECT * FROM table
by using
LIMIT
andOFFSET
like so:SELECT * FROM table LIMIT all OFFSET 0
Can I write SQL statement using LIMIT and OFFSET but still getting ALL result?
* of course I can use anIF
statement but I rather avoid it if possible -
d'alar'cop about 11 years"some large number" - MySQL... terrible
-
sroes about 11 years18446744073709551615 isn't just some number though; it's the max value of a bigint
-
d'alar'cop about 11 yearsYes I thought it would be something of the sort but they should really say that instead of "some large number"... or no?
-
Lior Elrom about 11 yearsOf course that is a reasonable approach however my goal was to avoid if by writing one line of code: $this->db->get($database, $limit, $offset)
-
Lior Elrom about 11 yearsThx for putting your time.
-
twicejr over 10 yearsI agree. Something like MAX_INT_NUMBER, which could then change depending on the amount of bits in the system processor in the future.
-
Peter over 8 yearswow why would you use defines instead variables in code like this, its crazy
-
user1298923 over 4 yearsPHP will cast a variable containing '18446744073709551615' to type FLOAT, causing the retrieval of only 1 result. Using PHP 7.3.12.
-
Crasher over 3 yearsThis is not a reliable solution.