Kurs SQL i MySQL · Moduł 2: Pobieranie danych (SELECT)

OFFSET i stronicowanie

4 min czytania ·
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ą:

StronaPozycjeTytułyLIMITOFFSET
11-5Ajaks, Antygona, Dziady, Elementy, Etyka50
26-10Fajdros, Hamlet, Iliada, Krzyżacy, Medea55
311-15Odyseja, Pan Tadeusz, Państwo, Polityka, Potop510
416-17Tom Sawyer, Uczta515

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 5 pominie 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 10 bez 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. 1. Co zwróci klauzula LIMIT 5 OFFSET 10 (przy ustalonym sortowaniu)?

  2. 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).

  1. PytanieDziałanie LIMIT z OFFSET
  2. PytanieWzór na OFFSET przy stronicowaniu
  3. Zadanie z kodemStronicowanie czytelników
  4. Kliknij w kolejnościDruga strona po 5 wierszy
  5. Zadanie z kodemDruga strona książek

Przydatne artykuły