Kurs SQL i MySQL · Moduł 2: Pobieranie danych (SELECT)
OFFSET i stronicowanie
W tej lekcji6
Kupiec z Rodos wrócił z nową prośbą: chce przejrzeć cały katalog na swojej tabliczce, po pięć tytułów naraz, jak strony księgi. Sam LIMIT bierze zawsze pierwsze wiersze. Ale co, jeśli chcesz drugą stronę katalogu - kolejne pięć książek po tych pierwszych pięciu? Tu wkracza OFFSET.
Strony katalogu
Ułóżmy wszystkie tytuły alfabetycznie i podzielmy je na strony po 5 pozycji. Siedemnaście zwojów daje trzy pełne strony i jedną krótszą:
| Strona | Pozycje | Tytuły | LIMIT | OFFSET |
|---|---|---|---|---|
| 1 | 1-5 | Ajaks, Antygona, Dziady, Elementy, Etyka | 5 | 0 |
| 2 | 6-10 | Fajdros, Hamlet, Iliada, Krzyżacy, Medea | 5 | 5 |
| 3 | 11-15 | Odyseja, Pan Tadeusz, Państwo, Polityka, Potop | 5 | 10 |
| 4 | 16-17 | Tom Sawyer, Uczta | 5 | 15 |
Kolumna OFFSET mówi, ile wierszy trzeba pominąć, zanim zacznie się dana strona: pierwsza niczego nie pomija, druga pomija pięć zwojów pierwszej, trzecia - dziesięć. LIMIT zawsze wynosi 5, bo to rozmiar strony. Ostatnia strona ma tylko dwie pozycje, bo tyle zwojów zostało.
OFFSET - przeskakiwanie wierszy
OFFSET mówi, ile wierszy pominąć, zanim zaczniesz liczyć. Tak pobierzesz trzecią stronę katalogu:
SELECT tytul
FROM ksiazki
ORDER BY tytul ASC
LIMIT 5 OFFSET 10;Tu pomijamy 10 zwojów i bierzemy kolejne 5 (czyli pozycje 11-15):
| tytul |
|---|
| Odyseja |
| Pan Tadeusz |
| Państwo |
| Polityka |
| Potop |
To dokładnie trzecia strona z tabeli powyżej. Na tym polega stronicowanie - dzielenie długiej listy na strony.
Wzór na stronę
Nie musisz liczyć na palcach. Wzór jest prosty, a w zapytaniu możesz go zapisać w komentarzu:
-- OFFSET = (numer_strony - 1) * rozmiar_strony
SELECT tytul FROM ksiazki ORDER BY tytul ASC LIMIT 5 OFFSET 5;Powyższe pokaże drugą stronę (pomijamy pierwszych 5, bierzemy kolejnych 5):
| tytul |
|---|
| Fajdros |
| Hamlet |
| Iliada |
| Krzyżacy |
| Medea |
Dla trzeciej strony wzór da (3 - 1) * 5 = 10, a dla czwartej (4 - 1) * 5 = 15. Zmienia się tylko OFFSET, LIMIT zostaje ten sam.
Stronicowanie wymaga ORDER BY
Zauważ, że każde zapytanie w tej lekcji ma ORDER BY. To nie przypadek. Bez ustalonej kolejności Biblioteka może przy każdym zapytaniu podać zwoje w innym porządku. Wtedy „pominięcie pierwszych dziesięciu” nie ma stałego znaczenia: ten sam tytuł może trafić na dwie strony, a inny nie pojawić się na żadnej. Dlatego zawsze stronicujemy listę posortowaną - najlepiej po rubryce bez remisów, takiej jak id.
Zapis z przecinkiem
W cudzym kodzie spotkasz też skrót, który MySQL przyjmuje zamiast słowa OFFSET:
SELECT tytul FROM ksiazki ORDER BY tytul ASC LIMIT 10, 5;Wynik jest taki sam jak przy LIMIT 5 OFFSET 10:
| tytul |
|---|
| Odyseja |
| Pan Tadeusz |
| Państwo |
| Polityka |
| Potop |
Uważaj na kolejność liczb: w zapisie z przecinkiem najpierw stoi liczba wierszy do pominięcia, a potem liczba wierszy do pokazania - odwrotnie niż w zapisie z OFFSET. Łatwo się pomylić, dlatego sam zawsze piszę LIMIT … OFFSET … - i to polecam.
Kolejność klauzul i typowe błędy
OFFSET dopisujemy po LIMIT, na samym końcu zapytania. Pełna kolejność brzmi: SELECT → FROM → ORDER BY → LIMIT → OFFSET. Dwa błędy zdarzają się najczęściej:
- Zamienione liczby.
LIMIT 10 OFFSET 5pominie pięć wierszy i pokaże dziesięć - zamiast jednej strony dostaniesz dwie sklejone w jedną. LIMIT to rozmiar strony, OFFSET to liczba pominiętych wierszy. - OFFSET bez LIMIT. W MySQL samo
OFFSET 10bez LIMIT to błąd składni. OFFSET zawsze idzie w parze z LIMIT.
Dzięki stronicowaniu nawet najdłuższy katalog mieści się wygodnie na ekranie - strona po stronie, zwój po zwoju.
Widzisz błąd w tej lekcji?
Sprawdź się
Odpowiedz na pytania z tej lekcji. Wybierz odpowiedź, a od razu zobaczysz, czy jest poprawna.
1. Co zwróci klauzula LIMIT 5 OFFSET 10 (przy ustalonym sortowaniu)?
2. Przy 5 pozycjach na stronę, jaki OFFSET trzeba podać, aby pokazać trzecią stronę wyników?
Zadania praktyczne w grze
- Edytor kodu
Napisz zapytanie, które z tabeli czytelnicy pobierze wszystkie kolumny, posortuje wiersze po id, pominie pierwszych 10 i pokaże kolejnych 5 (użyj ORDER BY, LIMIT i OFFSET).
- Klikanie w kolejności
Kliknij elementy w kolejności, w jakiej zapiszesz końcówkę zapytania pobierającą drugą stronę po 5 wierszy:
- Edytor kodu
Napisz zapytanie, które z tabeli ksiazki pobierze kolumnę tytul, posortuje rosnąco po tytule i pokaże drugą stronę po 5 pozycji (pomiń 5 wierszy, pokaż kolejnych 5).