The SELECT Statement and Basic Querying Techniques | 155

| Do You Love Me (Part Two) |
+----------------------------+
10 rows inset sec)
The
LIMIT
clause in this example restricts the output to the first 10 rows, saving the cost of buffering, communicating, and displaying the remaining 143 tracks.
The
LIMIT
clause can be used to return a fixed number of rows beginning anywhere in the result set. Suppose you want five rows, but you want the first one displayed to be the sixth row of the answer set. You do this by starting from after the fifth answer:
mysql> SELECT track_name FROM track LIMIT 5,5;
+----------------------------+
| track_name |
+----------------------------+
| I Let Love In |
| Thirsty Dog |
| Ain't Gonna Rain Anymore |
| Lay Me Low |
| Do You Love Me (Part Two) |
+----------------------------+
5 rows inset sec)
The output is rows 6 to 10 from the
SELECT
query.
If you want all rows after a start point, and you don’t know how many rows are in the table, then you need to choose a large integer as the second parameter. Suppose you want all rows after row 150 in the track table. Use the following command:
mysql> SELECT track_name FROM track LIMIT 150,999999999;
+----------------------+
| track_name |
+----------------------+
| As It Is When It Was |
| Weirdo |
| Paradise |
+----------------------+
3 rows inset sec)
Since there are likely to beat most tens of thousands of rows in the track table, providing as the second parameter guarantees all rows are returned. Technically, the largest number you can use is 18446744073709551615; this is the maximum value that can be stored in MySQL’s unsigned
BIGINT
variable type. MySQL will complain if you try to use a larger value. We discuss variable types in Other integer types in Chapter 6.
There’s an alternative syntax that you might see for the
LIMIT
keyword: instead of writing LIMIT 10,5
, you can write LIMIT 10 OFFSET 5

Download 4.24 Mb.

Share with your friends: