SELECT prod_name FROM products LIMIT 5;
The above statement uses the SELECT statement to retrieve a single column. LIMIT 5 instructs MySQL to return no more than five rows. The output from this statement is shown in the following:
+-----------+ | prod_name | +-----------+ | product 1 | | product 2 | | product 3 | | product 4 | | product 5 | +-----------+
To get the next five rows, specify both where to start and the number of rows to retrieve, like this:
SELECT prod_name FROM products LIMIT 5,5;
LIMIT 5,5 instructs MySQL to return five rows starting from row 5. The first number is where to start, and the second is the number of rows to retrieve. The output from this statement is shown in the following:
+------------+ | prod_name | +------------+ | product 6 | | product 7 | | product 8 | | product 9 | | product 10 | +------------+
So, LIMIT with one value specified always starts from the first row, and the specified number is the number of rows to return. LIMIT with two values specified can start from wherever that first value tells it to.
Row 0 The first row retrieved is row 0, not row 1. As such, LIMIT 1,1 will retrieve the second row, not the first one.
When There Aren't Enough Rows The number of rows to retrieve specified in LIMIT is the maximum number to retrieve. If there aren't enough rows (for example, you specified LIMIT 10,5, but there were only 13 rows), MySQL returns as many as it can.
MySQL 5 LIMIT and OFFSET
MySQL 5 LIMIT Syntax Does LIMIT 3,4 mean 3 rows starting from row 4, or 4 rows starting from row 3? As you just learned, it means 4 rows starting from row 3, but it is a bit ambiguous.
For this reason, MySQL 5 supports an alternative syntax for LIMIT. LIMIT 4 OFFSET 3 means to get 4 rows starting from row 3, just like LIMIT 3,4.