如何在MySQL中从给定的行号开始选择行?

Say I have 50 rows in a MySQL table. I want to select the first ten (LIMIT 10), but then I want to be able to select the next 10 on a different page.

假设MySQL表中有50行。我想要选择前10个(限制10)，然后我想要能够在另一个页面上选择下10个。

So how do I start my selection, after row 10?

那么如何在第10行之后开始我的选择呢?

Updated query:

更新查询:

mysql_query("
    SELECT * FROM `picdb`
    WHERE `username` = '$username'
    ORDER BY `picid` DESC
    LIMIT '$start','$count'
")

4 个解决方案

#1

I recommend working by obtaining the first page using:

我推荐使用以下的第一页:

LIMIT 0, 10

then for the second page

然后是第二页

LIMIT 10, 10

then

然后

LIMIT 20, 10

for the third page, and so on.

第三页，等等。

#2

LIMIT 10

LIMIT 10 OFFSET 10

From the MySQL 5.1 docs on SELECT syntax:

从MySQL 5.1文档中选择语法:

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

为了与PostgreSQL兼容，MySQL还支持限制row_count偏移量语法。

#3

This question is old but i just want to add a code that is not hardcoded, the answer chaos gave means you'll have to hardcode your scripts(Select statement). you can achieve the same results by getting the file name and then select data from the database based on the current page, without hardcoding your select statement. first get the current page

这个问题由来已久，但我只想添加一个非硬编码的代码，混乱的答案意味着您必须硬编码脚本(Select语句)。您可以通过获取文件名并基于当前页面从数据库中选择数据来获得相同的结果，而无需硬编码select语句。首先获取当前页面。

$page = basename($_SERVER['SCRIPT_FILENAME']);
$page_counter = rtrim($page, ".php");
//setting your limit
$start = 0;
$limit = 10;
//if current page is not index.php then $start = ($limit * page_counter); 
// e.g if current page is 1.php then $start = ($limit * 1) = 10
//if current page is 2.php then $start = ($limit * 2) = 20
if ($page !== 'index.php') {

 $start = ($limit * $page_counter);
}
//getting row count
$ROW_COUNT = $db->query('SELECT * from tableName')->rowCount();

//getting number of rows left in the table
$rows_left = ("SELECT * FROM tableName limit ?,?");
$rows_left = $db->prepare($rows_left);
$rows_left->execute(array($start,$ROW_COUNT));
$rows = $rows_left->fetchAll(PDO::FETCH_ASSOC);

$number_rows = 0;
foreach ($rows as $r) {
 $number_rows = $number_rows + 1;
 }
 //if number of rows left in the table is less than 10 then $limit = the number of rows left
 if ($number_rows < 10) {
 $limit = $number_rows;
 }

 //getting all rows
            $getRows = "SELECT * FROM tableName limit ?,?";
            $getRows = $db->prepare($getRows);
            $getRows->execute(array($start , $limit));
            $getRows = $getRows->fetchAll(PDO::FETCH_ASSOC);

#4

select * from 'table_name' 
ORDER BY 'column_id 'DESC 
LIMIT 0,10;

select * from 'table_name' 
ORDER BY 'column_id' DESC 
LIMIT 10,10;

select * from 'table_name' 
ORDER BY 'column_id' DESC
LIMIT 20,10;

and continue till the numbers you want.

继续到你想要的数字。

#1