MySQL 使用 SQL LIMIT 和 OFFSET 查询选择所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15950871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting all records using SQL LIMIT and OFFSET query
提问by Lior Elrom
I wonder if there is a way to accomplish:
我想知道是否有办法实现：
SELECT * FROM table
by using LIMITand OFFSETlike so:
通过使用LIMIT和OFFSET喜欢这样：
SELECT * FROM table LIMIT all OFFSET 0
Can I write SQL statement usingLIMIT and OFFSET but still getting ALL result?
* of course I can use an IFstatement but I rather avoid it if possible
我可以使用LIMIT 和 OFFSET编写 SQL 语句但仍然得到所有结果吗？
*当然我可以使用IF声明，但如果可能的话我宁愿避免它
回答by sroes
From the MySQL documentation:
从MySQL 文档：
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;
要检索从某个偏移量到结果集末尾的所有行，您可以为第二个参数使用一些大数字。此语句检索从第 96 行到最后一行的所有行：
SELECT * FROM tbl LIMIT 95,18446744073709551615;
So getting all rows might look as follows:
因此，获取所有行可能如下所示：
SELECT * FROM tbl LIMIT 0,18446744073709551615;
回答by Pritam Jana
I used this code in nodeJS with MySQL and it's run well, It's may help you. Why you use it?
我在 nodeJS 和 MySQL 中使用了这段代码，它运行良好，它可能对你有帮助。你为什么使用它？
- 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;
- 它是可靠的，因为它是一个将附加查询的字符串。
如果要设置限制，则可以将限制与变量一起放置，否则将 0 与变量一起传递。
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;
回答by Farid Rudiansyah
As the record will grow up, use mysql_num_rowsto dynamically find total amount of records, instead of using some large number.
随着记录的增长，使用mysql_num_rows动态查找记录总数，而不是使用some large number.
$cektotalrec=mysql_query("SELECT * FROM TABLE");
$numoffset=mysql_num_rows($cektotalrec);
$numlimit="0";
then:
然后：
$final="SELECT * FROM table ".$numlimit.", ".$numoffset"";
回答by Nate S.
This may not be the best way to do it, but its the first that comes to mind...
这可能不是最好的方法，但它是第一个想到的......
SELECT * FROM myTable LIMIT 0,1000000
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.
将 1000000 替换为某个足够大的数字，您知道该数字将始终大于表中的记录总数。
回答by AgeDeO
Maybe not the cleanest solution but setting limit to a very high number could work. Offset needs to be 0.
也许不是最干净的解决方案，但将限制设置为非常高的数字是可行的。偏移量需要为 0。
Why not use a IFstatement where you add the limit and offset to the query as a statement is true?
为什么不使用IF在查询中添加限制和偏移量的语句，因为语句为真？
回答by Lesly Revenge
You might receive an error if you set the limit to a very high number as defined by mysql doc. Thereofre, you should try to limit it 9999999999999, going higher can give you an error unless you set up server to go higher.
You might want to use LIMIT in a function, therefore it is not a bad idea. If you use it in a function, you might want it to be Limit All at one point and limit 1 at another point.
Below, I list an example where you might want your application to have no limit.
如果您将限制设置为 mysql doc 定义的非常高的数字，您可能会收到错误消息。因此，您应该尝试将其限制为 9999999999999，除非您将服务器设置为更高，否则更高可能会给您带来错误。
您可能希望在函数中使用 LIMIT，因此这不是一个坏主意。如果您在函数中使用它，您可能希望它在某一点限制所有，而在另一点限制 1。
下面，我列出了一个示例，您可能希望您的应用程序没有限制。
function get_navigation($select = "*", $from= "pages", $visible= 1, $subject_id = 2, $order_by = "position", $sort_by = "asc", $offset=0, $limit = 9551615){ global $connection;
function get_navigation($select = "*", $from="pages", $visible= 1, $subject_id = 2, $order_by = "position", $sort_by = "asc", $offset=0, $limit = 9551615 ){ 全局 $connection;
$query = " SELECT {$select} ";
$query .= " FROM {$from} ";
$query .= " WHERE visible = {$visible} ";
$query .= " AND subject_id = {$subject_id} ";
$query .= " ORDER BY {$order_by} {$sort_by} ";
$query .= " LIMIT {$offset}, {$limit} ";
mysqli_query($connection, $query);
$navigation_set = mysqli_query($connection, $query);
confirm_query($navigation_set);
return $navigation_set;
}
define ("SELECT", "*");
define ("FROM", "pages");
define ("VISIBLE", 1);
define ("SUBJECT_ID", 3);
define ("ORDER_BY", "position");
define ("SORT_BY", "ASC");
define ("LIMIT", "0");
$navigation_set = get_navigation(SELECT, FROM, VISIBLE, SUBJECT_ID, ORDER_BY, SORT_BY);
