MySQL中LIMIT使用的详解

在Mysql使用过程中经常会limit来获取特定的范围的row数据，其中常用的两种用法。

limit是对Mysql查询结果进行分割（或者分页）返回

第一：limit num；

下方是详细讲解的例子：

            mysql> SELECT * FROM ratings ORDER BY category;
            +----+----------+--------+
            | id | category | rating |
            +----+----------+--------+
            |  1 |        1 |    4.5 |
            |  5 |        1 |    3.2 |
            |  3 |        2 |    3.7 |
            |  4 |        2 |    3.5 |
            |  6 |        2 |    3.5 |
            |  2 |        3 |    5.0 |
            |  7 |        3 |    2.7 |
            +----+----------+--------+
            

使用limit：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
            +----+----------+--------+
            | id | category | rating |
            +----+----------+--------+
            |  1 |        1 |    4.5 |
            |  5 |        1 |    3.2 |
            |  4 |        2 |    3.5 |
            |  3 |        2 |    3.7 |
            |  6 |        2 |    3.5 |
            +----+----------+--------+
            

第二：limit x,y;

举例：

mysql> SELECT * FROM ratings ORDER BY category;
            +----+----------+--------+
            | id | category | rating |
            +----+----------+--------+
            |  1 |        1 |    4.5 |
            |  5 |        1 |    3.2 |
            |  3 |        2 |    3.7 |
            |  4 |        2 |    3.5 |
            |  6 |        2 |    3.5 |
            |  2 |        3 |    5.0 |
            |  7 |        3 |    2.7 |
            +----+----------+--------+
            

1.LIMIT表示分页查询， LIMIT 0,4; 表示从索引为0的位置，就是第一个往后查4条数据。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 0, 4;
            +----+----------+--------+
            | id | category | rating |
            +----+----------+--------+
            |  1 |        1 |    4.5 |
            |  5 |        1 |    3.2 |
            |  4 |        2 |    3.5 |
            |  3 |        2 |    3.7 |
            +----+----------+--------+
            

1. LIMIT 3,3; 表示从索引为4的位置也就是第五条开始往后查8条数据。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 3, 3;
            +----+----------+--------+
            | id | category | rating |
            +----+----------+--------+
            |  4 |        2 |    3.5 |
            |  6 |        2 |    3.5 |
            |  2 |        3 |    5.0 |
            +----+----------+--------+
            

总结：limit 是用于对返回数据的分割（分页）。