使用 MySQL LIMIT、OFFSET 进行分页？

首先，我们需要创建一个包含一些记录的表，然后我们将在以下的帮助下使用分页 限制和偏移量。

借助CREATE命令创建表。查询如下 -

mysql> CREATE table limitoffsetDemo -> ( -> id int, -> FisrtName varchar(200) -> ); Query OK, 0 rows affected (0.45 sec)登录后复制

插入记录

mysql> INSERT into limitoffsetDemo values(1,'John'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into limitoffsetDemo values(2,'Bob'); Query OK, 1 row affected (0.16 sec) mysql> INSERT into limitoffsetDemo values(3,'David'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into limitoffsetDemo values(4,'carol'); Query OK, 1 row affected (0.19 sec) mysql> INSERT into limitoffsetDemo values(5,'Taylor'); Query OK, 1 row affected (0.10 sec) mysql> INSERT into limitoffsetDemo values(1,'John'); Query OK, 1 row affected (0.24 sec) mysql> INSERT into limitoffsetDemo values(7,'Smith'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into limitoffsetDemo values(8,'John'); Query OK, 1 row affected (0.12 sec) mysql> INSERT into limitoffsetDemo values(9,'Johnson'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into limitoffsetDemo values(10,'Jason'); Query OK, 1 row affected (0.08 sec) mysql> INSERT into limitoffsetDemo values(11,'Bk'); Query OK, 1 row affected (0.17 sec)登录后复制

mysql> SELECT * from limitoffsetDemo; 登录后复制

+------+-----------+ | id | FisrtName | +------+-----------+ | 1 | John | | 2 | Bob | | 3 | David | | 4 | carol | | 5 | Taylor | | 1 | John | | 7 | Smith | | 8 | John | | 9 | Johnson | | 10 | Jason | | 11 | Bk | +------+-----------+ 11 rows in set (0.00 sec)登录后复制

SELECT * from yourTableName limit value1,value2; 登录后复制

现在，我应用上面的查询来获取结果，如下 -

mysql> SELECT * from limitoffsetDemo limit 7,4;登录后复制

+------+-----------+ | id | FirstName | +------+-----------+ | 8 | John | | 9 | Johnson | | 10 | Jason | | 11 | Bk | +------+-----------+ 4 rows in set (0.00 sec)登录后复制